I recently ran into an interesting problem involving hibernate and dates across different database systems mainly Microsoft SQL 2008 and IBM Informix.
It appears that in HQL queries you can use Informix’s today for obtaining the current date and time:
query = session.createQuery();
String whereClause = "from myTable where myDateColumn > today -10";
This query finds the today 10 days from the current time.
When we run the query as below:
List<MyTable> myTableResults = query.list();
THis executes successfully in IBM Informix but in MSSQL we get the following error:
column today not found
So what do we do now? it appears that NOW() does not work.
Looking through various information and issues on the internet and hibernate tutorial and forums I found that hibernate can support certain functions of a specific database. This ties in with the hibernate dialect and JDBC driver.
I found that GETDATE() works within the where clause. So updating the above to:
String whereClause = "from myTable where myDateColumn > GETDATE() -10";
This worked well. So this raises more questions. How can we in general see which functions the RDMS provides that hibernate will support in its HQL queries – the where clause?
It should be noted that in the select part of the query we have more freedom. As dates can be retrieved.
Also it should be noticed that MS SQL has a newer driver sqljdbc4.jar and sqljdbc.jar. The difference in these two is quite big when we consider dates. For example SQL 2005 had fewer date types then SQl 2008. So this leads to problems when using the older driver(sqljdbc.jar) on SQL 2008.
Another important insight is in the Dialect class there exists the methodDialect.getCurrentTimestampSQLFunctionName() which can give us the database-specific SQL function for retrieving the current timestamp.
Also we cannot use Java’snew Date() in the where clause part of the query as this does not always work. The format the date will be in might not translate well into a string. We could try parameters in queries using AbstractQueryImpl.setParameter(String name, Object val).
However a problem could arise when using Java’s new Date() in as a paramter in a query as opposed to using a database function, this problem would be that time from Java would be based on the application as opposed to the database server itself. So if the two are at different locations and setup differently we could have problems.
Hence we can make our application cater for different types of RDMS systems based on the type and hence use various functions to aid development.
Hence when dealing with date types one has to be cautious specially if developing on an open source RDMS and then using a commerial RDMS. This saves on licencing but when deploy issues like dates can give problems!