JAW Speak

Jonathan Andrew Wolter

Using Hibernate UserType’s for oddly formatted legacy tables (with tests)

with 2 comments

Reading time: 6 – 9 minutes

If you are using Hibernate with an existing (ahem, “creative”) database schema sooner or later you will be stuck with a field that Hibernate can’t parse. Say there is a Dimension column which stores the integer dimensions as a string "003X001X010". That would be 3 by 1 by 10 units.

You can implement UserType to transparently convert a proprietorially formatted field into first class objects of your choice.

And you can do it in a testable manner.

I’ll show the tests first, then the custom UserType. Right after the bump. (Click the post permalink to read the rest).

/**
 * @author Jonathan Andrew Wolter
 */
public class DimensionsUserTypeTest {
 
  // Fortunately there is no work done in the constructor, so we can easily create an instance
  // of a DimensionsUserType for exercising.
  private DimensionsUserType dimensionsUserType = new DimensionsUserType();
 
  @Test
  public void readsCorrectDimensions() throws Exception {
    // See how we can easily call our "sprouted method" in the test
    assertEquals(new DimensionsDto(1,2,3), dimensionsUserType.processNullSafeGet("001X002X003"));
    assertEquals(new DimensionsDto(100,200,300), dimensionsUserType.processNullSafeGet("100X200X300"));
    assertEquals(new DimensionsDto(1,2,3), dimensionsUserType.processNullSafeGet("01X2X3"));
  }
 
  @Test
  public void readsInvalidDimensions() throws Exception {
    assertEquals(null, dimensionsUserType.processNullSafeGet(null));
  }
 
  @Test
  public void setsCorrectDimensions() throws Exception {
    assertEquals("001X002X003", dimensionsUserType.processNullSafeSet(new DimensionsDto(1,2,3)));
    assertEquals("100X200X300", dimensionsUserType.processNullSafeSet(new DimensionsDto(100, 200, 300)));
  }
 
  @Test
  public void setIncorrectDimensions() throws Exception {
    assertThat(dimensionsUserType.processNullSafeSet(new DimensionsDto(1000,2000,3000)), is(nullValue()));
    assertThat(dimensionsUserType.processNullSafeSet(new DimensionsDto(1000,2,3000)), is(nullValue()));
  }
 
  @Test
  public void setsDimensionsWithDecimalRoundsToInteger() throws Exception {
    assertEquals(new DimensionsDto(2, 2, 3), dimensionsUserType.processNullSafeGet("1.5X2.X3.4"));
    assertEquals(new DimensionsDto(1,0,3), dimensionsUserType.processNullSafeGet("1.0X0.2X3."));
  }
 
  @Test
  public void setsNullDimensions() throws Exception {
    assertThat(dimensionsUserType.processNullSafeSet(null), is(nullValue()));
  }
 
  @Test
  public void interfaceContractualMethods() throws Exception {
    assertEquals(DimensionsDto.class, dimensionsUserType.returnedClass());
    assertThat(dimensionsUserType.sqlTypes(), is(new int[] { Types.VARCHAR }));
  }
 
  @Test
  public void deepCopyAndEquals() throws Exception {
    // VERY IMPORTANT!
    // If this is not implemented correctly, you can potentially be forcing Hibernate to make
    // update calls on EVERY object containing a field of this UserType.
    assertTrue(dimensionsUserType.equals(null, dimensionsUserType.deepCopy(null)));
 
    DimensionsDto dimensions123 = new DimensionsDto(1, 2, 3);
    assertFalse(dimensionsUserType.equals(null, dimensionsUserType.deepCopy(dimensions123)));
    assertFalse(dimensionsUserType.equals(dimensions123, dimensionsUserType.deepCopy(null)));
 
    assertTrue(dimensionsUserType.equals(
            new DimensionsDto(1, 2, 3),
            dimensionsUserType.deepCopy(dimensions123)));
    assertFalse(dimensionsUserType.equals(
            new DimensionsDto(1, 2, 3),
            dimensionsUserType.deepCopy(new DimensionsDto(100, 2, 3))));
 
    DimensionsDto old = new DimensionsDto(100, 2, 3);
    assertNotSame(old, dimensionsUserType.deepCopy(old));
  }
}

The class under test is our implementation of UserType:

/**
 * Custom database field type to convert (transparently) between "004X005X006" varchar fields
 * into a DimensionsDto object of width, length and height properties.
 * @author Jonathan Andrew Wolter
 */
public class DimensionsUserType implements UserType {
 
  public Object assemble(Serializable arg0, Object arg1) throws HibernateException {
    // TODO Tests don't indicate we need to implement this yet.
    return null;
  }
 
  public Object deepCopy(Object dimensions) throws HibernateException {
    if (dimensions == null) {
      return null;
    }
    DimensionsDto dim = (DimensionsDto)dimensions;
    return new DimensionsDto(dim.getLength(), dim.getWidth(), dim.getHeight());
  }
 
  public Serializable disassemble(Object arg0) throws HibernateException {
    // TODO Tests don't indicate we need this now
    return null;
  }
 
  public boolean equals(Object dimensions1, Object dimensions2) throws HibernateException {
    // Extremely, ultra, very important to implement correctly, otherwise we could read out
    // a value from the database, make no changes to the object with the field of type
    // DimensionsUserType, and then isDirty would be true and Hibernate would be forced to make
    // an update statement for every object with this type read.
    if (dimensions1 == null && dimensions2 == null) {
      return true;
    } else if (dimensions1 == null || dimensions2 == null ) {
      return false;
    }
    return dimensions1.equals(dimensions2);
  }
 
  public int hashCode(Object dimensions) throws HibernateException {
    // Very important to implement correctly
    return dimensions.hashCode();
  }
 
  public boolean isMutable() {
    //  Tests are saying it is not mutable is easier for now, as the application is read only,
    // we may need to revisit this later.
    return false;
  }
 
  /** What Hibernate calls when it wants to read a field annoated as using the Type declared by
   * this UserType implementation. We convert the varcar field into a first class DimensionsDto
   * object.
   */
  public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner) throws HibernateException,
          SQLException {
    String rawDimensions = (String) Hibernate.STRING.nullSafeGet(resultSet, names[0]);
    return processNullSafeGet(rawDimensions);
  }
 
  /** Visible for testing. (Sprouted Method)
   *  We call this method directly for testing, because to exercise the {@code nullSafeGet} is
   * difficult in a test. (It would require a ResultSet to be passed in). This method is simple,
   * and can be hammered easily in tests. */
  DimensionsDto processNullSafeGet(String rawDimensions) {
    if (rawDimensions == null) {
      return null;
    }
    String[] split = rawDimensions.split("X");
    if (split.length != 3) {
      // invalid dimensions
      return null;
    }
    return new DimensionsDto(
            Math.round(valueOf(split[0]).floatValue()),
            Math.round(valueOf(split[1]).floatValue()),
            Math.round(valueOf(split[2]).floatValue()));
  }
 
  /** The important entrypoint that Hibernate calls when we want to persist an object with a field
   * declared to have this UserType. The DimensionsDto object is passed in, and we then convert it
   * into the proprietary database schema used in the legacy database.
   */
  public void nullSafeSet(PreparedStatement ps, Object dimensions, int index) throws HibernateException,
          SQLException {
    Hibernate.STRING.nullSafeSet(ps, processNullSafeSet((DimensionsDto)dimensions), index);
  }
 
  /** Visible for testing. (Sprouted Method)
   *  We call into this method from the {@code nullSafeSet} above, because that is difficult to
   *  exercise in a test. However, we can call this method all day long. */
  String processNullSafeSet(DimensionsDto dim) {
    if (dim == null) {
      return null;
    }
    String formattedString = String.format("%03dX%03dX%03d", dim.getLength(), dim.getWidth(), dim.getHeight());
    if (formattedString.length() != 11) {
      // could throw, but we'll be nice instead, because the data is sometimes dirty.
       return null;
    }
    return formattedString;
  }
 
  public Object replace(Object arg0, Object arg1, Object arg2) throws HibernateException {
    // TODO not sure if we need this, tests say we don't so far
    return null;
  }
 
  /** This is the type that we convert TO when using this custom user type. Anywhere you
   * want to use a DimensionsDto object, annotate it with Type(type='xyz.MyUserType') */
  public Class returnedClass() {
    return DimensionsDto.class;
  }
 
  /** This is the SQL time that is stored in the column of the database */
  public int[] sqlTypes() {
    // We return a new array each time, becuase if we used a final array, the elements within
    // could still be mutated.
    return new int[] { Types.VARCHAR };
  }
 
}

If you want to read more about this, check out:

The alternative to using UserTypes is to have your application code do the translations. And you could isolate this potentially within your Repository / DAO layer. So you’d guarantee any object handed off to the rest of your application would have Dimension objects, and not an unparsed String. And that still might be the best thing to do sometimes, because UserTypes have one fatal flaw.

The fatal flaw with UserType’s is they offer no opportunity for dependency injection or configuration. This is a huge disappointment. This means if you need to do more complicated parsing of the raw field, you may be forced to reach out in to the ether and use a static method. Example: what if we parsed some string out of the raw sql row which was a foreign key to another table. How can we look that up? We can’t inject in a service to do this lookup. Read more at a thread over on the Spring Forums about the lack of DI. If you have any doubts of Singletons and Statics as the worst thing ever, please stand up and argue your point.

Bookmark and Share

Written by Jonathan

November 10th, 2008 at 1:23 am

Posted in code, java

Tagged with

2 Responses to 'Using Hibernate UserType’s for oddly formatted legacy tables (with tests)'

Subscribe to comments with RSS or TrackBack to 'Using Hibernate UserType’s for oddly formatted legacy tables (with tests)'.

  1. Thanks Jonathan. With regards to lack of DI, if you are willing to do bytecode enhancement with spring @configurable that would probably work.

    James

    10 Nov 08 at 8:31 am

  2. Glad you took the chance to post back. I’m more of a Google Guice guy than Spring user, but I will have to look into this.

    Jonathan

    11 Nov 08 at 10:31 pm

Leave a Reply