The Query DSL¶

Overview¶

Not every question about the data is a question about domain objects. Reports, dashboards, exports, pick lists and statistics want plain data: a handful of columns, aggregated, sorted, paginated — and they want it without instantiating a single PDO.

That is what ResultSetWrapper#toList is for: it maps the rows of a resultset to DTOs — records, builders or plain beans (see DbUtilities#resultSetToList). Such projection queries run on the low level of the database layer. There is no security manager involved, no domain context, no modification tracking — just SQL and data.

The remaining problem is the SQL itself. Hand-written query strings are tedious to assemble, easy to get wrong, and they silently rot when a column is renamed:

StringBuilder sql = new StringBuilder("SELECT c.name, SUM(o.net), SUM(o.net_2) FROM customer c");
sql.append(" JOIN ordr o ON o.customerid = c.id WHERE o.ordered >= ?");
if (realm != null) {
  sql.append(" AND c.realm = ?");       // don't forget the parameter index...
}
sql.append(" GROUP BY c.name ORDER BY c.name");

The query DSL in org.tentackle.dbms.dsl builds the very same query from typed expressions:

List<Turnover> turnovers =
    select(NAME, sum(NET).as("turnover"))
       .from(CUSTOMER)
       .join(ORDER).on(ORDER_CUSTOMER_ID.eq(ID))
       .where(ORDERED.ge(firstOfYear))
       .groupBy(NAME)
       .orderBy(NAME.asc())
       .toList(db, Turnover.class);

It is a thin layer on top of Query: the DSL renders the SQL-code and collects the parameters, the Query executes it. Everything that is dialect-specific — the LIMIT/OFFSET clauses, the join syntax, the function keywords, the alias syntax — is delegated to the Backend.

The DSL is for data, not for domain objects. It never loads PDOs, never applies permissions and never tracks modifications. Use PdoSelectList/PdoSelectUnique to load objects, and the DSL to answer questions about rows.

Declaring Tables and Columns¶

Tables and columns are declared once, usually as constants, and then shared by all queries. They are immutable and thread-safe.

Everything such a declaration needs — the table name, its alias, the column name of an attribute and its java type — is already in the model, so don't write it a second time: the QueryDSL wurblet generates the block from there. Name the entity first, then the attributes you query:

public class TurnoverReport {

  // @wurblet dsl QueryDSL Customer id name turnover active

  //<editor-fold defaultstate="collapsed" desc="code 'dsl' generated by wurblet QueryDSL">//GEN-BEGIN:dsl

  /** the query DSL table of entity Customer. */
  private static final Table CUSTOMER = DSL.table("customer", "cu");

  /** the query DSL field for 'id', column 'id'. */
  private static final Field<Long> ID = CUSTOMER.field("id", Long.class);

  /** the query DSL field for 'name', column 'name'. */
  private static final Field<String> NAME = CUSTOMER.field("name", String.class);

  /** the query DSL field for 'turnover', column 'turnover'. */
  private static final Field<BMoney> TURNOVER = CUSTOMER.field("turnover", BMoney.class);

  /** the query DSL field for 'active', column 'active'. */
  private static final Field<Boolean> ACTIVE = CUSTOMER.field("active", Boolean.class);

  //</editor-fold>//GEN-END:dsl

The entity is the wurblet's first argument, because these declarations belong where the query is written — a report, an export, an operation — not in the entity's own classes. A join needs the declarations of several entities in the same class; --prefix keeps their constants apart. Naming no attribute at all generates the fields for every attribute mapped by the entity. The class must import DSL, Table and Field from org.tentackle.dbms.dsl.

The block is regenerated on every build, and that is the point: it keeps the query aligned with the model and checks it against the model. Rename the column of turnover and the constant follows silently — the query keeps working. Rename or drop the attribute and the wurblet fails the build, naming it. Change its type from BMoney to BigDecimal and the field becomes a Field<BigDecimal>, so every comparison still passing a BMoney stops compiling. A hand-written SQL string survives all three — until it runs against production data.

Declaring them by hand¶

Not every table is in the model: legacy tables, views, temporary and reporting tables are declared directly, and the DSL is perfectly happy with them:

import static org.tentackle.dbms.dsl.DSL.*;

private static final Table          IMPORT = table("legacy_import", "li");   // "li" is the alias
private static final Field<Long>    ID     = IMPORT.field("id", Long.class);
private static final Field<String>  NAME   = IMPORT.field("name", String.class);

The java type of a field determines its DataType, and the datatype determines how many columns the field spans, how values are applied to the statement and how the field may be compared and sorted. For a modelled table declared by hand, the generated column-name constants (CN_NAME and friends) at least keep the column names honest:

private static final Field<String> NAME = CUSTOMER.field(CustomerPersistenceImpl.CN_NAME, String.class);

An aliased copy of a table is what self-joins need — generated or not:

Table parent = CUSTOMER.as("p");
Table child  = CUSTOMER.as("k");

For columns without a registered datatype there is field(String), which yields an untyped, single-column field usable in the projection, in GROUP BY and in COUNT.

Values Are Parameters, Names Are Verified¶

Every value handed to the DSL becomes a parameter of the prepared statement, never an SQL-literal:

where(NAME.eq(userInput))         //  ... WHERE c.name=?   with userInput bound to the statement

Table-, column-, function- and alias names, on the other hand, do end up in the SQL-code verbatim. They are therefore verified syntactically as soon as they enter the DSL — an identifier consisting of letters, digits, underscores and optional schema qualification is accepted, anything else is rejected:

table("customer; DROP TABLE customer")      // throws PersistenceException

null is not a valid parameter value: the statement cannot tell the backend the SQL-type of an untyped null, which is why Query rejects it as well. Use isNull() / isNotNull() instead.

Datatypes Spanning More Than One Column¶

Some Tentackle datatypes are mapped to more than one database column — BMoney (value + scale), ZonedDateTime (instant + zone), I18NText (VARCHAR + CLOB), and application-specific types may do the same. This is the part of hand-written SQL that goes wrong most often, because the second column is easily forgotten and the number of question marks must match the number of columns.

The DSL derives all of this from the datatype:

Written	Rendered
`select(TURNOVER)`	`c.turnover, c.turnover_2`
`select(TURNOVER.as("total"))`	`c.turnover AS total, c.turnover_2 AS total_2`
`where(TURNOVER.eq(money))`	`(c.turnover=? AND c.turnover_2=?)` — one value, two question marks
`where(TURNOVER.ne(money))`	`NOT (c.turnover=? AND c.turnover_2=?)`
`where(TURNOVER.isNull())`	`(c.turnover IS NULL AND c.turnover_2 IS NULL)`
`where(TURNOVER.isNotNull())`	`NOT (c.turnover IS NULL AND c.turnover_2 IS NULL)`
`groupBy(TURNOVER)`	`GROUP BY c.turnover, c.turnover_2`
`orderBy(TURNOVER.asc())`	`ORDER BY c.turnover ASC` — the sortable columns only

Note the asymmetry of the null tests: a value spanning several columns is null only if all of its columns are null. An I18NText lives either in its VARCHAR- or in its CLOB-column, so testing each column to be not null would reject rows that do have a value.

Relational operators (lt, le, gt, ge, between, like, in) compare columns independently and are therefore rejected for multi-column datatypes — as is sorting by a datatype that declares no sortable columns at all (getSortableColumns()), such as UUID or DMoney.

Mapping Rows to DTOs¶

toList(db, SomeDto.class) hands the resultset to DbUtilities#resultSetToList, which matches the column labels to record components, builder methods or setters — case-insensitive and ignoring underscores.

public record Turnover(String name, BMoney turnover) { }

For plain fields the label is the column name, so the DTO member is usually named after the column. As soon as an expression is computed, or two joined tables carry the same column name, give it an explicit alias — and name the DTO member accordingly:

select(NAME, sum(NET).as("turnover"))       // -> record Turnover(String name, BMoney turnover)

Aliases of multi-column expressions are suffixed exactly like column names (total, total_2), so a DTO member of such a type is filled from all of its columns.

Clauses¶

Clause	Method
`SELECT`	`select(...)`, `selectDistinct(...)`, no arguments for `SELECT *`
`FROM`	`from(table...)`, `table(select, alias)` for a derived table
`JOIN`	`join(t).on(c)`, `leftJoin`, `rightJoin`, `fullJoin`, `join(JoinType, t)`
`WHERE`	`where(condition...)`
`GROUP BY`	`groupBy(expression...)`
`HAVING`	`having(condition...)`
`ORDER BY`	`orderBy(sortField...)`, `orderBy(expression...)` (ascending)
`LIMIT`/`OFFSET`	`limit(n)`, `offset(n)`

The clauses may be added in any order and more than once. Conditions are combined by a logical AND, which is what queries assembled step by step need:

Select select = select(ID, NAME).from(CUSTOMER);
if (name != null) {
  select.where(NAME.like(name + "%"));
}
if (!realms.isEmpty()) {
  select.where(REALM.in(realms));
}

A Select is a mutable builder and thus not thread-safe — the expressions and conditions it is built from are.

Conditions¶

eq, ne, lt, le, gt, ge, like, notLike, isNull, isNotNull, between, notBetween, in, notIn — each of them either against a value or against another expression. They are combined with and(...), or(...), not() and the static DSL.and(...) / DSL.or(...) / DSL.not(...).

An empty value set is not an error: in(List.of()) renders a condition that is never true and notIn(List.of()) one that is always true, which is what a dynamically built set of values means.

Functions and aggregates¶

count(), count(x), countDistinct(x), sum(x), avg(x), min(x), max(x), upper(x), lower(x), abs(x) and coalesce(x, ...) — the latter rendered as COALESCE or NVL, whichever the backend uses. Anything else is one call away:

Expression<Integer> length = function("length", Integer.class, NAME);

Sub-selects¶

where(exists(select(ORDER_CUSTOMER_ID).from(ORDER).where(ORDER_CUSTOMER_ID.eq(ID))))
where(ID.in(select(ORDER_CUSTOMER_ID).from(ORDER)))
Expression<Long> orders = subSelect(select(count()).from(ORDER).where(ORDER_CUSTOMER_ID.eq(ID)), Long.class);
Table recent = table(select(NAME, ORDERED).from(ORDER).where(ORDERED.ge(firstOfYear)), "r");

Since the LIMIT- and OFFSET-clauses are backend-specific and applied to the executed statement, a select used as a sub-select must not define them.

Executing¶

Method	Result
`toList(db, dtoClass)`	all rows as DTOs
`toOptional(db, dtoClass)`	at most one row, throws if there are more
`toScalarList(db, type)`	the first expression of each row
`toScalar(db, type)`	the first expression of at most one row
`getRowCount(db)`	the number of rows, counted by the backend (`SELECT COUNT(*)`)
`execute(db)`	the raw `ResultSetWrapper`, to be closed by the caller
`toQuery(db)`	the `Query`, for everything else
`toSql(backend)`	the SQL-code, for logging and tests

fetchSize(int), maxRows(int) and statementCached(boolean) are passed through to the Query. Statement caching pays off for a query that is executed many times with a fixed SQL-string and changing parameters.

Pagination combines limit/offset with getRowCount, which counts without retrieving:

Select select = select(ID, NAME).from(CUSTOMER).where(REALM.eq(realm)).orderBy(NAME.asc());
int pages = (select.getRowCount(db) + PAGE_SIZE - 1) / PAGE_SIZE;
List<CustomerDto> page = select.limit(PAGE_SIZE).offset(pageIndex * PAGE_SIZE).toList(db, CustomerDto.class);

Escape Hatches¶

The DSL does not try to cover all of SQL. Whatever it lacks can be spliced in as plain SQL-code, with parameters, either as an expression or as a condition:

select(NAME, sql("c.id * 2", Long.class).as("doubled"))
   .from(CUSTOMER)
   .where(condition("c.name ~ ?", regex))

Backend-specific code is supplied by a SqlSupplier, the same abstraction Query uses:

where(condition(backend -> backend.isPosixEscapeSyntaxSupported() ? "c.name ~ ?" : "c.name LIKE ?", pattern))

For a construct that is used over and over, implement SqlElement (or Expression/Condition) and render into the RenderContext — the only rule is that parameters must be registered in the order of the question marks emitted.

Summary¶

	Hand-written `Query`	Query DSL
SQL-code	Written by the application	Generated from typed expressions
Multi-column datatypes	Columns and question marks counted by hand	Derived from the `DataType`
Dialect	`SqlSupplier` per difference	Delegated to the `Backend`
Column renames	Found at runtime	Found where the field constant is declared
Coverage	Everything	Projection queries (`SELECT`)