New versions: Dev (3.14) | Latest (3.13) | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6

Custom data type bindings

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

jOOQ supports all the standard SQL data types out of the box, i.e. the types contained in java.sql.Types. But your domain model might be more specific, or you might be using a vendor-specific data type, such as JSON, HSTORE, or some other data structure. If this is the case, this section will be right for you, we'll see how you can create org.jooq.Converter types and org.jooq.Binding types.


The simplest use-case of injecting custom data types is by using org.jooq.Converter. A Converter can convert from a database type <T> to a user-defined type <U> and vice versa. You'll be implementing this SPI:

public interface Converter<T, U> {

    // Your conversion logic goes into these two methods, that can convert
    // between the database type T and the user type U:
    U from(T databaseObject);
    T to(U userObject);

	// You need to provide Class instances for each type, too:
    Class<T> fromType();
    Class<U> toType();

If, for instance, you want to use Java 8's java.time.LocalDate for SQL DATE and java.time.LocalDateTime for SQL TIMESTAMP, you write a converter like this:

import java.sql.Date;
import java.time.LocalDate;

import org.jooq.Converter;

public class LocalDateConverter implements Converter<Date, LocalDate> {

    public LocalDate from(Date t) {
        return t == null ? null : LocalDate.parse(t.toString());

    public Date to(LocalDate u) {
        return u == null ? null : Date.valueOf(u.toString());

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

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

This converter can now be used in a variety of jOOQ API, most importanly to create a new data type:

DataType<LocalDate> type = SQLDataType.DATE.asConvertedDataType(new LocalDateConverter());

And data types, in turn, can be used with any org.jooq.Field, i.e. with any column expression, including plain SQL or name based ones:

DataType<LocalDate> type = SQLDataType.DATE.asConvertedDataType(new LocalDateConverter());

// Plain SQL based
Field<LocalDate> date1 = DSL.field("my_table.my_column", type);

// Name based
Field<LocalDate> date2 = DSL.field(name("my_table", "my_column"), type);


While converters are very useful for simple use-cases, org.jooq.Binding is useful when you need to customise data type interactions at a JDBC level, e.g. when you want to bind a PostgreSQL JSON data type. Custom bindings implement the following SPI:

public interface Binding<T, U> extends Serializable {

    // A converter that does the conversion between the database type T
    // and the user type U (see previous examples)
    Converter<T, U> converter();

    // A callback that generates the SQL string for bind values of this
    // binding type. Typically, just ?, but also ?::json, etc.
    void sql(BindingSQLContext<U> ctx) throws SQLException;

    // Callbacks that implement all interaction with JDBC types, such as
    // PreparedStatement, CallableStatement, SQLOutput, SQLinput, ResultSet
    void register(BindingRegisterContext<U> ctx) throws SQLException;
    void set(BindingSetStatementContext<U> ctx) throws SQLException;
    void set(BindingSetSQLOutputContext<U> ctx) throws SQLException;
    void get(BindingGetResultSetContext<U> ctx) throws SQLException;
    void get(BindingGetStatementContext<U> ctx) throws SQLException;
    void get(BindingGetSQLInputContext<U> ctx) throws SQLException;

Below is full fledged example implementation that uses Google Gson to model JSON documents in Java

import static;
import java.sql.*;
import org.jooq.*;
import org.jooq.impl.DSL;

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

    // 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 : new Gson().fromJson("" + t, JsonElement.class);

            public Object to(JsonElement u) {
                return u == null || u == JsonNull.INSTANCE ? null : new 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();

Code generation

There is a special section in the manual explaining how to automatically tie your Converters and Bindings to your generated code. The relevant sections are:

The jOOQ Logo