Post a Comment On: lo-fi

Understanding Zero time sounds like something you might find in a zen koan. Apparently, JDBC doesn't get it. For the past couple of days, I didn't either. But now, I've reached Hibernate nirvana; at least for a moment.

I've been working on getting a very basic resultset back when using Hibernate. The table I'm using has a couple of datetime fields, and by default, they are filled with zero time, such as 0000-00-00 00:00:00 in the format YY-MM-DD HH:mm:SS. I had lots of trouble pulling out the results, as a result. I'd get the following error:

Value '0000-00-00' can not be represented as java.sql.Timestamp

This only happens when I have existing data in my database with a "null" date. When the dates are good, the error doesn't appear.

Assuming that I don't have control over the entry of a null date in the database, I tried resolving the issue in a couple of ways. First I tried to resolve it by adjusting the hibernate mapping. I changed the "type" attribute of the property to be a timestamp, but that didn't work.

After some more research, I realized that a custom UserType might be the answer. I came to that decision after reading this post about the using dates in hibernate. I also read a bit about more date and time issues in the hibernate forums. After going through these discussions, and coming to the understanding that my problems related to the odd date in my database, Things pointed to the custom UserType. I didn't know much about them, but I do know that there's a method specified in the interface named nullSafeGet and that sounds a LOT like what I needed. My strategy was to implement the UserType interface and create a custom class to use as a type in my mapping. Then, when a "0000-00-00 00:00:00" value is retrieved from a DATETIME typed column in the database, I'd simply return null instead of the beginning-of-time value.

I tried this out a moment ago, using a UserType class for Timestamp-like fields, which I found again, on the Hibernate forums. However, by the time the code execution could reach the nullSafeGet method of the class I was using, the error had already been thrown. So, apparently the answer to resolving zero dates does NOT lie in creating a custom UserType for Hibernate. So what's the answer?

The answer is so simple, that I nearly tipped over in my chair when I read it. Interestingly enough, I found the beginning of the trail to the answer in a RadRails forums post. This post points out that all I need to do is add an argument to the JDBC URL. All of the arguments for the JDBC URL for MySQL using the Connector/J driver are here. Specifically, the following argument handles the odd dates

zeroDateTimeBehavior=converToNull

So, a full JDBC URL for connecting to a database that might have zero dates would look like this:

jdbc:mysql://myhost/mydatabase?zeroDateTimeBehavior=convertToNull

There you have it. The solution turned out not to be Hibernate related at all, but rather, JDBC related.