Mapping MySQL binary columns to UUIDs with jOOQ

Working on a project using MySQL and Java, I quickly found myself missing the more robust SQL and data types of PostgreSQL. 🙂

With a conversion off the table, we needed a way to handle mapping MySQL binary(16) columns to Java UUIDs and back automatically. I was surprised not to find any ready-to-run examples out there and thought I should share our method of using a custom Converter configured in jooq-codegen-maven.

Here’s our converter code:

package com.internetstaff.mysql.util;

import org.jooq.Converter;

import java.nio.ByteBuffer;
import java.util.UUID;

public class ByteArrayToUUIDConverter implements Converter<byte[], UUID> {

  @Override
  public final UUID from(byte[] t) {
    if (t == null) {
      return null;
    }

    ByteBuffer bb = ByteBuffer.wrap(t);
    return new UUID(bb.getLong(), bb.getLong());
  }

  @Override
  public final byte[] to(UUID u) {
    if (u == null) {
      return null;
    }

    return ByteBuffer.wrap(new byte[16])
        .putLong(u.getMostSignificantBits())
        .putLong(u.getLeastSignificantBits())
        .array();
  }

  @Override
  public Class<byte[]> fromType() {
    return byte[].class;
  }

  @Override
  public Class toType() {
    return UUID.class;
  }
}

… and here’s our (simplified) Maven configuration. Note that our configuration is converting any columns that are both 1) binary(16) and 2) have a name ending in “ID”. This works for us, but YMMV.

<database>
    <name>org.jooq.meta.mysql.MySQLDatabase</name>                           
    <forcedTypes>
        <forcedType>
            <userType>java.util.UUID</userType>
            <types>BINARY\(16\)</types>
            <expression>.*\.*ID</expression> 
            <converter>com.internetstaff.mysql.util.ByteArrayToUUIDConverter</converter>
        </forcedType>
    </forcedTypes> 
</database>

While jOOQ is a wonderful tool, I’ve found the documentation a little bit chaotic. I seem to find new things I’ve worked around are actually barely-documented features. If you know of a better way to handle this, please comment!