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 | 2.6

Extend jOOQ with custom types

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

If a SQL clause is too complex to express with jOOQ, 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;

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(Factory.val(2));
        catch (SQLException e) {
            throw translate(getSQL(), e);

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


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

The jOOQ Logo