Dates and Timestamps

Just a quick one today folks. Had a stupid little problem pop up when converting some HQL to native SQL. Even though I was still executing the SQL via the Hibernate session, the query wasn't returning any results.

Hmmm. Suspicious. Query which used to work perfectly now broken.

Obviously, something had gone awry during the conversion. Turns out that when executing the query as HQL, the java.util.Date object being passed in as a bind would convert to an Oracle time stamp quite happily. This would allow some cheeky Date/Time arithmetic to take place, and we'd get a result. When passing the same Date object in as a parameter to a native SQL query, Hibernate seems to effectively call toString() on the object, simply giving us the Date, without any hours, minutes, or seconds. Which is entirely not useful.

To solve this irritating little issue, I did this:

 Date myDate = new Date(); // in reality, this is a date retrieved from the underlying database using a custom util, so as to avoid any timing issues  
 java.sql.Timestamp timestamp = new java.sql.Timestamp(myDate.getTime());  

You can then just pass the TimeStamp object into your SQL query as a parameter as you normally would when executing native SQL via the Hibernate Session.

I guess this is due to the fact that when executing SQL queries through the Hibernate session, you lose all the implicit intelligence that Hibernate brings to the table. When you start talking native SQL, the type conversions that would usually take place behind the scenes just don't happen, as I assume Hibernate is electing to trust your judgement as the developer, rather than automatically assume you want your parameters converted. There are clearly going to be other ways to address this issue, but doing it in code like this was the most appropriate to me.