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!
December 19, 2018 @ 12:28 am
Thanks! This is exactly what I was looking for, and published just when I needed it 🙂
September 22, 2020 @ 8:22 am
Thank you – this is what I was missing.
I’m using jOOQ to store version 1 UUIDs in the database using the optimization described in this MariaDB post https://mariadb.com/kb/en/guiduuid-performance/ (the MariaDB/MySQL function works well for manualy queries, but not so well for jOOQ DAOs), so your converter here needed some adjustments. I’ve added the following filter methods that I apply to the top-most `long` in the `UUID` conversions:
/**
* Reorder the 64 MSB so version 1 UUIDs look to be monotonic
* @param u UUID top half
* @return UUID top half reordered
*/
private long toMonotonic(long u) {
return ByteBuffer.allocateDirect(8)
.putShort((short) (u & 0xFFFF)) // 3rd group
.putShort((short) ((u >> 16) & 0xFFFF)) // 2nd group
.putInt((int)((u >> 32) & 0xFFFFFFFFL)) // 1st group
.flip()
.getLong();
}
/**
* Reorder the “monotonic”-styled UUID top half so it conforms to standard UUID
* @param u UUID top half
* @return UUID top half de-reordered
*/
private long fromMonotonic(long u) {
return ByteBuffer.allocateDirect(8)
.putInt((int)(u & 0xFFFFFFFFL))
.putShort((short)((u >> 32) & 0xFFFF))
.putShort((short)((u >> 48) & 0xFFFF))
.flip()
.getLong();
}