handling DATETIME values 0000-00-00 00:00:00 in JDBC

Solution 1:

Alternative answer, you can use this JDBC URL directly in your datasource configuration:

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

Edit:

Source: MySQL Manual

Datetimes with all-zero components (0000-00-00 ...) — These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.

Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are:

• exception (the default), which throws an SQLException with an SQLState of S1009.
• convertToNull, which returns NULL instead of the date.
• round, which rounds the date to the nearest closest value which is 0001-01-01.

Update: Alexander reported a bug affecting mysql-connector-5.1.15 on that feature. See CHANGELOGS on the official website.

Solution 2:

I stumbled across this attempting to solve the same issue. The installation I am working with uses JBOSS and Hibernate, so I had to do this a different way. For the basic case, you should be able to add zeroDateTimeBehavior=convertToNull to your connection URI as per this configuration properties page.

I found other suggestions across the land referring to putting that parameter in your hibernate config:

In hibernate.cfg.xml:

<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

In hibernate.properties:

hibernate.connection.zeroDateTimeBehavior=convertToNull

But I had to put it in my mysql-ds.xml file for JBOSS as:

<connection-property name="zeroDateTimeBehavior">convertToNull</connection-property>

Hope this helps someone. :)