Dating, Where clauses and Hibernate

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 method Dialect.getCurrentTimestampSQLFunctionName() which can give us the database-specific SQL function for retrieving the current timestamp.

Also we cannot use Java’s

new 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!

Advertisements

About gjcbell

I am the Cloud Architect at IBM. I work in and create Hybrid Cloud solutions. These are solutions that have an on premise facet and multi Cloud platform facet. My area of focus spans the Bluemix Platform, IBM Cloud Private as well as cloud brokerage.
This entry was posted in Computers and Internet. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s