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

in application, using spring data , hibernate jpa provider persist , 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 have corresponding repository:

public interface concreteoperationrepository extends jparepository<concreteoperation, string> {      @query("select o concreteoperation o o.begintimestamp between :from , :to , o.status = :status , o.terminal.deviceid = :deviceid , 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 have integration test following method:

@transactional @test public void shouldfindoperationbyperiodandstatusandworkstationid() {     date = 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;     (string terminalid : terminalids) {         (operationstatus status : typeforuse) {             (int = 0; < countrowsforeachtype; i++) {                 concreteoperationrepository.save(createconcreteoperation(status, terminalid,                         string.valueof(++id)));             }         }     }     date = new date(calendar.getinstance().gettime().gettime());     (string terminalid : terminalids) {         (operationstatus status : typeforuse) {             collection<concreteoperation> operations =                     concreteoperationrepository.findoperations(from, to, status, terminalid, false);             assertequals(countrowsforeachtype, operations.size());         }     } } 

but test fails when using mysql database due empty result (but passes when switch hsqldb)

also, test passes if put delay "thread.sleep(1000)" 1 second @ beginning of test, after first line.

when execute sql hibernate log gives me right result. what's wrong code?

in jpa, date requires temporal hint. normally, set temporaltype when setting jpa query parameter:

query.setparameter("from", from), temporaltype.timestamp); 

with spring data need use @temporal annotation, query becomes:

@query("select o concreteoperation o o.begintimestamp between :from , :to , o.status = :status , o.terminal.deviceid = :deviceid , 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 );