How to use MySql BINARY(16) field as java.util.UUID attribute

[Oliver Wahl]

I've searched hi and low on the net to figure out an easy way to use an java.util.UUID type as record id. As this is a MariaDB instance I used a binary(16) field as storage.

Now on to the problem: I tried using a definition of a SQLDataType.UUID mapping as I understood that this is internally mapped to a java.util.UUID. The configuration used was:

<forcedType>

  <name>UUID</name>

 <expression>(?i:.*\..*_uuid)$/expression>

</forcedType>

Generated code did compile but fetching data always resulted in NULL values instead of the correctly stored UUIDs (and yes newRecord/store did work). A little debugging showed that default converter was trying to parse a String into a UUID and obviously the regex matcher didn't work on the binary data.

Switching to a 

<customType>

  <name>java.util.UUID</name>

 <converter>org.zbra.domain.UUIDConverter</converter>

</customType>

and 

<forcedType>

 <name>java.util.UUID</name>

 <expression>(?i:.*\..*_uuid)$/expression>

</forcedType>

implementing very straight forward

import org.jooq.Converter;

import java.nio.ByteBuffer;

import java.util.UUID;

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

private static final long serialVersionUID = 8213839434240264996L;

@Override

public UUID from(byte[] bytes) {

did work and fetching now returns correct values.

Was my assumption wrong that SQLDataType.UUID can be mapped to a binary(16)? Or how would it have to be configured? Feels to me that I rewrote boiler plate code...

  

[Lukas Eder]

Hello, and sorry for the delay

2015-01-25 4:03 GMT+01:00 Oliver Wahl <cow...@gmail.com>:

I've searched hi and low on the net to figure out an easy way to use an java.util.UUID type as record id. As this is a MariaDB instance I used a binary(16) field as storage.

Now on to the problem: I tried using a definition of a SQLDataType.UUID mapping as I understood that this is internally mapped to a java.util.UUID. The configuration used was:

<forcedType>

  <name>UUID</name>

 <expression>(?i:.*\..*_uuid)$/expression>

</forcedType>

Generated code did compile but fetching data always resulted in NULL values instead of the correctly stored UUIDs (and yes newRecord/store did work). A little debugging showed that default converter was trying to parse a String into a UUID and obviously the regex matcher didn't work on the binary data.

Yes, this currently only works if the database contains a VARCHAR representation of your UUID. I wonder if we can make the "forcedType-only" configuration work also for BINARY representations.

 

Switching to a 

<customType>

  <name>java.util.UUID</name>

 <converter>org.zbra.domain.UUIDConverter</converter>

</customType>

and 

<forcedType>

 <name>java.util.UUID</name>

 <expression>(?i:.*\..*_uuid)$/expression>

</forcedType>

implementing very straight forward

import org.jooq.Converter;

import java.nio.ByteBuffer;

import java.util.UUID;

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

private static final long serialVersionUID = 8213839434240264996L;

@Override

public UUID from(byte[] bytes) {

did work and fetching now returns correct values.

Yes, that's what we're doing in our integration tests for MySQL UUID columns represented as BINARY(16).

 

Was my assumption wrong that SQLDataType.UUID can be mapped to a binary(16)? Or how would it have to be configured? Feels to me that I rewrote boiler plate code...

We don't support that out of the box yet. Yes, it's boiler plate code, but there are tons of possible <T> / <U> pairs that could be useful to some users. People might even implement UUIDs as two BIGINT columns. We're waiting to see how adoption for org.jooq.Binding goes, and then, we might host community-contributed implementations. Shipping them with the core deliverable seems like overkill, and at the same time, we'd still be missing *some* pair of T/U.

I hope this helps,

Lukas

[Lukas Eder]

2015-01-29 16:21 GMT+01:00 Lukas Eder <lukas...@gmail.com>:

Hello, and sorry for the delay

2015-01-25 4:03 GMT+01:00 Oliver Wahl <cow...@gmail.com>:

I've searched hi and low on the net to figure out an easy way to use an java.util.UUID type as record id. As this is a MariaDB instance I used a binary(16) field as storage.

Now on to the problem: I tried using a definition of a SQLDataType.UUID mapping as I understood that this is internally mapped to a java.util.UUID. The configuration used was:

<forcedType>

  <name>UUID</name>

 <expression>(?i:.*\..*_uuid)$/expression>

</forcedType>

Generated code did compile but fetching data always resulted in NULL values instead of the correctly stored UUIDs (and yes newRecord/store did work). A little debugging showed that default converter was trying to parse a String into a UUID and obviously the regex matcher didn't work on the binary data.

Yes, this currently only works if the database contains a VARCHAR representation of your UUID. I wonder if we can make the "forcedType-only" configuration work also for BINARY representations.

I'm sorry, no we cannot make that work for BINARY representations, as we don't have any information about the actual database column type, if you're applying only "forcedType". In that case, all original type information is lost, and UUID is all we know.