User Types in Hibernate

Recently, I ran in to a series of vexing problems that each had the same solution: User Types in Hibernate. All of these problems originated because I was dealing with a mainframe database which I was trying to access using Hibernate.

Consider the following situations.

Example 1: Integer Date Formats

Let’s start with one of the most straight-forward oddities. Our database had a number of dates — year, month and day information. Rather than create a column type of DATE, the database designers instead decided to use a column type of Integer and stored values like 19950927 (to represent Sept. 27th, 1995).

Now, Hibernate can easily map an Integer column, but ultimately we wanted to be able to work with the type as a Java Date object. The ugly way of dealing with the problem is to have two different sets of getters/setters, like this:

public Integer getCreationDateAsInteger() {
  return this.creationDate;
}
public Date getCreationDate() {
  return DateUtil.convertIntegerToDate(this.creationDate);
}

Using this approach, we’d map the creationDateAsInteger property to the database column, and the other property would not be mapped to a column. That works, but it has the minor disadvantage of cluttering up our code.

Instead, we created an IntegerDateUserType class like this:

public class IntegerDateUserType implements UserType {

  private static final int[] SQL_TYPES = { Types.NUMERIC };

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

  public Class returnedClass() {
    return Date.class;
  }

  public boolean equals(Object x, Object y)
      throws HibernateException {
    if (x == y) {
      return true;
    } else if (x == null || y == null) {
      return false;
    } else {
      return x.equals(y);
    }
  }

  public Object nullSafeGet(ResultSet resultSet,
      String[] names, Object owner)
      throws HibernateException, SQLException {
    Date result = null;
    int dateAsInt = resultSet.getInt(names[0]);
    if (!resultSet.wasNull()) {
      result = dateAsInt == 0
        ? null
        : DateUtil.convertIntegerToDate(new Integer(dateAsInt));
    }
    return result;
  }

  public void nullSafeSet(PreparedStatement statement,
      Object value, int index)
      throws HibernateException, SQLException {
    if (value == null) {
      statement.setInt(index, 0);
    } else {
      Integer dateAsInteger =
        DateUtil.convertDateToInteger((Date) value);
      statement.setInt(index, dateAsInteger);
    }
  }

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

  public boolean isMutable() {
    return false;
  }
}

The two most important methods on this class are the nullSafeGet and the nullSafeSet. The nullSafeGet is used whenever a record is being read from the database — Hibernate would call the nullSafeGet with the Integer value, and we’d convert it into a Date. The nullSafeSet is used whenever we are trying to persist a record, or if we’re trying to construct a query using our custom date type. In these cases, we’d have a Date, and we’d want to convert it into an Integer.

To make use of this User Type, we’d configure a property on our mapping file like this:

<property
    name="creationDate"
    type="ca.intelliware.example.hibernate.IntegerDateUserType"
    update="true"
    insert="true"
    access="property"
    column="CREATION_DATE"
/>

Once all that was in place, Hibernate would perform automatic conversion of our dates. By the time the data was in our Java object, we’d have a Java Date class. When we’d persist the object, we’d get a database Integer type.

Example 2: Encoding Data User Type

Another case for which you might want to use a User Type is to encode certain pieces of data on the database — say, credit card numbers or something like that.

We discovered that the legacy database was encoding certain key values, not for privacy reasons, but to prevent page-level locking in the database. We were using a version of DB2 on the mainframe, and it didn’t support row-level locking. Suppose three different users were trying to insert new records into the same table. And suppose we gave these records simple, incremental ids like 31, 32, and 33. Chances are good that the ids would end up on the same DB page, and there’d be some contention. One user would have locked the page and the other users would get contention errors, even though they’re working with completely different records.

The solution is to make the ids non-adjacent, using some kind of randomizer routine or encoding algorithm. That way, we lessen the likelihood that the ids are on the same page, and lower the likelihood of contention.

In our legacy database, the randomized key for the table would be created by scrambling the numbers in an account number. If someone wanted to look up an account, they would type in an account number, and we’d have to encode the number by scrambling the digits, and then look for a record with the scrambled number as a key. Like this:

public Account getAccount(Integer accountNumber) throws ... {
  Integer encodedAccountNumber = EncodingAlgorithm.scramble(accountNumber);
  return getRecordById(encodedAccountNumber);
}

As in Example 1, that worked just fine. But we found ourselves making mistakes by sometimes passing around scrambled or unscrambled versions of the account number, and that caused us some grief. Hibernate User Types, though make the process a lot more transparent.

First we’d create our user type class:

public class EncodableNumberUserType implements UserType {

  private static final int[] SQL_TYPES = { Types.NUMERIC };

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

  public Class returnedClass() {
    return Integer.class;
  }

  public boolean equals(Object x, Object y) throws HibernateException {
    if (x == y) {
      return true;
    } else if (x == null || y == null) {
      return false;
    } else {
      return x.equals(y);
    }
  }

  public Object nullSafeGet(ResultSet resultSet,
      String[] names, Object owner)
      throws HibernateException, SQLException {
    Integer result = null;
    int encodedNumber = resultSet.getInt(names[0]);
    if (!resultSet.wasNull()) {
      result = new Integer(EncodingAlgorithm.unscramble(encodedNumber)));
    }
    return result;
  }

  public void nullSafeSet(PreparedStatement statement,
      Object value, int index)
      throws HibernateException, SQLException {
    Integer unencodedNumber = (Integer) value;
    statement.setInt(index, EncodingAlgorithm.scramble(unencodedNumber));
  }

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

  public boolean isMutable() {
    return false;
  }
}

As before, we’d configure this user type in our Hibernate mapping file, and when we’d read an object from the database, our Java representation would already have unscrambled the account number. Every time we looked at the account number on our Account object, it’d be unscrambled. Every time we’d write it to the database, it’d be scrambled.

One special case we should mention is this, though: when we’d write a query, we’d have to remember that we were using a special type. Here’s an example:

Query query = session.createQuery(
  "from Account as account where account.number = :accountNumber");
query.setParameter("accountNumber", accountNumber,
  new CustomType(EncodableNumberUserType.class));
return query.uniqueResult();

Example 3: Null Types

In the final example, our legacy database decided to avoid nulls in records. All of the existing (COBOL) code that read the database recognized that certain default values indicated that a value hadn’t been set. For example, a CHAR column might be set to spaces to indicate that no value had been set.

While this strategy was mostly ignorable, it did present some special problems when we were dealing with relationships. The legacy database did not use referential integrity, and so it was completely possible for them to set a foreign key to 0 or to blanks to indicate that the relationship didn’t exist.

Using Hibernate, we’d run into errors because Hibernate wouldn’t be able to find a corresponding entry on the foreign table with that default key and would throw an exception. (Hibernate 3 has a configuration option that allows it to ignore this situation, but Hibernate 2, which we were using, does not).

Once again, User Types came to our rescue. This case was a bit less intuitive for us because it wasn’t quite clear where to put the type. Imagine this case. We have one class, the WorkItem, and it has a relationship (“assignedWorker”) to another persistent class, Worker. On the WORK_ITEM table, there’s a column, WORKER_ID, which indicates the assigend worker. If the WORKER_ID is blank, then WorkItem is unassigned.

First we created our UserType:

public class NullableStringUserType implements UserType {

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

  public Class returnedClass() {
    return String.class;
  }

  public boolean equals(Object x, Object y) throws HibernateException {
    if (x == y) {
      return true;
    } else if (x == null) {
      return false;
    } else {
      return x.equals(y);
    }
  }

  public Object nullSafeGet(ResultSet resultSet,
      String[] names, Object owner)
      throws HibernateException, SQLException {
    String result = resultSet.getString(names[0]);
    return result == null || result.trim().length() == 0
        ? null : result;
  }

  public void nullSafeSet(PreparedStatement statement,
      Object value, int index)
      throws HibernateException, SQLException {
    statement.setString(index, value == null ? " " :( String) value);
  }

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

  public boolean isMutable() {
    return false;
  }
}

What wasn’t obvious was how to configure this type. In our WorkItem.hbm.xml file, we’d only ever refer to the WORKER_ID column in the context of a many-to-one relationship:

<many-to-one
    name="assignedWorker"
    class="ca.intelliware.example.hibernate.Worker"
    cascade="none"
    outer-join="auto"
    update="false"
    insert="false"
    access="property" >
  <column name="WORKER_ID"/>
</many-to-one>

How do we fit the custom user type in there? Answer: we didn’t. We went over to the Worker.hbm.xml file:

<id name="id" column="WORKER_ID"
    type="ca.intelliware.example.hibernate.NullableStringUserType" >

Under the covers, when Hibernate instantiates a WorkItem, it uses the key type of the Worker object. Since that type is a Hibernate UserType, then it processes the data that it gets out of a WORK_ITEM table through the NullableStringUserType. The NullableStringUserType ends up returning a null to Hibernate, and Hibernate understands that to mean that the relationship does not exist.

Print Friendly
This entry was posted in Technology and tagged by BC Holmes. Bookmark the permalink.

About BC Holmes

BC Holmes is an architect with 20+ years experience designing and building applications, currently specializing in eHealth and mobile applications. She has worked in the IT Services industry and the financial services industry. She holds a joint honours in Pure Mathematics and Theatre Arts. BC has presented papers at JavaOne ("Developing an Internet Architecture for the Enterprise", "Fast Feedback Loop: JUnit Test Strategies for Tapestry"), XP Toronto ("Good Design: Know It When I See It?") and the Toronto WebSphere Users' Group ("Extreme WebSphere").

Leave a Reply