[SOLVED] JPA query does not return any data

JPA query does not return any data

I have a following JPA query:

@Query(value = "select r FROM TableEntity r where r.time=?1")
  TableEntity findByTime(java.sql.Timestamp time);

That query is pretty straightforward, should fetch the database row based on some time. It works like a charm with MYSQL and Oracle but won't work for MSSQL. I have debugged the query through Hibernate and JTDS driver and saw that Timestamp is successfully resolved in the Prepared statement, I can see the exact number of hours, minutes, seconds and milliseconds in query as I have in the database row. However, no data is returned back.

class TableEntity {

    @Type(type = "timestamp")
    private Timestamp time;

}

I am suspecting that some milliseconds rounding happens or they somehow gets messed up since query does return something once in a blue moon!

My Time Field in the database is datetime2(3) I am using the net.sourceforge.jtds 1.3 driver.

Time is formatted like this: 2020-06-03 13:02:21.273, I am working with milliseconds

EDIT: I tried writing plain prepared statement and here are results:

select r FROM TableEntity r where r.time=?1

preparedStatement.setTimestamp(1, timestamp); //does not work...

preparedStatement.setString(1, timestamp.toString()) //works like a charm

Any idea?

Solution

So, under the hood, Hibernate was mapping that timestamp to datetime type when it queried the DB (I concluded that using the MSSQL profiler) . Since I had datetime2 in the DB, comparing datetime with datetime2 on the DB level was not returning anything even if they were exactly the same in Milliseconds.

Solution would be to force mapping of timestamp to datetime2 so db datetime2 type gets queried with the same type