Fork me on GitHub

3 Mar 2009

Persisting DateTime with zone information

I was stuck a while ago trying to figure out how to map PersistentDateTimeTZ in a GORM class. It's an implementation of Hibernate's UserType interface that persists a Joda DateTime value using 2 columns - one for the actual timestamp and one for the time zone. The DateTime class contains time zone information but a SQL timestamp column is time zone agnostic so you can lose data when the value is saved (the same exact problem exists when persisting java.util.Date values).

I could never figure out how to map the user type to my domain class property correctly. Just doing:

static mapping = {
dateTimeProperty type: PersistentDateTimeTZ
}

Failed with:

org.hibernate.MappingException: property mapping has wrong number of columns.

I seem to remember someone on the Grails mailing list suggesting I tried treating the value as an embedded type. That also didn't work as GORM embedded types have to be Groovy classes in grails-app/domain and PersistentDateTimeTZ is written in Java and lives in a library jar.

I finally found the solution in the 2nd Edition of The Definitive Guide to Grails (which I can't recommend enough, by the way). The trick is to pass a closure specifying the two columns to the property definition in the mapping builder. The working code looks like this:

static mapping = {
dateTimeProperty type: PersistentDateTimeTZ, {
column name: "date_time_property_timestamp"
column name: "date_time_property_zone"
}
}

The order of the columns corresponds to the order of the values returned by the sqlTypes() method of whatever UserType implementation you're using.

7 comments:

Roshan said...

Are you using the Joda Time plugin?

http://www.grails.org/JodaTime+Plugin

Rob said...

Yeah. I wrote the Joda-Time plugin! :-)

Dalton Jorge said...

Hi Robert,
I have some problems with the Joda Plugin.

My domain class is the following:

import org.joda.time.*
import org.joda.time.contrib.hibernate.*

class Horario {
TipoHorario tipoHorario
LocalTime horaInicio
LocalTime horaTermino


static constraints = {
tipoHorario(blank:false, unique:true)
horaInicio(blank:false)
horaTermino(blank:false)
}
}

When I try to run the application I get this error:
2009-04-03 00:55:19,551 [main] ERROR mortbay.log - Nested in org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'messageSource': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Invocation of init method failed; nested exception is java.lang.NullPointerException:
java.lang.NullPointerException

Please, can you help me?

Rob said...

It looks like you're missing the mapping block from your class that tells GORM how to persist the types, for example...

static mapping = {
horaInicio type: PersistentLocalTimeAsTime
horaTermino type: PersistentLocalTimeAsTime
}

Also using blank: false doesn't make sense for anything other than a String property.

Dalton Jorge said...

Great! Thank you very much!
It all worked successfully!!!

DH said...

good work on the plugin however, how do we get support for the following when using PeristenteDateTimeTZ, gorm and hibernate copmplain that it is not mapped to a single column....for example:

doing somthing like

c.list{
projections{
max('dateCreated')
}
}

causes hibernate to complain that date is not mapped to a single column. Seems like there might be a few missing hooks to make this work?

Rob said...

Hmm. I'll have to experiment with that, however it's very possible that it's not something the plugin can do anything about - it might be a problem with *any* Hibernate UserType that persists to multiple columns. I'll post something here once I figure anything out.