Select
, Insert
,
Update
, Delete
, Merge
statements prefixed with a
WITH
clause and with CommonTableExpression
s.
Example:
DSL.with("table", "col1", "col2")
.as(
select(one(), two())
)
.select()
.from("table")
Referencing XYZ*Step
types directly from client code
It is usually not recommended to reference any XYZ*Step
types
directly from client code, or assign them to local variables. When writing
dynamic SQL, creating a statement's components dynamically, and passing them
to the DSL API statically is usually a better choice. See the manual's
section about dynamic SQL for details: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql.
Drawbacks of referencing the XYZ*Step
types directly:
- They're operating on mutable implementations (as of jOOQ 3.x)
- They're less composable and not easy to get right when dynamic SQL gets complex
- They're less readable
- They might have binary incompatible changes between minor releases
- Author:
- Lukas Eder
-
Method Summary
Modifier and TypeMethodDescription@NotNull WithStep
as(ResultQuery<?> query)
Associate a subselect with a common table expression's table and column names.@NotNull WithStep
asMaterialized(ResultQuery<?> query)
Associate a materialized subselect with a common table expression's table and column names.@NotNull WithStep
asNotMaterialized(ResultQuery<?> query)
Associate a materialized subselect with a common table expression's table and column names.
-
Method Details
-
as
@NotNull @CheckReturnValue @Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,DB2,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) @NotNull WithStep as(ResultQuery<?> query)Associate a subselect with a common table expression's table and column names. -
asMaterialized
@NotNull @CheckReturnValue @Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,DB2,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) @NotNull WithStep asMaterialized(ResultQuery<?> query)Associate a materialized subselect with a common table expression's table and column names.This adds the PostgreSQL 12
MATERIALIZED
hint to the common table expression definition, or silently ignores it, if the hint is not supported. -
asNotMaterialized
@NotNull @CheckReturnValue @Support({AURORA_POSTGRES,BIGQUERY,COCKROACHDB,DB2,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,MARIADB,MEMSQL,MYSQL_8_0,ORACLE,POSTGRES,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA}) @NotNull WithStep asNotMaterialized(ResultQuery<?> query)Associate a materialized subselect with a common table expression's table and column names.This adds the PostgreSQL 12
NOT MATERIALIZED
hint to the common table expression definition, or silently ignores it, if the hint is not supported.
-