Using Hibernate on old Solid databases

17 November 2009

Stéphane Épardaud

by Stéphane Épardaud

Lately I’ve had to program an application that accesses an old Solid database that we use for “hysterical raisins” (as my co-worker on the project calls it), and found that our preferred JPA implementation Hibernate needed a little nudge to let you use it properly. This article explains the tricks to get you started.

Does Hibernate support Solid?

Yes of course, Hibernate supports tons of databases including… Wait a minute no. Not including Solid. Unless… Unless someone wrote support for Solid in Hibernate more than three years ago, went through the trouble of sending a patch to Hibernate to share his good work and it sat there sad, lonely and simply ignored like so many other bug reports and patches on Hibernate’s bug tracking system (really a lot, and I’m only talking about those I know because I hit the same bugs). To be fair, Solid (especially this old version) is not as common as most of the other supported databases, and maintaining the support for new databases comes at a cost.

Does it still work then? Hell yeah it does. It’s written for Hibernate 3.1.3 (but works for 3.2.4.sp1) and Solid 4.5 which turns out to be the version of Solid we use.

Just download that Hibernate Solid dialect, save it in your JAR and tell Hibernate to use it in your persistence.xml by setting the hibernate.dialect property to the fully-qualified name of the Solid Dialect.

So what else do I need?

You also need to find the Solid JDBC driver in your Solid installation, and put it in your application’s classpath so that Hibernate can use it.

At that point you must know there is at least one known bug in the Solid JDBC driver, related to timestamps. It turns out that the Solid JDBC driver has a bug in the ResultSet.getTimestamp and PreparedStatement.setTimestamp which makes it impossible to use timestamps using JPA with Hibernate without some trickery.

There are several ways to get a timestamp out or into JDBC, either as a direct type (which doesn’t work here) or as a string (“2009-09-28 15:23:00.00” for example) and luckily it is possible to define custom type mappings in Hibernate so this is what we will do.

Defining a custom Hibernate type to fix a Solid JDBC bug

Defining the custom type is as simple (the length of the code speaks for itself) as follows:

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;

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

public class TimestampType implements UserType {

 protected static final int[] SQL_TYPES_UTC = { Types.TIMESTAMP };

 protected final static Class objectClass = java.sql.Timestamp.class;

 public int[] sqlTypes() {
  return SQL_TYPES_UTC;
 }

 public boolean equals(Object x, Object y) {
  return (x == null) ? (y == null) : x.equals(y);
 }

 public boolean isMutable() {
  return true;
 }

 public Class returnedClass() {
  return objectClass;
 }

 public int hashCode(Object x) throws HibernateException {
  return x.hashCode();
 }

 public Serializable disassemble(Object value) throws HibernateException {
  return (Serializable) deepCopy(value);
 }

 public Object assemble(Serializable cached, Object owner)
   throws HibernateException {
  return deepCopy(cached);
 }

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

 public Object deepCopy(Object value) {
  if (value == null)
   return null;
  Timestamp copy = new Timestamp(((Timestamp) value).getTime());
  copy.setNanos(((Timestamp) value).getNanos());
  return copy;
 }

 public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
   throws SQLException {
  String timestamp = rs.getString(names[0]);
  if (timestamp == null)
   return null;
  try {
   return Timestamp.valueOf(timestamp);
  } catch (IllegalArgumentException e) {
   throw new SQLException("Failed to convert date: ", e);
  }
 }

 public void nullSafeSet(PreparedStatement st, Object value, int index)
   throws SQLException {
  if (value == null) {
   st.setString(index, null);
  } else {
   String timestamp = ((Timestamp) value).toString();
   st.setString(index, timestamp);
  }
 }
}

In fact only the sqlTypes, returnedClass, nullSafeGet and nullSafeSet are interesting, and they quite clearly instruct Hibernate that this type is about java.sql.Timestamp types, and converts them to and from String objects.

Once you have this type you need to name it in your package-info.java:

@TypeDefs( { @TypeDef(name = "timestamp", typeClass = TimestampType.class) })
package com.lunatech.solid;

import com.lunatech.solid.TimestampType;

import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;

Once that is done you can use the custom type in your entities as follows:

@Entity
public class Foo {
 @Type(type = "timestamp")
 private Timestamp myTimestamp;
 // …
}

Conclusion

We’ve been using Solid 4.5 with Hibernate for a while now and have never had any problem other than the initial surprises described above. If you every need, for various reasons, to use Solid with Hibernate, this should be all you need to get you started.