2

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)

Gerardo Suárez Trejo
  • 21
  • 2
  • _"it is not what I expect"_, could you be more specific about what exactly the problem is? – Mark Rotteveel Nov 15 '17 at 17:45
  • 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](https://www.firebirdsql.org/file/documentation/drivers_documentation/java/3.0.2/release_notes.html#character-set-octets-handled-as-jdbc-varbinary). Also, `CHAR(16) CHARACTER SET OCTETS` is a 'binary' type, not 'varbinary'. – Mark Rotteveel Nov 15 '17 at 17:53
  • Mark thanks for your fast response. I edited my question (trying to be clearer). Is Jaybir 3.0.2 ready for production? – Gerardo Suárez Trejo Nov 15 '17 at 18:10
  • 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. – Gerardo Suárez Trejo Nov 15 '17 at 18:20
  • 1
    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. – Mark Rotteveel Nov 15 '17 at 18:36
  • Did you try forced type UUID instead of VARBINARY (or BINARY)? – Mark Rotteveel Nov 15 '17 at 18:43
  • Mark thanks for all your comments. I will follow all your advice and I'll see what happens. I forced VARBINARY – Gerardo Suárez Trejo Nov 15 '17 at 18:49
  • 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. – Gerardo Suárez Trejo Nov 15 '17 at 19:12
  • 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 ;) – Mark Rotteveel Nov 15 '17 at 19:15

1 Answers1

2

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())

Lukas Eder
  • 181,694
  • 112
  • 597
  • 1,319