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

Question

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

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)

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.

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

I've changed Date to LocalDateTime but it doesn't solve problem — Dmitry Shipaev
– Dmitry Shipaev, Commented Feb 6, 2016 at 12:41

Vlad Mihalcea · Accepted Answer · 2016-02-06 14:42:01Z

9

In JPA, the Date requires a temporal hint. Normally, you could set the TemporalType when setting the JPA Query parameter:

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

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

@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
);

answered Feb 6, 2016 at 14:42

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dmitry Shipaev Over a year ago

I've added annotation @Temporal(TemporalType.TIMESTAMP) before date. But this haven't given any result

Vlad Mihalcea Over a year ago

Then better log the SQL query with parameters using P6Spy

Dmitry Shipaev Over a year ago

All seems correct. I've executed query from client and all is ok (concreop0_1_.begin_timestamp between '2016-02-07 11:44:21' and '2016-02-07 11:44:22') and concreop0_1_.training_mode=false

Dmitry Shipaev Over a year ago

No, it's not working while raw sql is right. It only works if I set small delay after defining Date from. But if I dont't do this result is empty.

Oliver Drotbohm Over a year ago

Maybe rename the parameters to the placeholders used in the query to avoid the @Param madness? 😉

Dmitry Shipaev · Accepted Answer · 2016-02-08 14:42:51Z

0

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:

ALTER TABLE transaction modify end_timestamp TIMESTAMP(6)

and that's solved my problem.

answered Feb 8, 2016 at 14:42

Dmitry Shipaev

811 gold badge1 silver badge5 bronze badges

3 Comments

Basil Bourque Over a year ago

Did you mean to say “microseconds” for MySQL?

Basil Bourque Over a year ago

The java.time framework built into Java 8 and later has no such problem, handling nanosecond resolution (up to 9 digits of a fractional second). If you use java.sql.Timestamp and java.time.Instant you will have no such problem with losing data. Avoid the notoriously troublesome java.util.Date/.Calendar classes.

Dmitry Shipaev Over a year ago

Yes, thanks for the correction. I meant "microseconds". But microseconds were cut off by MySql. For instance - when I insert "2016-02-09 08:11:37.567" this timestamp inserted into MySql as "2016-02-09 08:11:37".

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related