Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Custom syntax elements
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 CustomStatement implements Statement {}
public abstract class CustomTable<R extends TableRecord<R>> implements Table<R> {}
public abstract class CustomRecord<R extends TableRecord<R>> implements TableRecord<R> {}
An example for implementing a custom table and its record.
Here's an example org.jooq.impl.CustomTable showing how to create a custom table with its field definitions, similar to what the code generator is doing.
public class BookTable extends CustomTable<BookRecord> {
public static final BookTable BOOK = new BookTable();
public final TableField<BookRecord, String> FIRST_NAME = createField(name("FIRST_NAME"), VARCHAR);
public final TableField<BookRecord, String> UNMATCHED = createField(name("UNMATCHED"), VARCHAR);
public final TableField<BookRecord, String> LAST_NAME = createField(name("LAST_NAME"), VARCHAR);
public final TableField<BookRecord, Short> ID = createField(name("ID"), SMALLINT);
public final TableField<BookRecord, String> TITLE = createField(name("TITLE"), VARCHAR);
protected BookTable() {
super(name("BOOK"));
}
@Override
public Class<? extends BookRecord> getRecordType() {
return BookRecord.class;
}
}
public class BookRecord extends CustomRecord<BookRecord> {
protected BookRecord() {
super(BookTable.BOOK);
}
}
An example for implementing custom multiplication.
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()) {
@Override
public void accept(Context<?> context) {
context.visit(BOOK.ID).sql(" * ").visit(DSL.val(2));
}
};
// Use the above field in a SQL statement:
create.select(IDx2).from(BOOK);
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;
}
@Override
public void accept(RenderContext context) {
context.visit(delegate(context.configuration()));
}
private QueryPart delegate(Configuration configuration) {
switch (configuration.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);
default:
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));
}
}
Alternatively, implement it using a simple lambda:
public class MyDSL {
public static Field<String> toChar(Field<?> field, String format) {
return CustomField.of("to_char", VARCHAR, ctx -> {
switch (ctx.family()) {
case ORACLE:
ctx.visit(DSL.field("TO_CHAR({0}, {1})", String.class, arg0, arg1));
break;
case SQLSERVER:
ctx.visit(DSL.field("CONVERT(VARCHAR(8), {0}, {1})", String.class, arg0, arg1));
break;
default:
throw new UnsupportedOperationException("Dialect not supported");
}
});
}
}
Feedback
Do you have any feedback about this page? We'd love to hear it!