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



50 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
  22. Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision. This is a great inspiring article.I am pretty much pleased with your good work. You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post..

    ReplyDelete
  23. I have read your blog. Your information is really useful for beginner. informations provided here are unique and easy to understand.Thanks for this useful infromation.This is a great inspiring article.I am pretty much pleased with your good work.
    Linux Training Institute in Chennai | Linux Training in Velachery | RedHat Linux Training in Chennai

    ReplyDelete
  24. This is a great inspiring article.I am pretty much pleased with your good work. You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post..
    AWS Training Institute in Chennai | AWS Training in Velachery

    ReplyDelete
  25. I am pretty much pleased with your good work. You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
    Best Linux Training Institute in Chennai | Linux Training Center in Velachery

    ReplyDelete
  26. 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.
    AWS Training Institute in Chennai | AWS Training in Velachery

    ReplyDelete
  27. 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 AWS Training Center in Chennai | AWS Courses in Velachery

    ReplyDelete
  28. Good Post.Helps to gain knowledge about new concepts and techniques.Thank you so much for sharing with us.
    Best Java Training Institute in Chennai | Java Training in Velachery | J2EE Training in Chennai

    ReplyDelete

  29. 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 Python Training in Velachery | Python Exams in Kanchipuram | Python Training Center in Chennai

    ReplyDelete
  30. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is helpful to me a lot...

    Best Embedded System Training in Kanchipuram | Embedded Training in Kanchipuram | Embedded Training Center in Velachery

    ReplyDelete
  31. Thanks a lot for sharing this wonderful blog.keep updating such a excellent post with us.
    PCB Designing Training in Kanchipuram | PCB Training in Velachery | PCB Designing Training Institute in Chennai

    ReplyDelete
  32. Great blog, you put Good stuff.All the topics were explained briefly.so quickly understand for me.I am waiting for your next fantastic article. Thanks for sharing.Any course related details learn.
    Linux Training in Velachery | Linux Training Institute in Chennai | Linux Training in Kanchipuram

    ReplyDelete
  33. Very informative blog. Helps to gain knowledge about new concepts and techniques..so quickly understand for me.Thanks for sharing.Any course related details learn.
    AWS Training Institute in Chennai | AWS Training in Velachery | AWS Training Center in Kanchipuram

    ReplyDelete
  34. 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 JAVA Training Institute in Chennai | JAVA Training in Velachery | JAVA Training in Kanchipuram

    ReplyDelete
  35. Thanks for sharing this information,this is helpful to me a lot...It is amazing and wonderful to visit your site.


    Thanks for sharing this information,this is helpful to me a lot...It is amazing and wonderful to visit your site.


    Best CCNA Training Institute in Chennai | CCNA Training Center in Chennai | CCNA Training in Chennai | CCNA Courses in Chennai

    ReplyDelete
  36. Good and more informative post... thanks for sharing your ideas and views... keep rocks and updating.........It is amazing and wonderful to visit your site.

    Python Certification Training in Chennai | Python Training in Chennai | Python Training Center in Chennai | Python Exam Center in Chennai

    ReplyDelete
  37. I have read your blog. Good and more information useful for me, Thanks for sharing this information keep it up.....
    Aws Training Center in Chennai |Aws Training Center in Velachery

    ReplyDelete
  38. Good and more informative post...I was useful to improve my knowledge. Thanks a lot for sharing this wonderful blog.
    Python Training Institute in Chennai | Python Training Center in Velachery | Python Certification Training in Chennai

    ReplyDelete
  39. I have read your blog… This concept unique and easy to understand. Thank you for this useful information.
    Selenium Training Institute in Chennai | Selenium Training Center in Velachery | Selenium Certification Training in Chennai

    ReplyDelete
  40. Good Post! Helps to gain knowledge about new concepts Thank you so much for sharing this post, keep for sharing....
    Software Testing Training Institute in Chennai | Software Testing Training in Velachery | Software Testing Training Center in Chennai

    ReplyDelete
  41. Your Blog is Nice... Good concepts and gain my knowledge...keep updating...Thank You for the post....
    Java Training Institute in Chennai | Java Training Center in Velachery | Java Courses in Chennai


    ReplyDelete
  42. It is amazing and wonderful to visit your site.. . thanks for sharing your ideas and views... keep rocks and updating...thanks for sharing your ideas and views... keep rocks and updating
    Linux Training in Velachery | Linux Training Institute in Chennai | Linux Training in Kanchipuram

    ReplyDelete
  43. It is amazing and wonderful to visit your site.. . thanks for sharing your ideas and views... keep rocks and updating...thanks for sharing your ideas and views... keep rocks and updating
    Linux Training in Velachery | Linux Training Institute in Chennai | Linux Training in Kanchipuram

    ReplyDelete
  44. 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.
    AWS Training Institute in Chennai | AWS Training in Velachery | AWS Training Center in Kanchipuram

    ReplyDelete
  45. 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.
    AWS Training Institute in Chennai | AWS Training in Velachery | AWS Training Center in Kanchipuram

    ReplyDelete