Zero dates or Null in MYSQL?

As a follow-up to my earlier post on date conversions, I noticed that the MYSQL database was getting loaded with a lot of zero dates where the original data had null dates, I wondered how to deal with this and if it would present issues… well here is a great tip: Also note that MYSQL more or less requires zero dates and does not like nulls for dates…

The following from http://hartsock.blogspot.com

Magical MySQL URL parameters Save my Groovy Code

I’ve been working with legacy databases in MySQL and basically forbidden from using hibernate by a bug I had with all zero dates of the format ‘0000-00-00′ which is perfectly legal in MySQL but completely disallowed in JDBC.

Stack trace for this problem contains:

SQL Exception: Value ‘0000-00-00′ can not be represented as java.sql.Date

The answer? Apparently there is a hidden setting to use…

dataSource {

driverClassName = “com.mysql.jdbc.Driver”

dialect= org.hibernate.dialect.MySQLMyISAMDialect

url = “jdbc:mysql://localhost:3306/legacy?zeroDateTimeBehavior=convertToNull”

username = “legacyUser”

password = “legacyPassword”

}

That little flag on the URL is absolutely magical and now I can map my Grails objects onto a legacy database. Isn’t life grand?

Thoughts and Ideas: Magical MySQL URL parameters Save my Groovy Code.