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

Extend jOOQ with custom types

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

To support simple vendor specific SQL syntax extensions, jOOQ offers the plain SQL templating API. If a SQL clause is too complex to express with jOOQ or with this templating API, or you have a requirement to support different dialects, you can extend either one of the following types for use directly in a jOOQ query:

// Simplified API description:
public abstract class CustomField<T> implements Field<T> {}
public abstract class CustomCondition implements Condition {}
public abstract class CustomTable<R extends TableRecord<R>> implements Table<R> {}
public abstract class CustomRecord<R extends TableRecord<R>> implements TableRecord<R> {}

These classes are declared public and covered by jOOQ's integration tests. When you extend these classes, you will have to provide your own implementations for the QueryParts' toSQL() and bind() methods, as discussed before:

// This method must produce valid SQL. If your QueryPart contains other parts, you may delegate SQL generation to them
// in the correct order, passing the render context.
// If context.inline() is true, you must inline all bind variables
// If context.inline() is false, you must generate ? for your bind variables
public void toSQL(RenderContext context);

// This method must bind all bind variables to a PreparedStatement. If your QueryPart contains other QueryParts, $
// you may delegate variable binding to them in the correct order, passing the bind context.
// Every QueryPart must ensure, that it starts binding its variables at context.nextIndex().
public void bind(BindContext context) throws DataAccessException;

An example for implementing multiplication.

The above contract may be a bit tricky to understand at first. The best thing is to check out jOOQ source code and have a look at a couple of QueryParts, to see how it's done. Here's an example org.jooq.impl.CustomField showing how to create a field multiplying another field by 2

// Create an anonymous CustomField, initialised with BOOK.ID arguments
final Field<Integer> IDx2 = new CustomField<Integer>(BOOK.ID.getName(), BOOK.ID.getDataType()) {
    public void toSQL(RenderContext context) {
        // In inline mode, render the multiplication directly
        if (context.inline()) {
            context.sql(BOOK.ID).sql(" * 2");
        // In non-inline mode, render a bind value
        else {
            context.sql(BOOK.ID).sql(" * ?");

    public void bind(BindContext context) {
        try {
            // Manually bind the value 2
            context.statement().setInt(context.nextIndex(), 2);
            // Alternatively, you could also write:
            // context.bind(DSL.val(2));
        catch (SQLException e) {
            throw new DataAccessException("Bind error", e);

// Use the above field in a SQL statement:;

An example for implementing vendor-specific functions.

Many vendor-specific functions are not officially supported by jOOQ, but you can implement such support yourself using CustomField, for instance. Here's an example showing how to implement Oracle's TO_CHAR() function, emulating it in SQL Server using CONVERT():

// Create a CustomField implementation taking two arguments in its constructor
class ToChar extends CustomField<String> {

    final Field<?> arg0;
    final Field<?> arg1;

    ToChar(Field<?> arg0, Field<?> arg1) {
        super("to_char", VARCHAR);

        this.arg0 = arg0;
        this.arg1 = arg1;

    public void toSQL(RenderContext context) {

    public void bind(BindContext context) {

    private QueryPart delegate(Configuration configuration) {
        switch (configuration.dialect().family()) {
            case ORACLE:
                return DSL.field("TO_CHAR({0}, {1})", String.class, arg0, arg1);

            case SQLSERVER:
                return DSL.field("CONVERT(VARCHAR(8), {0}, {1})", String.class, arg0, arg1);

                throw new UnsupportedOperationException("Dialect not supported");

The above CustomField implementation can be exposed from your own custom DSL class:

public class MyDSL {
    public static Field<String> toChar(Field<?> field, String format) {
        return new ToChar(field, DSL.inline(format));


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

The jOOQ Logo