Use this for single-schema environments, or when all objects are made available using synonyms]]>
This setting does not affect any plain SQL usage.]]>
This setting does not affect any plain SQL usage.]]>
@deprecated - 3.12.0 - [#5909] - Use {@link RenderQuotedNames} and {@link RenderNameCase} instead.]]>
:name
(such as supported by Oracle, JPA, Spring), but
vendor specific parameters may look differently. This flag can be used to determine the prefix to be
used by named parameters, such as @
for SQL Server's @name
or $
for PostgreSQL's $name
, when generating SQL.
"Named indexed" parameters can be obtained in the same way by specifingy {@code ParamType#NAMED} and not
providing a name to parameters, resulting in :1
or @1
or $1
, etc.]]>
INNER JOIN
, if it is optional in the output dialect.]]>OUTER JOIN
, if it is optional in the output dialect.]]>This feature is available in the commercial distribution only.]]>
Older databases did not support OFFSET .. FETCH pagination, so jOOQ emulates it using derived
tables and ROWNUM
(Oracle 11g and older) or ROW_NUMBER()
(e.g. DB2,
SQL Server, etc.) filtering. While these subqueries are ordered, the ordering is not
guaranteed to be stable in the outer most queries. It may be stable (and e.g. in Oracle,
it mostly is, if queries are not parallel, or joined to other queries, etc.), so the excess
ORDER BY
clause may add some additional performance overhead. This setting forces
jOOQ to not generate the additional ORDER BY
clause.
For details, see https://github.com/jOOQ/jOOQ/issues/7609.]]>
OUTPUT
clause.
SQL Server supports an OUTPUT
clause in most DML statements, whose behaviour
is almost identical to RETURNING
in Firebird, Oracle, PostgreSQL. Users who
want to prevent jOOQ from rendering this OUTPUT
clause can deactivate this flag
to revert to jOOQ calling {@code java.sql.Statement#getGeneratedKeys()} instead, which
is only supported for single row inserts.
This OUTPUT
clause does not support fetching trigger generated values. In order
to fetch trigger generated values, {@link #fetchTriggerValuesAfterSQLServerOutput} needs to
be enabled as well.
For details, see https://github.com/jOOQ/jOOQ/issues/4498.]]>
@@group_concat_max_len
session variable in MySQL style database systems.
MySQL truncates GROUP_CONCAT
results after a certain length, which may be way
too small for jOOQ's usage, especially when using the MULTISET
emulation. By
default, jOOQ sets a session variable to the highest possible value prior to executing a
query containing GROUP_CONCAT
. This flag can be used to opt out of this.
For details, see https://github.com/jOOQ/jOOQ/issues/12092.]]>
false
jOOQ will only render parenthesis pairs around queries combined with set operators when required.
This is for example the case when set operators are nested, when non-associative operators like EXCEPT are used, or when the queries are rendered as derived tables.
When this setting is set to true
the queries combined with set operators will always be surrounded by a parenthesis pair.
For details, see https://github.com/jOOQ/jOOQ/issues/3676 and https://github.com/jOOQ/jOOQ/issues/9751.]]>
SELECT
a.first_name AS "book.author.firstName"
a.last_name AS "book.author.lastName"
FROM ...
Not all dialects support "." in identifiers. This setting allows for specifying an alternative String to use as separator, e.g. "__".]]>
Historically, jOOQ encoded the java.time
types as strings to offer better compatibility with older JDBC drivers. By now, most drivers should support the java.time
types. Using them may produce better performance both on the server and on the client side.
This flag allows for reverting to pre-jOOQ 3.14 behaviour, where the default is to bind these types natively.
For details, see https://github.com/jOOQ/jOOQ/issues/9902.]]>
Historically, jOOQ encoded the java.time
types as strings to offer better compatibility with older JDBC drivers. By now, most drivers should support the java.time
types. Using them may produce better performance both on the server and on the client side.
This flag allows for reverting to pre-jOOQ 3.14 behaviour, where the default is to bind these types natively.
For details, see https://github.com/jOOQ/jOOQ/issues/9902.]]>
SQL Server OUTPUT
statements do not support fetching trigger generated values.
This is a limitation of the {@link #renderOutputForSQLServerReturningClause}. An additional
MERGE
statement can run a second query if (and only if) the primary key has been
included in the OUTPUT
clause.
For details, see https://github.com/jOOQ/jOOQ/issues/4498.]]>
Using this flag, fetching of intermediate results can be turned off even when execute listeners are present, or turned on even if they're absent.]]>
This feature is available in the commercial distribution only.]]>
RTRIM(LTRIM(x))
to TRIM(x)
.
Historically, a few dialects did not implement TRIM(x)
or TRIM(BOTH FROM x)
,
so users worked around this by wrapping LTRIM()
and RTRIM()
with each other.
Maintaining this is usually undesirable, so this transformation helps remove the unwanted wrapping.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x
.
This transformation removes a redundant logic negation.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
a = b
, and similar comparisons.
This transformation removes a redundant logical negation from the DISTINCT
predicate.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
a IS DISTINCT FROM b
.
This transformation removes a redundant logical negation from the DISTINCT
predicate.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
((a + b) + c) + d
.
This transformation turns trees into lists, which greatly simplifies other tree traversal transformations. Some of those other transformations currently rely on this flag to be active.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x = a
and x NOT IN (a)
to x != a
.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
a = 1
.
This transformation inverses {@link TableField} [op] {@link org.jooq.impl.QOM.Val} comparisons, if they're not in that order.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x IN (c1, c2)
.
This transformation simplifies verbose OR
predicates into simpler IN
predicates.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x NOT IN (c1, c2)
.
This transformation simplifies verbose AND
predicates into simpler NOT IN
predicates.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x >= a
.
This transformation merges multiple OR
connected comparisons to a single comparison using a simpler operator.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x = a
.
This transformation merges multiple AND
connected comparisons to a single comparison using a simpler operator.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x IN (b, c)
.
This transformation merges multiple OR
connected comparisons to a single comparison using a simpler operator.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x BETWEEN a AND b
.
This transformation merges multiple AND
connected range predicates to a single comparison using BETWEEN
.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x BETWEEN SYMMETRIC a AND b
.
This transformation merges multiple OR
connected BETWEEN
predicates to a single comparison using BETWEEN SYMMETRIC
.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
a
.
This transformation removes any trivial case abbreviations, such as NVL()
,
COALESCE()
, NULLIF()
, etc.
This feature is available in the commercial distribution only.]]>
TRUE
.
This transformation removes any trivial predicates.
This feature is available in the commercial distribution only.]]>
EXISTS (SELECT 1 ..)
.
Scalar subqueries that count rows and whose count is compared to 0 can be transformed into equivalent, but likely cheaper to execute EXISTS queries.
This feature is available in the commercial distribution only.]]>
EXISTS (SELECT 1 .. WHERE expr IS NOT NULL)
.
Scalar subqueries that count non-null expressions and whose count is compared to 0 can be transformed into equivalent, but likely cheaper to execute EXISTS queries.
This feature is available in the commercial distribution only.]]>
NULL
.
Scalar subqueries that are guaranteed to produce no results can be replaced by a NULL
value.
This feature is available in the commercial distribution only.]]>
x
This transformation removes a redundant arithmetic negation.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x
.
This transformation removes a redundant bitwise negation.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
bitand(x, y)
and ~(bitand(x, y)
to bitnand(x, y)
.
This transformation removes a redundant bitwise negation.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
bitor(x, y)
and ~(bitor(x, y)
to bitnor(x, y)
.
This transformation removes a redundant bitwise negation.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
bitxor(x, y)
and ~(bitxor(x, y)
to bitxnor(x, y)
.
This transformation removes a redundant bitwise negation.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
UPPER(s)
.
Idempotent functions that are covered so far, include:
LTRIM(LTRIM(s))
to LTRIM(s)
LTRIM(TRIM(s))
to TRIM(s)
RTRIM(RTRIM(s))
to RTRIM(s)
RTRIM(TRIM(s))
to TRIM(s)
TRIM(LTRIM(s))
to TRIM(s)
TRIM(RTRIM(s))
to TRIM(s)
UPPER(UPPER(s))
to UPPER(s)
LOWER(LOWER(s))
to LOWER(s)
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
x / y
, and other transformations.
This transformation simplifies arithmetic expressions.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
TAN(x)
, and other transformations.
This transformation turns expanded trignonometric function definitions into their shorter equivalents.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
LOG(base, value)
, and other transformations.
This transformation turns expanded logarithmic function definitions into their shorter equivalents.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
SINH(x)
, and other transformations.
This transformation turns expanded hyperbolic function definitions into their shorter equivalents.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
ASINH(x)
, and other transformations.
This transformation turns expanded inverse hyperbolic function definitions into their shorter equivalents.
To enable this feature, {@link #transformPatterns} must be enabled as well.
This feature is available in the commercial distribution only.]]>
(+)
(Oracle, DB2) or *=
(SQL Server) for outer join
support. For backwards compatibility with older RDBMS versions, ANSI joins in jOOQ code may be
converted to equivalent table lists in generated SQL using this flag.
This flag has a limited implementation that supports inner joins (in most cases) and outer joins (only for simple comparison predicates).
This feature is available in the commercial distribution only.]]>
(+)
(Oracle, DB2) or *=
(SQL Server) for outer join
support. For backwards compatibility with older RDBMS versions, ANSI joins in jOOQ code may be
converted to equivalent table lists in generated SQL using this flag.
This flag has a limited implementation that supports inner joins (in most cases) and outer joins (only for simple comparison predicates).
This feature is available in the commercial distribution only.]]>
This feature is available in the commercial distribution only.]]>
This feature is available in the commercial distribution only.]]>
(+)
(Oracle, DB2) or *=
(SQL Server) for outer join
support. Migrating such join syntax is tedious. The jOOQ parser can parse the old syntax and
this flag enables the transformation to ANSI join syntax.
This feature is available in the commercial distribution only.]]>
LIMIT
clauses or ROW_NUMBER()
expressions.
In Oracle 11g and less, ROWNUM
filtering was the most popular way to paginate. This pseudo
column is not supported in other RDBMS, and should be replaced in Oracle 12c by the FETCH clause or
ROW_NUMBER() OVER ()
filtering. This transformation allows for replacing such a filter by
equivalent SQL, if possible.
This feature is available in the commercial distribution only.]]>
This feature is available in the commercial distribution only.]]>
TIMESTAMP
and TIMESTAMP(6)
, if 6 is the default precision for timestamps on the configured dialect.]]>:name
(such as supported by Oracle, JPA, Spring), but
vendor specific parameters may look differently. This flag can be used to determine the prefix to be
used by named parameters, such as @
for SQL Server's @name
or $
for PostgreSQL's $name
when parsing SQL.
"Named indexed" parameters can be obtained in the same way by specifingy {@code ParamType#NAMED} and not
providing a name to parameters, resulting in :1
or @1
or $1
, etc.]]>
USING
clause, if applicable.
Teradata (and possibly others) allow for referencing tables that are not listed in the FROM
clause, such as SELECT t.* FROM t WHERE t.i = u.i
. This transformation is executed in the
parser, to produce SELECT t.* FROM t, u WHERE t.i = u.i
, instead. By default, it is active
when the input dialect supports this syntax.
This feature is available in the commercial distribution only.]]>