What’s a “String” in the jOOQ API?

One of jOOQ’s biggest strength is the fact that it is a type safe SQL API. “Type safe”, in this context, means that every object that you put in a jOOQ query has a well defined type, such as:

These can be used in jOOQ in a type safe way as such:

ctx.select(T.A)      // A Field
   .from(T)          // A Table
   .where(T.B.eq(1)) // A Condition
   .fetch();

No Strings here, no string concatenation.

However, there are a few cases where you want to bypass type safety, for whatever reason, including to extend jOOQ e.g. with plain SQL templating. In those cases, you would pass a “String” object to the jOOQ API. But not every such String object is the same. What different types of strings are there? There are 4 main types of String in the jOOQ API:

1. The bind value

The most obvious type of string is the bind value or literal. You can create those explicitly using:

// As always, this static import is implied
import static org.jooq.impl.DSL.*;

Field<String> bind = val("abc");
Field<String> literal = inline("xyz");

By default, the first value will produce a bind parameter marker "?" in the generated SQL, whereas the second one will produce an escaped string literal 'xyz'. You’re probably already doing this implicitly. Whenever you pass a String value where the jOOQ API expects a T type, you’re implicitly wrapping your String value using DSL.val(T):

ctx.select(T.A)
   .from(T)
   .where(T.C.eq("xyz")) // Implicit bind value
   .fetch();

This is still type safe usage of a String value, as it is really wrapped as a Field<String>

2. The plain SQL template

Whenever jOOQ is missing some vendor specific functionality, the backdoor is to use plain SQL templating. You can create plain SQL templates explicitly for the most popular types as shown above, like this:

Field<Integer> field = field("(1 + 2)", SQLDataType.INTEGER);
Table<?> table       = table("generate_series(1, 10)");
Condition condition  = condition("some_function() = 1");

These expressions can now be embedded in your queries, like any other:

ctx.select(field)
   .from(table)
   .where(condition)
   .fetch();

Alternatively, there also exists convenience overloads on some query methods to make this simpler:

ctx.select(field("(1 + 2)", SQLDataType.INTEGER)) // Not on SELECT
   .from("generate_series(1, 10)")
   .where("some_function() = 1")
   .fetch();

Notice that the select() method doesn’t have such convenience API yet, as of jOOQ 3.13

Important disclaimer: Using these APIs, you’re going to expose yourself to the usual SQL injection risk that is present also with JDBC or JPQL usage, when composing SQL from strings. NEVER concatenate plain SQL templates, nor use user input in these strings. Use the templating language instead, and turn every user input into bind variables. Examples:

ctx.select(...)
   .from(...)
   .where("some_function() = ?", 1) // Bind variable
   .fetch();

ctx.select(...)
   .from(...)
   .where("some_function() = {0}", val(1)) // Templating
   .fetch();

If you encounter String types on most query API in jOOQ, it’s for plain SQL templating. All of this API is annotated with @org.jooq.PlainSQL for additional documentation purposes, and to be preprocessed with a static checker that can be used to disallow such API usage by default, for added security.

3. Names (identifiers)

But on some query API in jOOQ, Strings are not convenience for plain SQL templating, but for names and identifiers. Namely, all DDL statements use strings this way in their API.

You can create qualified or unqualified identifiers explicitly as follows:

// Unqualified table identifier
Name table = name("t");

// Qualified column identifier
Name field = name("t", "col");

And then, use those identifiers in your DDL statements, e.g. to create the table:

ctx.createTable(table)
   .column(field, SQLDataType.INTEGER)
   .execute();

Depending on the context, qualification is necessary or not. In this case, field qualification would not have been necessary.

For convenience, you can also just use String types on the createTable(String) API:

ctx.createTable("t")
   .column("col", SQLDataType.INTEGER)
   .execute();

These strings will simply be wrapped in DSL.name(String) as illustrated before.

Beware: In jOOQ, all identifiers are quoted by default (RenderQuotedNames.EXPLICIT_DEFAULT_QUOTED). This has two benefits:

  • Special characters, and more importantly, keyword conflicts are handled correctly out of the box
  • Quoting prevents SQL injection
  • Case sensitivity is handled correctly in those dialects that support it for quoted identifiers

The price to pay for this convenience is that quoted identifiers might turn case sensitive, when this is undesired. To work around this, you can turn off quoting using your settings, e.g. by setting RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED. But beware, this will put you again at SQL injection risk if you don’t sanitise your identifier names first!

4. Keywords

Keywords are strings too, in jOOQ. In rare cases, you may want to wrap your keyword-as-string representation in a org.jooq.Keyword type. The main benefit (as of jOOQ 3.13) is a consistent keyword style. There is no convenience API for this, as it is very rare for client code to use this feature. There’s only DSL.keyword(String):

Keyword current = keyword("current");
Keyword current = keyword("time");

You can now use keywords in plain SQL templates:

Field<Time> currentTime = field(
  "{0} {1}", 
  SQLDataType.TIME, 
  current, time
);

Never Concatenate Strings With jOOQ

jOOQ supports a vast amount of SQL syntax out of the box. As such, most users will not think of resorting to string concatenation like in the old days when writing dynamic SQL with JDBC.

But every now and then, a vendor specific feature is not supported by jOOQ (yes, it happens). In that case, jOOQ supports a variety of “plain SQL” API, which can be used to construct almost all types of jOOQ API elements, such as:

// Static import is implied, as always
import static org.jooq.impl.DSL.*;

// Column expressions
Field<String> f = field("cool_function(1, 2, 3)", String.class);

// Predicates
Condition c = condition("col1 <fancy operator> col2");

// Tables
Table<?> t = table("wicked_table_valued_function(x, y)");

But then, sometimes, you need to pass an argument to such a function dynamically, such as another column expression. And you want to do that in a type safe way, because the jOOQ code generator already produced type safe column expressions. So you might be inclined to concatenate, nonetheless:

field("cool_function(1, " + MY_TABLE.MY_COLUMN + ", 3)");

Never do this!

For these reasons:

  1. Despite jOOQ being very SQL injection safe in general, this is where you can in fact introduce a plain SQL injection vulnerability nonetheless. Not in this case, as the column is generated code, but maybe, you will concatenate user input. Note that in order to increase SQL injection protection, plain SQL usage can be prevented globally, and allowed only locally when needed by adding our PlainSQL checker, using the checker framework or Google ErrorProne.
  2. As always with string concatenation, you’re prone to SQL syntax errors. In this case, the generated SQL is not specific to any dialect, as MY_TABLE.MY_COLUMN.toString() is being called, without any contextual information, such as the SQLDialect and all the other configuration flags.

Instead, use jOOQ’s plain SQL templating mini language, which allows for template placeholders like {0}, {1}, {2}:

field("cool_function(1, {0}, 3)", MY_TABLE.MY_COLUMN);

And if you’re doing this more often, you can factor out this call in your own mini DSL:

public static Field<String> coolFunction(Field<?> field) {
    field("cool_function(1, {0}, 3)", field);
}

And now, call it like this:

coolFunction(MY_TABLE.MY_COLUMN)

As a rule of thumb:

With jOOQ, you should never need to resort to SQL string concatenation

You can always use either:

  • The type safe jOOQ DSL API
  • The plain SQL templating API (and ideally hide such usage behind your own type safe DSL API)