Wednesday, December 8, 2010

Mapping Dates and Time Zones with Hibernate, Part 2: Few Solutions

Go back to Part 1 of this article

In the first part of this article I was talking about the problems when mapping date/time to a database table using Hibernate. In part two I will talk about the solutions.

The quick Way: Use Property Access Type and implement the Setter

Tell Hibernate to use setter and getter methods for field access, instead of using reflection to modify the entity object's fields directly. To do so, you have to put the Hibernate annotations above the getter methods, instead of the class attributes. You MUST move the annotation of the @Id field to the getter to enable property access. But you should do it with all field annotations, for better clarity. You can find a more detailed discussion of Hibernate property access here and here.
Once you did so, you can implement a setter method for the calendar field, which takes the calendar object provided by Hibernate, and creates a new object with the right time zone and date information from it:

package entity;

...

public class GMTDateEntity implements Serializable {

   
    ...

    private Integer pk;

    private Calendar calendar;

    @Id
    @Column(name = "pk", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer getPk() {
        return pk;
    }



    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "calendartime")
    public Calendar getCalendar() {
         return calendar;
    }

    public void setCalendar(Calendar calendar) {
         //create new calendar in GMT time zone
        this.calendar = new GregorianCalendar(TimeZone.getTimeZone("GMT"));
       
        //set calendar fields
        this.calendar.set(Calendar.YEAR, calendar.get(Calendar.YEAR));
        this.calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH));
        this.calendar.set(Calendar.DATE, calendar.get(Calendar.DATE));
        this.calendar.set(Calendar.HOUR_OF_DAY, calendar.get(Calendar.HOUR_OF_DAY));
        this.calendar.set(Calendar.MINUTE, calendar.get(Calendar.MINUTE));
        this.calendar.set(Calendar.SECOND, calendar.get(Calendar.SECOND));
        this.calendar.set(Calendar.MILLISECOND, calendar.get(Calendar.MILLISECOND));
       
        //recalculate calendar time millis
        this.calendar.getTime();
     }


     ...
}


Because the time zone information is the only thing that is wrong in Hibernate's calendar object, we simply create a new calendar object in the correct time zone, and then copy all required fields from Hibernate's object to our new object. The final call to getTime() will recalculate the calendars internal time milliseconds based on the field and time zone information. Unfortunately, the actual recalculation-methods are protected in java.util.Calendar for reasons I just don't know. So we have to use this less elegant workaround.

The elegant Way: Create a custom Hibernate User Type 

If for any reason you cannot use Hibernate property access, you will have to stick to the advanced art of programming and create a custom user type, which does the mapping from DB result set to Java object for you. Explanation of user type implementation would be beyond topic of this article. But you can find a very good introduction here. Just be aware, that you have to create a mutable user type, as java.util.Calendars are mutable objects.
To put everything short, here is the user type implementation that will serve our purpose:
package usertypes;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.TimeZone;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class GmtCalendarUserType implements UserType, Serializable {

    private static final long serialVersionUID = 1L;
  
    //The interesting methods:

    @Override
    public void nullSafeSet(PreparedStatement statement, Object value, int index)
            throws HibernateException, SQLException {


        // we have to be null-safe
        if (value == null) {
          Hibernate.CALENDAR.nullSafeSet(statement, null, index); 
          return;
        } 
 
        // we have a Calendar here
        Calendar cal = (Calendar) value;
        // cut millis, as SQL Server uses only 1/300 precision
        long millis = cal.getTimeInMillis();
        millis = millis - (millis % 1000);
        cal.setTimeInMillis(millis);
        // simply delegate to hibernate's built in method
        Hibernate.CALENDAR.nullSafeSet(statement, cal, index);
    }

    @Override
    public Object nullSafeGet(ResultSet resultSet, String[] columnNames,
            Object owner) throws HibernateException, SQLException {

        // we cannot do it like this, because it would initialize the calendar
        // in the jvm's default timezone:
        // Calendar cal = (Calendar) Hibernate.CALENDAR.nullSafeGet(resultSet,
        // columnNames);
        // return cal;

        // We have to create the Calendar object from the DB date string
        String timeString = (String) Hibernate.STRING.nullSafeGet(resultSet,
                columnNames);
        if (timeString == null)
            return null;

        try {
            Date date = this.parseDbDateString(timeString);
            // Init calendar in GMT
            Calendar retValue = new GregorianCalendar(
                    TimeZone.getTimeZone("GMT"));
            retValue.setTime(date);
            // calculate calendar fields
            retValue.getTime();
            return retValue;
        } catch (ParseException e) {
            throw new HibernateException("Could not parse datestring from DB.",
                    e);
        }
    }

    private Date parseDbDateString(String dateString) throws ParseException {
        // create gmt time zone
        TimeZone gmtZone = TimeZone.getTimeZone("GMT");
        // create db date format (cut millis)
        String pattern = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
        dateFormat.setTimeZone(gmtZone);
        return dateFormat.parse(dateString);
    }

    //The other UserType methods:
  
    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        return this.deepCopy(cached);
    }

    @Override
    public Object deepCopy(Object object) throws HibernateException {
        if (object == null)
            return null;
        // we have a calendar here
        Calendar cal = (Calendar) object;
        return cal.clone();
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) this.deepCopy(value);
    }

    @Override
    public boolean equals(Object object1, Object object2)
            throws HibernateException {
        if (object1 == object2) {
            return true;
        }
        if ((object1 == null) || (object2 == null))
            return false;
        return object1.equals(object2);
    }

    @Override
    public int hashCode(Object value) throws HibernateException {
        return value.hashCode();
    }

    @Override
    public boolean isMutable() {
        // Calendar is mutable
        return true;
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        return this.deepCopy(original);
    }

    @Override
    public Class returnedClass() {
        return Calendar.class;
    }

    @Override
    public int[] sqlTypes() {
        return new int[] { Types.TIMESTAMP };
    }
}

The nullSafeSet method will inject our Java object into the SQL statement. As Hibernate handles Calendars correctly when persisting, you simply delegate this task to Hibernate here. But since I'm using MS SQL Server in this example, I have to deal with the different precisions of Java dates (1 millisecond prec.) and SQL Server datetime types (1/300 second prec.). I choose the simple way here and simply cut the millis.

The nullSafeGet method does a little more of tweaking. This is where the result set is mapped to our Java Calendar object. If we would use Hibernate's built in mapping here (as in the commented block in this method), we would get the Calendar with the JVM time zone set. So we have to treat the datetime from the SQL result set as a string, and parse the date in GMT from it and create a new GMT GregorianCalendar.

Once we've finished our UsertType, all we have to do is to annotate our GMTDateEntity object to use it:
package entity;

...



@Entity
@Table(name = "datetestgmt")

//Declare the UserType on the class
@TypeDef(name = "gmtCalendar", typeClass = GmtCalendarUserType.class)
public class GMTDateEntity implements Serializable {



...




    
    //Use the UserType on the attribute
    @Type(type = "gmtCalendar")
    @Column(name = "calendartime")
    private Calendar calendar;
 



...

}


Go back to Part 1 of this article

6 comments:

  1. I believe there's a typo in your final solution:


    @Override
    public Class returnedClass() {
    return ZuluCalendar.class;
    }

    no mention of ZuluCalendar.class appears in anything previous.

    ReplyDelete
  2. So I've added your class to my project as a custom user type and used the custom type on the attribute. I've set mySQL global and session timezones to GMT, but when I persist the object to the database the timezone is still being converted even when the calendar's timezone is set to GMT. If I don't set the timezone, it persists as I would expect.

    Calendar calGMT = Calendar.getInstance();
    calGMT.setTimeZone(TimeZone.getTimeZone("GMT"));
    calGMT.set(1951, 1, 15, 0,00,00);

    I would expect that when this is persisted that the value in the row in mysql would be:
    '1951-02-15 00:00:01'
    instead it is:
    '1951-02-14 18:00:01'

    If I don't set the calendar's timezone then I get the expected value of: '1951-02-15 00:00:01'

    My system timezone is CST.

    Know what is happening?

    ReplyDelete
  3. It seems the gmt timezone is ignored when persisting. But from the code snippets provided, I cannot tell why. Pls provide more information: Did you use the user type as is? Please provide the code of the entity declaration and how you persist the entity.

    ReplyDelete
  4. For me even saving a calendar object does not take the time zone into consideration. Doesn't matter what time zone the calendar object has, it always gets saved using the system/database time zone.

    ReplyDelete
  5. This implementation appears to now be out of date with version 4.x of Hibernate (e.g. nullSafeSet impl must use SessionImplementor, etc).

    ReplyDelete