Search code examples
javajooq

Customizing UUID type for mysql in jooq

I'm porting a java application to jooq.

Currently, I have separate classes for sql to support MySQL, MariaDB, and H2.

I store UUID types differently in each because they have varying support. Maria & H2 have UUID data types. MySQL doesn't so I used BINARY(16) and convert to/from a UUID myself

I'm not sure how best to handle this with jooq. jooq has a UUID type but for MySQL it creates a VARCHAR(36).

create.createTableIfNotExists("users")
    .column("user_uuid", SQLDataType.UUID.notNull())
    .execute();

Is there any way I can alter this for mysql so that it uses binary 16 and the UUID_TO_BIN functions when queried?

Or do I need to create a copy of this code just for mysql that uses SQLDataType.BINARY instead?

It may be worthwhile for me to just accept varchar. This table might have 100k records so the binary 16 was smaller file size than a varchar 36, but it means I'd need to override jooq every time that column is touched.

Solution

  • If this is worth optimising on a per dialect basis, then you should indeed use BINARY for those database products, and define a table with a dedicated data type Binding that can handle the conversion on a per-dialect basis. That way, you will hardly ever need to touch that column again, it will all be transparent (of course, comparisons and other operators might not work on BINARY, which work on VARCHAR, but that seems to be OK for you?)