Pages

Sunday, April 22, 2012

Reading and Writing UTC Timestamps to DB with Hibernate

Problem

Reading and writing UTC timestamps to a database when the default timezone may change. E.g., this might happen in an application server if an application running in the same JRE changes the default timezone as follows:
    TimeZone.setDefault(TimeZone.getTimeZone("Europe/Zurich")); 
This is not possible as the applications are running in separated classloaders, you may say. I thought the same. And perhaps this must not happen because there is a spec that does forbid this (by the way, is there any?). However, it is possible to change the default timezone for all applications in the same node that way at least in Oracle Weblogic server. Try it out, if you dont't believe me.

Solution

The easiest solution to solve this problem I know is to create an own mapping type extending the standard Hibernate timestamp type org.hibernate.type.TimestampType:
package ch.meteoswiss.commons.hibernate;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.SimpleTimeZone;


/**
 * timestamp: A type that maps an SQL TIMESTAMP to a Java
 * java.util.Date or java.sql.Timestamp using UTC time zone.
 * @author Peter Keller
 */
public class UTCTimestampType extends org.hibernate.type.TimestampType {

    @Override
    public Object get(ResultSet rs, String name) throws SQLException {
        return rs.getTimestamp(name, createUTCCalendar());
    }

    /**
     * Creates UTC calendar. DO NOT USE a static calendar instance.
     * This may lead to concurrency problems with (at least) the Oracle DB
     * driver!
     * @return Calendar with UTC time zone.
     */
    private static Calendar createUTCCalendar() {
        final Calendar c = Calendar.getInstance();
        c.setTimeZone(new SimpleTimeZone(0, "UTC"));
        return c;
    }
    
    @Override
    public void set(PreparedStatement st, Object value, int index) throws SQLException {
        Timestamp ts;
        if (value instanceof Timestamp) {
            ts = (Timestamp) value;
        } else {
            ts = new Timestamp(((java.util.Date)value).getTime());
        }
        st.setTimestamp(index, ts, createUTCCalendar());
    }

}

Use it as follows with Java annotations (you could use the type class also in a XML configuration file):
import java.util.Date;
import org.hibernate.annotations.Type;

@Entity
@Table(name="...")
public class Data {

    private Date receptionTimeDt;
 
    @Type(type="ch.meteoswiss.commons.hibernate.UTCTimestampType")
    @Column(name="RECEPTION_TIME_DT", nullable=false)
    public Date getReceptionTimeDt() {
        return receptionTimeDt;
    }

}
Of course, this mapping class only works with Hibernate and is not standard JPA.

Tuesday, April 10, 2012

Hibernate returning NULL entities?

Ever got NULL entity entries from Hibernate? I did.

Having a table V_MEAS_SITE with following attributes:
MEAS_SITE_ID:     NUMBER(8)
INSTALLATION_ID  NUMBER(8)
NAME_TX          VARCHAR2(10)
...
The "V_" in the table name indicates that this is a Oracle view and not a table. This name follows our naming conventions. As the name of the view indicates, the view is holding all information about measurement sites (I actually work for the national weather service). This table is mapped to the following Java class:
@Entity(table="V_MEAS_SITE")
public class MeasurementSite {

    @Id
    @Column(name="MEAS_SITE_ID")
    private int measurementSiteId;

    @Column(name="INSTALLATION_ID")
    private int installationId;

    @Column(name="NAME_TX")
    private String name;

    // getter/setter are ommited for brevity

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((this.getMeasurementSiteId() == null) ? 0 : this.getMeasurementSiteId().hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (!(obj instanceof MeasurementSite)) {
            return false;
        }
        final MeasurementSite other = (MeasurementSite)obj;
        if (this.getInstallationId() == null) {
            if (other.getInstallationId() != null) {
                return false;
            }
        } else if (!this.getMeasurementSiteId().equals(other.getMeasurementSiteId())) {
            return false;
        }
        return true;
    }
}
Let's write an EJB session bean reading some data:
@Session
public class MeasurementSiteService implements MeasurementSiteLocal {
    
    @PersistenceContext(unitName="DefaultEntityManager")
    private EntityManager em;

    public List<MeasurementSite> findByName(String name) {
        return em.createQuery("select m from MeasurementSite where m.name like :name")
            .setParameter("name", name)
            .list();
    }

    // used for testing
    public void setEntityManager(EntityManager em) {
        this.em = em;
    }

}
That's easy. OK, let's test this mapping with a JUnit test class:
    
public class MeasurementSiteServiceTest {
    private EntityManager em = ....; // set up the entity manager   
    
    @Test
    public void findByName() {
         final MeasurementSiteService s = new MeasurementSiteService();
         s.setEntityManager(em);

         final List<MeasurementSite> list = s.findByName("Z%");
         for (MeasurementSite m: list) {
             Assert.notNull(m); 
         }
    }
   
}

However, this test fails. There are indeed some NULL entity entries in the list. Hibernate must be broken!

Of course, Hibernate is not broken. Do you see what went wrong? I did after 3 hours of debugging. The view catches content of different tables and is therefore de-normalized. The primary key of the V_MEAS_SITE view is not MEAS_SITE_ID but INSTALLATION_ID! As a view has no explicit contraints there is no primary key constraint neither and therefore you have no explicit indications about the "logic" in the data. In this case it means that there are more than one entry for a given measurement site ID. And that's why Hibernate returned the NULL values. Of course it would have been nice, if Hibernate would have thrown some Exceptions to help a careless Java developer...

By the way, Oracle DB allows to query the DLL statement:
select dbms_metadata.get_ddl('VIEW', 'V_MEAS_SITE') as dll from dual

Sunday, April 8, 2012

Hibernate SQL Logging to Log4j

In my projects, for logging the SQL statements generated by Hibernate I always set the hibernate.sql_show property to true in the hibernate.cfg.xml file:
true
This logs the SQL statements to the STDOUT console. Only recently I realized that the logs can also be sent to Log4j. Of course this allows to log the SQL statements not only to the STDOUT but also to all possible Log4j appenders such as a file appender etc.. There are (at least) two ways to do this.

1) Setting the log level statically in the log4j.properties configuration file, e.g.:
log4j.logger.org.hibernate.SQL=DEBUG, FILE_APPENDER
log4j.additivity.org.hibernate.SQL=false
Setting additivity to false ensures that log messages aren't bubbled up to parent handlers.

2) Or setting the log level dynamically in Java, e.g.:
import org.apache.log4j.Logger;
import org.apache.log4j.Level;

...    

Logger log = Logger.getLogger("org.hibernate.SQL");
log.setLevel(Level.DEBUG);