Java 使用日期参数时，Spring Data JPA 日期“介于”查询问题

Spring Data JPA date "between" query issue when using Date parameters

提问by Dmitry Shipaev

In my application, I am using Spring Data and hibernate as JPA provider to persist and read data.

在我的应用程序中，我使用 Spring Data 和 hibernate 作为 JPA 提供程序来持久化和读取数据。

I have top level Entity class:

我有顶级实体类：

@Entity
@Getter @Setter
@Table(name = "operation")
@Inheritance(strategy = InheritanceType.JOINED)
@EqualsAndHashCode(of = {"operationId"})
public abstract class Operation implements Serializable {
    public static final int OPERATION_ID_LENGTH = 20;

    @Id
    @Column(name = "operation_id", length = OPERATION_ID_LENGTH, nullable = false, columnDefinition = "char")
    private String operationId;

    @Column(name = "operation_type_code")
    @Getter(AccessLevel.NONE)
    @Setter(AccessLevel.NONE)
    private String operationTypeCode;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "begin_timestamp", nullable = false)
    private Date beginTimestamp = new Date();

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "end_timestamp")
    private Date endTimestamp;

    @Column(name = "operation_number", length = 6, columnDefinition = "char")
    private String operationNumber;


    @Enumerated(EnumType.STRING)
    @Column(name = "operation_status", length = 32, nullable = false)
    private OperationStatus status;

    @ManyToOne(optional = false)
    @JoinColumn(name = "user_id")
    private User user;

    @ManyToOne
    @JoinColumn(name = "terminal_id")
    private Terminal terminal;


    @Column(name = "training_mode", nullable = false)
    private boolean trainingMode;
}

For inherited class I have corresponding repository:

对于继承的类，我有相应的存储库：

public interface ConcreteOperationRepository extends JpaRepository<ConcreteOperation, String> {

    @Query("SELECT o FROM ConcreteOperation o WHERE o.beginTimestamp BETWEEN :from AND :to AND o.status = :status AND o.terminal.deviceId = :deviceId AND o.trainingMode = :trainingMode")
    Collection<ConcreteOperation> findOperations(@Param("from") Date startDay,
                                                   @Param("to") Date endDay,
                                                   @Param("status") OperationStatus status,
                                                   @Param("deviceId") String deviceId,
                                                   @Param("trainingMode") boolean trainingMode);
}

And I have integration test with following method:

我使用以下方法进行集成测试：

@Transactional
@Test
public void shouldFindOperationByPeriodAndStatusAndWorkstationId() {
    Date from = new Date(Calendar.getInstance().getTime().getTime());
    List<String> terminalIds = loadTerminalIds();
    List<OperationStatus> typeForUse = Arrays.asList(OperationStatus.COMPLETED,
            OperationStatus.LOCKED, OperationStatus.OPEN);
    int countRowsForEachType = 3;
    int id = 100001;
    for (String terminalId : terminalIds) {
        for (OperationStatus status : typeForUse) {
            for (int i = 0; i < countRowsForEachType; i++) {
                concreteOperationRepository.save(createConcreteOperation(status, terminalId,
                        String.valueOf(++id)));
            }
        }
    }
    Date to = new Date(Calendar.getInstance().getTime().getTime());
    for (String terminalId : terminalIds) {
        for (OperationStatus status : typeForUse) {
            Collection<ConcreteOperation> operations =
                    concreteOperationRepository.findOperations(from, to, status, terminalId, false);
            assertEquals(countRowsForEachType, operations.size());
        }
    }
}

But this test fails when I using MySql database due to empty result (but passes when I switch to HSQLDB)

但是当我使用 MySql 数据库时，由于结果为空，此测试失败（但当我切换到 HSQLDB 时通过）

Also, this test passes if I put delay "Thread.sleep(1000)" for one second at the beginning of the test, just after the first line.

此外，如果我在测试开始时将延迟“Thread.sleep(1000)”延迟一秒钟，就在第一行之后，则该测试通过。

When I execute SQL from Hibernate log it gives me right result. What's wrong with my code?

当我从 Hibernate 日志执行 SQL 时，它给了我正确的结果。我的代码有什么问题？

采纳答案by Dmitry Shipaev

I realized my problem. The problem was due to difference of precision between type of field in MySql (default timestamp precision cut of milliseconds) and Java date (with milliseconds) I've altered my table:

我意识到我的问题。问题是由于 MySql 中的字段类型（默认时间戳精度为毫秒）和 Java 日期（以毫秒为单位）之间的精度差异，我改变了我的表：

ALTER TABLE transaction modify end_timestamp TIMESTAMP(6)

and that's solved my problem.

这解决了我的问题。

回答by Vlad Mihalcea

In JPA, the Daterequires a temporal hint. Normally, you could set the TemporalTypewhen setting the JPA Queryparameter:

在 JPA 中，Date需要时间提示。通常，您可以TemporalType在设置 JPAQuery参数时设置：

query.setParameter("from", from), TemporalType.TIMESTAMP);

With Spring Data you need to use the @Temporalannotation, so your query becomes:

使用 Spring Data 您需要使用@Temporal注释，因此您的查询变为：

@Query("SELECT o FROM ConcreteOperation o WHERE o.beginTimestamp BETWEEN :from AND :to AND o.status = :status AND o.terminal.deviceId = :deviceId AND o.trainingMode = :trainingMode")
Collection<ConcreteOperation> findOperations(
    @Param("from") @Temporal(TemporalType.TIMESTAMP) Date startDay,
    @Param("to") @Temporal(TemporalType.TIMESTAMP) Date endDay,
    @Param("status") OperationStatus status,
    @Param("deviceId") String deviceId,
    @Param("trainingMode") boolean trainingMode
);