Friday, November 19, 2010

Mapping Dates and Time Zones with Hibernate, Part 1: Plenty of Obstacles

Go ahead to part 2 of this article
 
Have you ever tried to persist a date with Hibernate, which is supposed to
be in a different time zone from the one of the system your application
is running on? I'm pretty sure that you will get into trouble while
trying this. There's a number of obstacles to pass. Let's get them on
one by one and figure out appropriate solutions.


The Setup

I'm using JBoss 5.1 with Hibernate 3 and JPA for this tutorial. The data base is
Microsoft SQL Server Express 2008 with the JDBC driver from
sqljdbc4.jar. Both, JBoss and SQL Server are running on the same
machine in time zone Europe/Berlin with Central European Time (CET) as
active time schedule. We want to persist a Date to the data base wich shows up there in GMT time.


We will need two Java classes. First there's the entity data object to be
persisted to the DB. As we want to persist GMT date records, it is called
GMTDateEntity:

package entity;

import java.io.Serializable;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

public class GMTDateEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    //DB primary key.
    @Id
    @Column(name = "pk", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer pk;

     //The actual Date record.
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "datetime")
    private Date date;

    public Integer getPk() {
        return pk;
    }

    public Date getDate() {
        return this.date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public void setPk(Integer pk) {
        this.pk = pk;
    }
}

The second class is the Data Access Object used to persist and retrieve our data object:
package dao;

import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import entity.GMTDateEntity;

@Stateless
@Remote(IDateTestDao.class)

public class DateTestDao implements IDateTestDao {

    //References section in hibernate config persistence.xml
    @PersistenceContext(unitName = "hibernate-date-test")
    private EntityManager entityManager;

    @Override
    public GMTDateEntity retrieveGMTDateEntity(Integer pk) {
        return this.entityManager.find(GMTDateEntity.class, pk);
    }

    @Override
    public GMTDateEntity persistGMTDateEntity(GMTDateEntity entity) {
        return this.entityManager.merge(entity);
    }
}


1. Obstacle: Hibernate uses system's time zone

Now let's persist a date object to the DB using our two classes. For this example I will create a java.util.Date representing the 9. November 2010, 12:00h GMT which we want to store to the DB. Now we do something like this:

String dateStringGMT = "2010-11-09T12:00:00";
//Parse the String using GMT time zone
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));

Date date = dateFormat.parse(dateStringGMT);
GMTDateEntity entity = new GMTDateEntity();
entity.setDate(date);
dateTestDao.persistGMTDateEntity(entity);





After executing the code we will have a new record in our DB:




What happened? The time is shifted by an hour!
This is because java.util.Date stores dates in a time zone independent fashion, but for SQL data bases there dosn't exists the notion of time zone independent dates. You can store date records to a SQL DBMS only with a specific time zone. So what Hibernate does, is using the time zone schedule of its JVM (which runs in CET) to convert the time zone independent java.util.Date to the time zone specific date string in our DB record. Because CET is one hour ahead of GMT, one hour has been added to our DB date.
Unfortunately, there exists neither an Hibernate or JPA annotation nor any other simple way to tell Hibernate in which time zone to persist java.util.Date objects. So if we cannot set the time zone schedule of our JVM to GMT, we must figure out other ways to tell Hibernate what we want.


2. Obstacle: Hibernate doesn't use Calendar's time zone for reading
 
The sensible way for attaching time zone information to dates in Java is the use of java.util.Calendar. So let's tweak our entity class a bit and add a Calendar field:

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "calendartime")
private Calendar calendar;

And now we extend our testing code to persist the calendar field, too:
String dateStringGMT = "2010-11-09T12:00:00";
//Parse the String using GMT time schedule
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));

Date date = dateFormat.parse(dateStringGMT);
GMTDateEntity entity = new GMTDateEntity();
entity.setDate(date);

//Create Calendar object in GMT
Calendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
cal.setTime(date);
entity.setCalendar(cal);
dateTestDao.persistGMTDateEntity(entity);


After code execution our DB will look like this:




As you can see, the calendartime column perfectly displays our date in GMT! But still we're not satisfied. To be a little bitchy, we add the following lines to our testing code:

 String dateStringGMT = "2010-11-09T12:00:00";
//Parse the String using GMT time schedule
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));

Date date = dateFormat.parse(dateStringGMT);
GMTDateEntity entity = new GMTDateEntity();
entity.setDate(date);

//Create Calendar object in GMT
Calendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
cal.setTime(date); 
entity.setCalendar(cal); 
dateTestDao.persistGMTDateEntity(entity);

//Now read back what we persisted, and store it again
GMTDateEntity readEntity = dateTestDao.retrieveGMTDateEntity(entity.getPk());
GMTDateEntity newEntity = new GMTDateEntity();
//just to be sure we set the time zone to GMT again
readEntity.getCalendar().setTimeZone(TimeZone.getTimeZone("GMT"));
newEntity.setCalendar(readEntity.getCalendar());
newEntity.setDate(readEntity.getCalendar().getTime());
dateTestDao.persistGMTDateEntity(newEntity);
 

The DB now looks like this:





What the hell?! Obviously we got a Calendar out of the DB which is different from the one we previously stored to it! And that's true. When reading from DB, HIbernate creates a completely new Calendar object, and uses the system's time zone again for converting the date.

3. Obstacle: There are solutions, but it won't be easy!

Well, if you're as naive as I am, you might just shout out: "Of cause it didn't work! We didn't tell HIbernate about the target time zone of our entity class!" And then you would go and tweak the GMTDateEntity like this:

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "calendartime")
private Calendar calendar = new GregorianCalendar(TimeZone.getTimeZone("GMT"));

Just to shorten this: It will not work! Hibernate uses Reflection to set the entity's fields, and when it comes to do so, the date conversion is already done. So instead of asking yout entity's field: "What kind of time zone would be welcome, Sir?", it just creates a new Calendar instance in system's time zone and squeezes the DB date into it.

However, there are some more complex solutions to this problem, which I will explain in the second part of this article.

Go ahead to part 2 of this article



No comments:

Post a Comment