Is it possible to implement MySQL JSON_MERGE_PATCH with JOOQ data binding?

Question

Question:

In my MySQL database, I have an info column of JSON type: info json DEFAULT NULL,

I wrote a simple converter as follows and registered it with the code generator:

class JsonMapConverter : AbstractConverter<JSON, Map<*, *>>(JSON::class.java, Map::class.java) {
    override fun from(databaseObject: JSON?): Map<*, *> =
        jacksonObjectMapper().readValue(databaseObject.toString(), Map::class.java)

    override fun to(userObject: Map<*, *>): JSON =
        JSON.json(jacksonObjectMapper().writeValueAsString(userObject))
}

Insert and update work fine with this converter. However, now I want to update the JSON field with MySQL's JSON_MERGE_PATCH instead of simply replacing the entire JSON object on every update.

I thought of jOOQ data binding, but after a while of researching, my head started to spin because I am very unfamiliar with JDBC.

Is it actually possible to achieve what I want with jOOQ data binding?

Versions:

• jOOQ: 3.14.4
• Kotlin: 1.4.10
• Database: MySQL 5.7.32

Answer 1

Whenever jOOQ is missing support for a vendor specific feature, use plain SQL templating.

fun jsonMergePatch(vararg fields: Field<Map<*, *>>): Field<Map<*, *>> = DSL.field(
    "json_merge_patch({0})", 
    SQLDataType.JSON.asConvertedDataType(JsonMapConverter()),
    DSL.list(*fields)
);

Now you can use it as if it were an ordinary jOOQ function.

Alternatively, if you don't want to link this functionality to a specific converter, do this:

fun <T> jsonMergePatch(vararg fields: Field<T>): Field<T> = DSL.field(
    "json_merge_patch({0})", 
    fields[0].getDataType(),
    DSL.list(*fields)
);