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



23 comments:

  1. Anonymous9:59 PM

    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. Anonymous6:35 PM

    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. Anonymous3:41 PM

    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
  4. Anonymous8:03 PM

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

    ReplyDelete
  5. Awesome post it's really useful from me..Kindly keep sharing such a nice post..
    Comptia Network+ Certification Courses in Chennai | Best N+ Courses in Tambaram

    ReplyDelete
  6. Excellent blog which helps me to get the in depth knowledge about the technology, Thanks for sharing such a nice blog..Android Certifications Exam Center in Chennai | Best Android Exam in Mandaveli

    ReplyDelete
  7. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    CorelDraw Graphics Suite Certifications Center in Chennai | No.1 CorelDraw Courses in Saidapet

    ReplyDelete
  8. very useful information. i am expecting more posts like this please keep updating us........ Python Certifications Training Institute in Chennai | Python Coaching in Chromepet

    ReplyDelete
  9. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    ISTQB Certifications Center in Chennai | ISTQB Training in Taramani

    ReplyDelete
  10. Very informative and innovative blog to sharing..keep sharing your post.
    Python Certifications Exam Cost in Chennai | NO.1 Python Coaching in Adyar

    ReplyDelete
  11. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    No.1 ISTQB Certifications Exam Course in Chennai | Testing in Thiruvanmiyur

    ReplyDelete
  12. Thank you for having taken your time to provide us with your valuable information relating to your stay with us.
    Adobe InDesign Certification Courses in Chennai | No.1 InDesign Training in Adambakkam

    ReplyDelete
  13. Your article is really an amazing with useful content, thank you so much for sharing such an informative information. keep updating.
    AWS Training Institute in Chennai | Best AWS Training Center in Velachery | AWS Training in Perungudi | AWS Training in Kanchipuram

    ReplyDelete
  14. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    Best Java Training Institute in Chennai | Java Training in Velachery

    ReplyDelete
  15. It’s really a nice and helpful piece of information. I’m satisfied that you just shared this helpful information with us. Please stay us informed like this. Thanks for sharing.
    MatLab Training Institute in Chennai | MatLab Training in Velachery

    ReplyDelete
  16. Nice Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one,keep updating..

    Best AWS Training Institute in Taramani | No.1 AWS Training Center in Taramani

    ReplyDelete
  17. Your article is really amazing with informative information,you are shared.Thanks a lot for sharing this wonderful blog.keep updating such a excellent post with us.
    Embedded System Training in Tambaram | Embedded Training in Tambaram

    ReplyDelete
  18. Very informative blog. Helps to gain knowledge about new concepts and techniques. Thanks a lot for sharing this wonderful blog.keep updating such a excellent post with us.
    Best MatLab Training Institute in OMR | No.1 MatLab Training Center in OMR

    ReplyDelete
  19. I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.
    Best CCNA Training Institute in Guindy | No.1 CCNA Training Institute in Guindy

    ReplyDelete
  20. Very informative blog. Helps to gain knowledge about new concepts and techniques. Thanks a lot for sharing this wonderful blog.keep updating such a excellent post with us.
    AWS Exam Center in Chennai | AWS Certification Exams in Chennai | AWS Exams in Velachery

    ReplyDelete
  21. Thank you for your post. This was really an appreciating one. You done a good job. Keep on blogging like this unique information with us.
    Best Embedded Training Institute in Thiruvanmiyur | No.1 Embedded Training Institute in Thiruvanmiyur

    ReplyDelete