JPA query does not return any data

Question

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?

how `time` is formatted ?, I mean in your code (`SimpleDateFormat` pattern) — Bashir, Jun 03 '20 at 13:55
this is not what I mean, when you execute your query with `findByTime(time)`, you must assign a date into `time`, how do you assign that 2020-06-03 13:02:21.273 into `time`. I guess you parse from `String` to `Timestamp` using `SimpleDateFormat`, how you do that? how you assign the date inside `time` — Bashir, Jun 03 '20 at 14:12
how do you call `finByTime(time)` , if you tested it and it doesn't work please provide us how you're calling it and how the time `value` is being assigned before executing `finByTime(time)` . I hope you understood what I need. it's not that difficult to find it — Bashir, Jun 03 '20 at 14:34
SQL Server stores time rounded to nearest 1/300 of a second. See: https://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond . — Alex, Jun 04 '20 at 02:17
@Bashir - Timestamp is already prepared as a result of some previously executed JPA query, and then I simply call the method with the findByTime(thatTimestamp). I debug the thatTimestamp, all seems good, it has milliseconds in its String representation. Can you say what you suspect might be wrong? — Filip, Jun 04 '20 at 11:55
@Alex I don't think that is an issue. I see the value in database with .243 Milliseconds lets say. When I query it with the simple sql string "where time = ...243" I get it back. Looks like that prepared statements are somehow loosing the MSs — Filip, Jun 04 '20 at 11:57
Run SQL Server Profiler to capture the actual executed statements and parameters. — Alex, Jun 04 '20 at 12:40
Please check that hours of the dates match. This is probably not relevant but just to tick off the list: https://stackoverflow.com/questions/23267971/timestamp-discrepancies-between-sql-server-and-java — Alex, Jun 04 '20 at 12:47
@Filip I am asking about format, because may be you are using `MM-dd-yyyy hh:mm:sss` while it should be `MM-dd-yyyy HH:mm:sssa` — Bashir, Jun 04 '20 at 13:44
@Alex I did. Hibernate was mapping that timestamp to datetime type when it queried the DB. 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 but I did not do that, since I could not change that part so we avoided that query by changing some previous — Filip, Jun 10 '20 at 09:58
Post this as answer as I did not see this info anywhere else. (You will have my upvote) — Alex, Jun 10 '20 at 09:59

score 1 · Accepted Answer · answered Jun 10 '20 at 10:00

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

JPA query does not return any data

1 Answers1