How to apply MySQL cryptography functions in Jooq custom Converter?

Question

I'm trying to achieve below using Jooq library,

• Encrypt and decrypt specified column using org.jooq.util.mysql.MySQLDSL methods
• This must be a jooq global configuration, so that whenever a new query method return fetch/insert/update the record encryption/decryption should be automatically taken care.
• Secret key for encryption/decryption will be value from the another column in the same table
• I'm trying to avoid Spring framework here.

Currently I'm trying to do this using org.jooq.Converter and org.jooq.meta.jaxb.ForcedType But

• Not able to access org.jooq.RecordContext in the CustomConverter class to get the secret key
• Also I'm not able to make this generic configuration

Links I'm referring to:

• https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/util/mysql/MySQLDSL.html
• https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/util/mysql/MySQLDSL.html
• https://www.jooq.org/doc/3.18/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-converter/
• https://www.jooq.org/doc/latest/manual/sql-execution/fetching/data-type-conversion/

Update: In short this is what I'm trying to achieve through Jooq:

CREATE TABLE TESTCRYPT(
Id mediumint AUTO_INCREMENT,
Secret int NOT NULL,
encrypteddata BLOB,
PRIMARY KEY(Id)
);

INSERT INTO TESTCRYPT(Secret,encrypteddata) VALUE ('1234',AES_ENCRYPT('my-data','1234'));

SELECT Secret ,AES_DECRYPT(encrypteddata,Secret) from TESTCRYPT t ;

Answer 1

An out of the box feature

As of jOOQ 3.18, there isn't an out of the box feature for this sort of computation. I've created a feature request, as this isn't the first time someone was looking for it:

• https://github.com/jOOQ/jOOQ/issues/14913

Custom implementation on the server side

If you must encrypt / decrypt on the server side using SQL expressions, perhaps you can get away with the new experimental query object model traversal and replacement features.

The idea is that you:

• Specify which columns or types are supposed to be encrypted (either in a configuration or using a custom data type)
• Then transform all SQL queries to always
  • Replace Field<MyType> by Field<byte[]> using encryption in contexts where a bind variable is written
  • Replace generated SQL for all Field<MyType> to decryption in contexts where a projection is made
  • Leave other expressions untouched

Custom implementation on the client side

All of this seems much easier to achieve with a simple Converter, if you can encrypt / decrypt on the client instead of the server.