[jooq] Add generic support for Postgres json(b) columns using Jackson JsonNode

[jooq] Add generic support for Postgres json(b) columns using jacksons JsonNode.md

Motivation

jooq does not support Postgres nativ json(b) datatypes out of the box. It would be nice have jooq recognize json columns and provide them in a generic way within generated records.

Idea

The official jooq documentation provides an example for creating a custom data type binding for Postgres json columns using GSON. Based on this, it is possible to tweak the implemenation to

• return Jackson JsonNode instead of GSON JsonElement
• use jsonb as the data type instead if json

Usage

According to the mentioned example you need two classes to be implemented, a Converter and a Binding. In addition the binding needs to be added the the generators database configuration within your applications pom.xml.

jooq/converter/JsonNodeConverter.java

package com.github.msievers.jooq.converter;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.jooq.Converter;

import java.io.IOException;
import java.util.Objects;

public class JsonNodeConverter implements Converter<Object, JsonNode> {

    private ObjectMapper objectMapper;

    public JsonNodeConverter() {
        objectMapper = new ObjectMapper();
    }

    @Override
    public JsonNode from(Object object) {

        if(Objects.isNull(object)) {
            return null;
        }

        try {
            return objectMapper.readTree(object.toString());
        } catch (IOException e) {
            return null;
        }
    }

    @Override
    public Object to(JsonNode jsonNode) {

        if(Objects.isNull(jsonNode)) {
            return null;
        }

        try {
            return objectMapper.writeValueAsString(jsonNode);
        } catch (JsonProcessingException e) {
            return null;
        }
    }

    @Override
    public Class<Object> fromType() {
        return Object.class;
    }

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

jooq/binding/JsonBinding.java

package com.github.msievers.jooq.binding;

import com.fasterxml.jackson.databind.JsonNode;
import com.github.msievers.jooq.converter.JsonNodeConverter;
import org.jooq.*;
import org.jooq.conf.ParamType;
import org.jooq.impl.DSL;

import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Types;
import java.util.Objects;

public class JsonBinding implements Binding<Object, JsonNode> {

    private final static String POSTGRES_JSON_TYPE = "jsonb";

    @Override
    public JsonNodeConverter converter() {
        return new JsonNodeConverter();
    }

    // Rendering a bind variable for the binding context's value and casting it to the jsonb type
    @Override
    public void sql(BindingSQLContext<JsonNode> bindingSQLContext) throws SQLException {
        // Depending on how you generate your SQL, you may need to explicitly distinguish
        // between jOOQ generating bind variables or inlined literals.
        if (bindingSQLContext.render().paramType() == ParamType.INLINED) {
            bindingSQLContext.render().visit(DSL.inline(bindingSQLContext.convert(converter()).value())).sql("::" + POSTGRES_JSON_TYPE);
        } else {
            bindingSQLContext.render().sql("?::" + POSTGRES_JSON_TYPE);
        }
    }

    // Registering VARCHAR types for JDBC CallableStatement OUT parameters
    @Override
    public void register(BindingRegisterContext<JsonNode> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
    }

    // Converting the JsonNode to a String value and setting that on a JDBC PreparedStatement
    @Override
    public void set(BindingSetStatementContext<JsonNode> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
    }

    // Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
    @Override
    public void set(BindingSetSQLOutputContext<JsonNode> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    // Getting a String value from a JDBC ResultSet and converting that to a JsonNode
    @Override
    public void get(BindingGetResultSetContext<JsonNode> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    // Getting a String value from a JDBC CallableStatement and converting that to a JsonElement
    @Override
    public void get(BindingGetStatementContext<JsonNode> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    // Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
    @Override
    public void get(BindingGetSQLInputContext<JsonNode> bindingGetSQLInputContext) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}

pom.xml

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${jooq-codegen-maven.version}</version>
    <configuration>
        <generator>
            <database>
                <name>org.jooq.meta.postgres.PostgresDatabase</name>
                <includes>.*</includes>
                <excludes>flyway_schema_history|qrtz.*</excludes>
                <inputSchema>public</inputSchema>
                <recordVersionFields>public.mapping_configuration.version</recordVersionFields>
                <forcedTypes>
                    <forcedType>
                        <userType>com.fasterxml.jackson.databind.JsonNode</userType>
                        <binding>com.github.msievers.jooq.binding.JsonBinding</binding>
                        <types>jsonb</types>
                    </forcedType>
                </forcedTypes>
            </database>
        </generator>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgresql.version}</version>
        </dependency>
    </dependencies>
</plugin>

Resources

• https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings