Available in versions: 3.8

Custom data type binding

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The previous section discussed the case where your custom data type is mapped onto a standard JDBC type as contained in org.jooq.impl.SQLDataType. In some cases, however, you want to map your own type onto a type that is not explicitly supported by JDBC, such as for instance, PostgreSQL's various advanced data types like JSON or HSTORE, or PostGIS types. For this, you can register an org.jooq.Binding for relevant columns in your code generator. Consider the following trivial implementation of a binding for PostgreSQL's JSON data type, which binds the JSON string in PostgreSQL to a Google GSON object:

import static org.jooq.tools.Convert.convert;
import java.sql.*;
import org.jooq.*;
import org.jooq.impl.DSL;
import com.google.gson.*;

// We're binding <T> = Object (unknown JDBC type), and <U> = JsonElement (user type)
public class PostgresJSONGsonBinding implements Binding<Object, JsonElement> {

    private final Gson gson = new Gson();

    // The converter does all the work
    public Converter<Object, JsonElement> converter() {
        return new Converter<Object, JsonElement>() {
            public JsonElement from(Object t) {
                return t == null ? JsonNull.INSTANCE : gson.fromJson("" + t, JsonElement.class);

            public Object to(JsonElement u) {
                return u == null || u == JsonNull.INSTANCE ? null : gson.toJson(u);

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

            public Class<JsonElement> toType() {
                return JsonElement.class;

    // Rending a bind variable for the binding context's value and casting it to the json type
    public void sql(BindingSQLContext<JsonElement> ctx) throws SQLException {
        // Depending on how you generate your SQL, you may need to explicitly distinguish
        // between jOOQ generating bind variables or inlined literals.
        if (ctx.render().paramType() == ParamType.INLINED)

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

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

    // Getting a String value from a JDBC ResultSet and converting that to a JsonElement
    public void get(BindingGetResultSetContext<JsonElement> ctx) throws SQLException {

    // Getting a String value from a JDBC CallableStatement and converting that to a JsonElement
    public void get(BindingGetStatementContext<JsonElement> ctx) throws SQLException {

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

    // Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
    public void get(BindingGetSQLInputContext<JsonElement> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();

Registering bindings to the code generator

The above org.jooq.Binding implementation intercepts all the interaction on a JDBC level, such that jOOQ will never need to know how to correctly serialise / deserialise your custom data type. Similar to what we've seen in the previous section about how to register Converters to the code generator, we can now register such a binding to the code generator. Note that you will reuse the same types of XML elements (<customType/> and <forcedType/>):

  <!-- Optionally, reuse your configuration via a <customType/>, first -->

      <!-- Specify the Java type of your custom type. This corresponds to the Binding's <U> type. -->

      <!-- Associate that custom type with your binding. -->

      <!-- A Java regex matching fully-qualified columns, attributes, parameters. Use the pipe to separate several expressions.
           If provided, both "expressions" and "types" must match. -->
      <!-- A Java regex matching data types to be forced to
           have this type.
           Data types may be reported by your database as:
           - NUMBER              regexp suggestion: NUMBER
           - NUMBER(5)           regexp suggestion: NUMBER\(5\)
           - NUMBER(5, 2)        regexp suggestion: NUMBER\(5,\s*2\)
           - any other form
           It is thus recommended to use defensive regexes for types.
           If provided, both "expressions" and "types" must match. -->

See also the section about data type rewrites to learn about an alternative use of <forcedTypes/>.

The above configuration will lead to AUTHOR.CUSTOM_DATA_JSON being generated like this:

public class TAuthor extends TableImpl<TAuthorRecord> {

    // [...]
    public final TableField<TAuthorRecord, JsonElement> CUSTOM_DATA_JSON =    // [...]
    // [...]



Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo