A plain SQL QueryPart.

Plain SQL query parts can be constructed in a variety of ways from the DSL API (DSL#field(String), DSL#table(String), etc.) as well as from convenience methods such as for instance SelectWhereStep#where(String). These query parts allow for embedding simple SQL strings with associated bind variables or nested queryparts.

Template language

A simple template language is implemented by such plain SQL query parts, exposing the following features:

• Nested query parts: A template may refer to argument query parts using zero-based, numbered references wrapped in curly braces. The following expression will embed expr1 and expr2 at the appropriate locations:

  
  DSL.field("GROUP_CONCAT(DISTINCT {0} ORDER BY {1} ASC SEPARATOR '-')", expr1, expr2);
  

  Query part indexes may be referenced multiple times from within a template.
• Keywords: Curly braces are also used to delimit keywords, which expose the behaviour specified in DSL#keyword(String) (e.g. upper-casing, lower-casing, etc.) . The following expression makes use of embedded keywords in a plain SQL template:

  DSL.field("{current_timestamp}");

• JDBC escape syntax: JDBC also allows for using curly braces to embed escape expressions in SQL statements, such as date literals, for instance. JDBC escape syntax is left untouched by jOOQ's plain SQL query part renderings. The following example shows such usage:

  DSL.field("{d '2015-01-01'}");

  The following JDBC escape syntax elements are recognised:
  • {d [date literal]}
  • {t [time literal]}
  • {ts [timestamp literal]}
  • {fn [function literal]}
• JDBC bind variables: in case bind variables should be inlined (as in DSL#inline(CharSequence), ParamType#INLINED, or StatementType#STATIC_STATEMENT), plain SQL query parts will discover question marks (?) and named parameters (:identifier) at syntactically appropriate positions, e.g. outside of comments, string literals, quoted name literals, and substitute the appropriate bind value into the template.

Instances can be created using DSL#sql(String) and overloads.