Dates and Timestamps

Just a quick one today folks. Had a stupid little problem pop up when converting some HQL to native SQL. Even though I was still executing the SQL via the Hibernate session, the query wasn't returning any results.

Hmmm. Suspicious. Query which used to work perfectly now broken.

Obviously, something had gone awry during the conversion. Turns out that when executing the query as HQL, the java.util.Date object being passed in as a bind would convert to an Oracle time stamp quite happily. This would allow some cheeky Date/Time arithmetic to take place, and we'd get a result. When passing the same Date object in as a parameter to a native SQL query, Hibernate seems to effectively call toString() on the object, simply giving us the Date, without any hours, minutes, or seconds. Which is entirely not useful.

To solve this irritating little issue, I did this:

 Date myDate = new Date(); // in reality, this is a date retrieved from the underlying database using a custom util, so as to avoid any timing issues  
 java.sql.Timestamp timestamp = new java.sql.Timestamp(myDate.getTime());  

You can then just pass the TimeStamp object into your SQL query as a parameter as you normally would when executing native SQL via the Hibernate Session.

I guess this is due to the fact that when executing SQL queries through the Hibernate session, you lose all the implicit intelligence that Hibernate brings to the table. When you start talking native SQL, the type conversions that would usually take place behind the scenes just don't happen, as I assume Hibernate is electing to trust your judgement as the developer, rather than automatically assume you want your parameters converted. There are clearly going to be other ways to address this issue, but doing it in code like this was the most appropriate to me.

Hibernate and State Pattern Persistence

Yesterday, I started looking at implementing a State Pattern using Hibernate as the persistence mechanism. For the most part, I was following Joe Kelley's excellent blog post on the subject here, in which he outlines two possible implementations using both an Integer, and the org.apache.commons Enum type.

Both of these implementations are good solutions to the problem - they get the job done with minimal fuss.

However, I thought it might be useful to write about a third possible implementation, hinted at in Mr. Kelley's post - still based on his work, but tweaked to use a standard Java Enum as its main currency of change. It has the advantages of the described Enum implementation, but without bringing in extra libraries, or adding more complication than is strictly necessary.

To get this to work, realistically you only need to alter your implementation of the custom UserType. There are 4 things we need to change:

• Remove any static ints from your custom UserType. You won't need them.
• Alter the public int[] sqlTypes method to return a Types.VARCHAR.
• Alter the public Object nullSafeGet to convert the returned String value into your Java Enum value.
• Alter the public void nullSafeSet method to convert the concrete implementation of your State object to the String value required for persistence.

The first change is simple enough. We're moving responsibility for the actual values we're storing out of our UserType implementation, and into an Enum.

The second is to change the sqlTypes method. Here, we're merely telling Hibernate that what we expect our transactions with the data layer to be of type VARCHAR.

 public int[] sqlTypes()  
 {  
      return new int[] {Types.VARCHAR};  
 }  

The most interesting changes are the ones for nullSafeGet and nullSafeSet. We're using the GenericStateEnum to hold possible state values to be persisted. Using this method, you can manage explicitly what is being persisted, and enforce data-level constraints if you want.

nullSafeGet is fairly easy to understand - all we're doing here is taking our String value out of the ResultSet, attempting to convert it to an Enum value, and running a switch / case over it to determine which implementation of AbstractGenericState we should be returning to the calling class.

 public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException  
 {  
      final String stateStr = rs.getString(names[0]);  
      if (rs.wasNull())  
      {  
           return null;  
      }       
      GenericState state = null;  
      GenericStateEnum stateValue = GenericStateEnum.valueOf(stateStr);  
      switch(stateValue)  
      {  
           case STATEONE:  
                state = AbstractGenericState.STATEONE;  
                break;  
           case STATETWO:  
                state = AbstractGenericState.STATETWO;  
                break;  
           default:  
                throw new RuntimeException("Could not find GenericState for value " + stateStr);  
      }       
      return state;  
 }  

The method nullSafeSet is the interesting one:

 public void nullSafeSet(PreparedStatement ps, Object value, int index) throws HibernateException, SQLException  
 {  
      if (value== null)  
      {  
           ps.setNull(index, Types.VARCHAR);  
      }  
      else  
      {  
           GenericState state = (GenericState) value;  
           GenereicStateEnum stateEnum = null;  
           if (state.isStateOne())  
           {  
                stateEnum = GenericStateEnum.STATEONE;  
           }  
           if (state.isStateTwo())  
           {  
                stateEnum = GenericStateEnum.STATETWO;  
           }  
           ps.setString(index, stateEnum.toString());  
      }  
 }  

Now, I'm not particularly happy with this method at the moment in the way it's not particularly flexible, and I'm currently pondering ways to improve this. For example, what if we need to add another state? It's a relatively small change for sure, but is it one we can avoid?

Personally, I think the way forward with this is to use Reflection to work out the relationship between the implementation of GenericState used in the method, and the GenericStateEnum value. Certainly, it's not beyond the realms of possibility to solve. But as a starting point for using bog-standard Java Enums for State Pattern persistence using Hibernate, I've found it pretty effective.

Have a go, see what you think.