Stack Exchange
Using UUID PK or FK in Firebird with Jooq
I have a table in Firebird with a PK column
CREATE TABLE TEST
(
  ID CHAR(16) CHARACTER SET OCTETS NOT NULL,
  CONSTRAINT PK_TEST PRIMARY KEY (ID)
);

OCTETS encoding are treated as bytes.
I create a converter
public class UUIDConverter implements Converter<byte[], UUID>{

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

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

    @Override
    public UUID from(byte[] bytes) {
        if (bytes == null) return null;
        ByteBuffer bb = ByteBuffer.wrap(bytes);
        long high = bb.getLong();
        long low = bb.getLong();
        return new UUID(high, low);
    }

    @Override
    public byte[] to(UUID uuid) {
        if (uuid == null) return null;
        byte[] buffer = new byte[16];
        ByteBuffer bb = ByteBuffer.wrap(buffer);
        bb.putLong(uuid.getMostSignificantBits());
        bb.putLong(uuid.getLeastSignificantBits());
        return buffer;
    }
}

Then I configured the converter in my pom (I'm using maven to generate the source)
<customTypes>
    <customType>
        <name>UUID</name>
        <converter>com.vas.database.UUIDConverter</converter>
    </customType>
</customTypes>
<forcedTypes>
    <forcedType>
        <name>VARBINARY</name>
        <expressions>ID|ID_.*</expressions>
    </forcedType>
</forcedTypes>

Although the code is generated, it is not what I expect. 
public final TableField<TestRecord, byte[]> ID = createField("ID", org.jooq.impl.SQLDataType.VARBINARY.nullable(false), this, "");

This is what I would like it to be generated (I made the changes by hand and everything works wonderfully).
public final TableField<TestRecord, UUID> ID = createField("ID", org.jooq.impl.SQLDataType.VARBINARY.nullable(false), this, "", new UUIDConverter());

How do I make it work?
(By the way, I'm using Firebird 3.0, Jooq 3.10.1 and Jaybird 2.2.13)

"it is not what I expect", could you be more specific about what exactly the problem is?

For example, what code does it generate, and why doesn't that work for you? BTW: Consider upgrading to Jaybird 3.0.2, it has better handling of character set octets (see Character set OCTETS handled as JDBC (VAR)BINARY. Also, CHAR(16) CHARACTER SET OCTETS is a 'binary' type, not 'varbinary'.

Mark thanks for your fast response. I edited my question (trying to be clearer). Is Jaybir 3.0.2 ready for production?

Something else, VARBINARY is a data type from Jooq as you can see here
rg.jooq.impl.SQLDataType.VARBINARY) and It is not a Jaybird data type.

Jaybird 3.0.2 is a released version, so yes, it is ready for production. But consult the release notes, and test it, because there are a number of breaking changes compared to 2.2.x. In any case SQLDataType.BINARY is a closer match to the intent of CHAR(16) CHARACTER SET OCTETS than varbinary. Not sure if it matters for this problem though.

Did you try forced type UUID instead of VARBINARY (or BINARY)?

Mark thanks for all your comments. I will follow all your advice and I'll see what happens. I forced VARBINARY

Yes, you are right. If I forced to UUID,  the data type I got is org.jooq.impl.SQLDataType.UUID instead of  org.jooq.impl.SQLDataType.BINARY and After that I got I compilation error. If there also a chance to force Jooq BINARY data type? That will solve the problem.

I haven't done much with this part of jOOQ, I would need to try a few things to see what works. Maybe Lukas sees this question before that time ;)

There are several things to explain here:
1. Your config is wrong
In your current configuration, only this part applies to the code generator, rewriting your CHAR data type to VARBINARY using the data type rewriting feature:
<forcedTypes>
    <forcedType>
        <name>VARBINARY</name>
        <expressions>ID|ID_.*</expressions>
    </forcedType>
</forcedTypes>

The custom type for the UUID type is never applied because you don't force any column to the UUID type. (btw, you shouldn't name your custom type UUID, because that conflicts with SQLDataType.UUID)
2. You cannot rewrite a type twice
The current version of jOOQ 3.10 doesn't allow you to rewrite a type twice, i.e. from CHAR to VARBINARY and then from VARBINARY to your user-defined type. This is a limitation which might be fixed in a future version.
But right now, you have to implement this in one go.
By default, jOOQ would read / write CHAR types using java.lang.String and JDBC's corresponding PreparedStatement.setString() and ResultSet.getString() methods, regardless of any encoding / collation / etc. This is probably not what you want. Instead, you should not implement a Converter but a Binding as documented here:
https://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings
It allows you to "bind" your custom data type directly to JDBC, bypassing jOOQ's internal DefaultBinding semantics (which would call setString() and getString())
