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 Templating Language
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The plain SQL API, as documented in the previous chapter, supports a string templating mini-language that allows for constructing complex SQL string content from smaller parts. A simple example can be seen below, e.g. when looking for support for one of PostgreSQL's various vendor-specific operator types:
ARRAY[1,4,3] && ARRAY[2,1]
condition("{0} && {1}", array1, array2);
Such a plain SQL template always consists of two things:
- The SQL string fragment
- A set of
org.jooq.QueryPartarguments, which are expected to be embedded in the SQL string
The SQL string may reference the arguments by 0-based indexing. Each argument may be referenced several times. For instance, SQLite's emulation of the REPEAT(string, count) function may look like this:
Field<Integer> count = val(3);
Field<String> string = val("abc");
field("replace(substr(quote(zeroblob(({0} + 1) / 2)), 3, {0}), '0', {1})", String.class, count, string);
// ^ ^ ^ ^^^^^ ^^^^^^
// | | | | |
// argument "count" is repeated twice: \------------------+----------|---------------------/ |
// argument "string" is used only once: \-----------------------------/
For convenience, there is also a DSL.list(QueryPart...) API that allows for wrapping a comma-separated list of query parts in a single template argument:
Field<String> a = val("a");
Field<String> b = val("b");
Field<String> c = val("c");
// These two produce the same result:
condition("my_column IN ({0}, {1}, {2})", a, b, c); // Using distinct template arguments
condition("my_column IN ({0})", list(a, b, c)); // Using a single template argument
Parsing rules
When processing these plain SQL templates, a mini parser is run that handles things like
- String literals
- Quoted names
- Comments
- JDBC escape sequences
- Indexed (
?) or named (:identifier) bind variable placeholders
The above are recognised by the templating engine and contents inside of them are ignored when replacing numbered placeholders and/or bind variables. For instance:
query(
"SELECT /* In a comment, this is not a placeholder: {0}. And this is not a bind variable: ? */ title AS `title {1} ?` " +
"-- Another comment without placeholders: {2} nor bind variables: ?" +
"FROM book " +
"WHERE title = 'In a string literal, this is not a placeholder: {3}. And this is not a bind variable: ?'"
);
The above query does not contain any numbered placeholders nor bind variables, because the tokens that would otherwise be searched for are contained inside of comments, string literals, or quoted names.
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!