Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Plain SQL
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A DSL is a nice thing to have, it feels "fluent" and "natural", especially if it models a well-known language, such as SQL. But a DSL is always expressed in a host language (Java in this case), which was not made for exactly the same purposes as its hosted DSL. If it were, then jOOQ would be implemented on a compiler-level, similar to LINQ in .NET. But it's not, and so, the DSL is limited by language constraints of its host language. We have seen many functionalities where the DSL becomes a bit verbose. This can be especially true for:
You'll probably find other examples. If verbosity scares you off, don't worry. The verbose use-cases for jOOQ are rather rare, and when they come up, you do have an option. Just write SQL the way you're used to!
jOOQ allows you to embed SQL as a String into any supported statement in these contexts:
- Plain SQL as a conditional expression
- Plain SQL as a column expression
- Plain SQL as a function
- Plain SQL as a table expression
- Plain SQL as a query
The DSL plain SQL API
Plain SQL API methods are usually overloaded in three ways. Let's look at the condition query part constructor:
// Construct a condition without bind values
// Example: condition("a = b")
Condition condition(String sql);
// Construct a condition with bind values
// Example: condition("a = ?", 1);
Condition condition(String sql, Object... bindings);
// Construct a condition taking other jOOQ object arguments
// Example: condition("a = {0}", val(1));
Condition condition(String sql, QueryPart... parts);
Both the bind value and the query part argument overloads make use of jOOQ's plain SQL templating language.
Please refer to the org.jooq.impl.DSL Javadoc for more details. The following is a more complete listing of plain SQL construction methods from the DSL:
// A condition Condition condition(String sql); Condition condition(String sql, Object... bindings); Condition condition(String sql, QueryPart... parts); // A field with an unknown data type Field<Object> field(String sql); Field<Object> field(String sql, Object... bindings); Field<Object> field(String sql, QueryPart... parts); // A field with a known data type <T> Field<T> field(String sql, Class<T> type); <T> Field<T> field(String sql, Class<T> type, Object... bindings); <T> Field<T> field(String sql, Class<T> type, QueryPart... parts); <T> Field<T> field(String sql, DataType<T> type); <T> Field<T> field(String sql, DataType<T> type, Object... bindings); <T> Field<T> field(String sql, DataType<T> type, QueryPart... parts); // A field with a known name (properly escaped) Field<Object> field(Name name); <T> Field<T> field(Name name, Class<T> type); <T> Field<T> field(Name name, DataType<T> type); // A function <T> Field<T> function(String name, Class<T> type, Field<?>... arguments); <T> Field<T> function(String name, DataType<T> type, Field<?>... arguments); // A table Table<?> table(String sql); Table<?> table(String sql, Object... bindings); Table<?> table(String sql, QueryPart... parts); // A table with a known name (properly escaped) Table<Record> table(Name name); // A query without results (update, insert, etc) Query query(String sql); Query query(String sql, Object... bindings); Query query(String sql, QueryPart... parts); // A query with results ResultQuery<Record> resultQuery(String sql); ResultQuery<Record> resultQuery(String sql, Object... bindings); ResultQuery<Record> resultQuery(String sql, QueryPart... parts); // A query with results. This is the same as resultQuery(...).fetch(); Result<Record> fetch(String sql); Result<Record> fetch(String sql, Object... bindings); Result<Record> fetch(String sql, QueryPart... parts);
Apart from the general factory methods, plain SQL is also available in various other contexts. For instance, when adding a .where("a = b") clause to a query. Hence, there exist several convenience methods where plain SQL can be inserted usefully. This is an example displaying all various use-cases in one single query:
// You can use your table aliases in plain SQL fields
// As long as that will produce syntactically correct SQL
Field<?> LAST_NAME = field("a.LAST_NAME");
// You can alias your plain SQL fields
Field<?> COUNT1 = field("count(*) x");
// If you know a reasonable Java type for your field, you
// can also provide jOOQ with that type
Field<Integer> COUNT2 = field("count(*) y", Integer.class);
// Use plain SQL as select fields
create.select(LAST_NAME, COUNT1, COUNT2)
// Use plain SQL as aliased tables (be aware of syntax!)
.from("author a")
.join("book b")
// Use plain SQL for conditions both in JOIN and WHERE clauses
.on("a.id = b.author_id")
// Bind a variable in plain SQL
.where("b.title != ?", "Brida")
// Use plain SQL again as fields in GROUP BY and ORDER BY clauses
.groupBy(LAST_NAME)
.orderBy(LAST_NAME)
.fetch();
Important things to note about plain SQL!
There are some important things to keep in mind when using plain SQL:
- jOOQ doesn't know what you're doing. You're on your own again!
- You have to provide something that will be syntactically correct. If it's not, then jOOQ won't know. Only your JDBC driver or your RDBMS will detect the syntax error.
- You have to provide consistency when you use variable binding. The number of ? must match the number of variables
- Your SQL is inserted into jOOQ queries without further checks. Hence, jOOQ can't prevent SQL injection.
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!