Package org.eclipse.net4j.db.dml

Class SQL

java.lang.Object
org.eclipse.net4j.db.dml.SQL
public final class SQL extends Object
Canonical factories for SQL expressions, definitions, references, and fluent statements. Builders preserve their operations as immutable templates; compiling a template renders adapter-specific SQL text and ordered parameter occurrences for preparation and JDBC parameter binding.

A typical SELECT chain keeps schema fields as expressions and parameters explicit: 

 SQL.select(SQL.all(table.as("t")), SQL.count(id).as("total")) //
     .from(table.as("t")) //
     .where(id).eq(SQL.param()) //
     .groupBy(name) //
     .orderByDesc(SQL.count(id)) //
     .build();

literal(Object) values are checked by the selected adapter during compilation and may not be portable across dialects; application values should generally be supplied through param(). SQL comparisons with a literal or bound NULL retain ordinary SQL comparison semantics and are not rewritten to IS NULL.

Since:
4.15
Author:
Eike Stepper

  • Field Details

    • NULL

      public static final SQLExpression NULL
      The SQL NULL literal.
      Since:
      4.15

    • ZERO

      public static final SQLExpression ZERO
      The numeric SQL literal 0.
      Since:
      4.15

    • ONE

      public static final SQLExpression ONE
      The numeric SQL literal 1.
      Since:
      4.15

  • Method Details

    • select

      public static SQLSelectProjectionStep select(SQLSelectItem first, SQLSelectItem... others)
      Begins a SELECT definition whose projection contains the required first item and any additional items in the supplied order.
      Parameters:
      first - the required first item; it also determines the item category for this call
      others - additional items, preserved in their supplied order
      Returns:
      the corresponding staged builder
      Since:
      4.15

    • selectDistinct

      public static SQLSelectProjectionStep selectDistinct(SQLSelectItem first, SQLSelectItem... others)
      Begins a SELECT DISTINCT definition; all supplied projection items participate in duplicate elimination.
      Parameters:
      first - the required first item; it also determines the item category for this call
      others - additional items, preserved in their supplied order
      Returns:
      the corresponding staged builder
      Since:
      4.15

    • selectAll

      public static SQLSelectProjectionStep selectAll()
      Begins a SELECT with an unqualified wildcard projection.
      Returns:
      the corresponding staged builder
      Since:
      4.15

    • update

      public static SQLUpdateStartStep update(IDBTable table)
      Begins an UPDATE definition for the supplied schema table. A complete assignment is required before the definition can be built. Without a WHERE clause, the UPDATE may modify every row in the table.
      Parameters:
      table - the existing schema table
      Returns:
      the corresponding staged builder
      Since:
      4.15

    • update

      public static SQLUpdateStartStep update(IDBTable.Provider provider)
      Begins an UPDATE definition for the table supplied by the provider. The provider is resolved immediately, and the resulting statement retains the table rather than the provider.
      Parameters:
      provider - the provider of the existing schema table
      Returns:
      the corresponding staged builder
      Throws:
      NullPointerException - if the provider or its table is null
      Since:
      4.15

    • insertInto

      public static SQLInsertStartStep insertInto(IDBTable table)
      Begins an INSERT definition. Choose a values list, explicit columns and values, or dynamic column assignments.
      Parameters:
      table - the existing schema table
      Returns:
      the corresponding staged builder
      Since:
      4.15

    • insertInto

      public static SQLInsertStartStep insertInto(IDBTable.Provider provider)
      Begins an INSERT definition for the table supplied by the provider, resolving it immediately.
      Parameters:
      provider - the provider of the existing schema table
      Returns:
      the corresponding staged builder
      Throws:
      NullPointerException - if the provider or its table is null
      Since:
      4.15

    • deleteFrom

      public static SQLDeleteStep deleteFrom(IDBTable table)
      Begins a DELETE definition. It may be built directly or completed with a WHERE condition. Without a WHERE clause, the DELETE may remove every row in the table.
      Parameters:
      table - the existing schema table
      Returns:
      the corresponding staged builder
      Since:
      4.15

    • deleteFrom

      public static SQLDeleteStep deleteFrom(IDBTable.Provider provider)
      Begins a DELETE definition for the table supplied by the provider, resolving it immediately.
      Parameters:
      provider - the provider of the existing schema table
      Returns:
      the corresponding staged builder
      Throws:
      NullPointerException - if the provider or its table is null
      Since:
      4.15

    • table

      public static SQLTableRef table(IDBTable table)
      Creates an unaliased occurrence of a schema table for use in a statement.
      Parameters:
      table - the existing schema table
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • table

      public static SQLTableRef table(IDBTable.Provider provider)
      Creates an unaliased occurrence of the table supplied by the provider, resolving it immediately.
      Parameters:
      provider - the provider of the existing schema table
      Returns:
      the corresponding immutable SQL API value
      Throws:
      NullPointerException - if the provider or its table is null
      Since:
      4.15

    • as

      public static SQLTableRef as(IDBTable table, String alias)
      Creates a table occurrence with a SQL alias. This is a table alias, distinct from a result-column alias.
      Parameters:
      table - the existing schema table
      alias - the SQL alias for this table occurrence
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • as

      public static SQLTableRef as(IDBTable.Provider provider, String alias)
      Creates an aliased occurrence of the table supplied by the provider, resolving it immediately.
      Parameters:
      provider - the provider of the existing schema table
      alias - the SQL alias for this table occurrence
      Returns:
      the corresponding immutable SQL API value
      Throws:
      NullPointerException - if the provider, its table, or alias is null
      Since:
      4.15

    • as

      public static SQLSelectItem as(SQLExpression expression, String alias)
      Associates a result-column label with an expression in a projection.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      alias - the result-column label
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • column

      public static SQLColumnRef column(IDBField column)
      Creates an unqualified reference to a schema column. Use a table reference’s column method when qualification is needed.
      Parameters:
      column - the existing schema column
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • qualified

      public static SQLColumnRef qualified(IDBField column)
      Creates a table-qualified column reference using the column's owning schema table. The table is referenced by its physical name, without an alias. For aliased tables, use SQLTableRef.column(IDBField) instead.
      Parameters:
      column - the column to qualify with its owning table
      Returns:
      the qualified column reference

    • all

      public static SQLSelectItem all()
      Creates a wildcard projection; the overload with a table reference qualifies the wildcard to that table occurrence.
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • all

      public static SQLSelectItem all(SQLTableRef table)
      Creates a wildcard projection; the overload with a table reference qualifies the wildcard to that table occurrence.
      Parameters:
      table - the existing schema table
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • asc

      public static SQLOrderItem asc(SQLExpression expression)
      Wraps an expression as an ascending ORDER BY item.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • desc

      public static SQLOrderItem desc(SQLExpression expression)
      Wraps an expression as a descending ORDER BY item.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • param

      public static SQLParameter param()
      Creates a new immutable logical parameter identity. Each appearance of the parameter in a compiled statement becomes a JDBC placeholder occurrence; reusing one instance preserves its identity across those occurrences.
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • param

      public static SQLParameter param(int sqlType)
      Creates a new immutable logical parameter identity with an explicit JDBC type. Each factory call creates a distinct identity; reusing one instance in a definition preserves that identity across occurrences.
      Parameters:
      sqlType - the Types code used by typed setObject calls and for NULL binding
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • param

      public static <JAVA, JDBC> SQLParameter param(Function<JAVA,JDBC> converter)
      Creates a new immutable logical parameter identity with a converter. Each factory call creates a distinct identity; reusing one instance in a definition preserves that identity across occurrences. The converter runs once for every occurrence bound with a non-null input, and its result is passed to default JDBC binding. Converter failures propagate to the caller.
      Type Parameters:
      JAVA - the input value type accepted by the converter
      JDBC - the converted value type passed to JDBC
      Parameters:
      converter - converts each non-null application value before default JDBC binding; it is bypassed for null input and by an explicit setter override
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • param

      public static <JAVA, JDBC> SQLParameter param(int sqlType, Function<JAVA,JDBC> converter)
      Creates a new immutable logical parameter identity with an explicit JDBC type and converter. Each factory call creates a distinct identity; reusing one instance in a definition preserves that identity across occurrences. The converter runs once for every occurrence bound with a non-null input, and its result is passed to default JDBC binding. Converter failures propagate to the caller.
      Type Parameters:
      JAVA - the input value type accepted by the converter
      JDBC - the converted value type passed to JDBC
      Parameters:
      sqlType - the Types code used by typed setObject calls and for NULL binding
      converter - converts each non-null application value before default JDBC binding; it is bypassed for null input and by an explicit setter override
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • param

      public static <JAVA> SQLParameter param(SQLValueSetter<JAVA> setter)
      Creates a new logical parameter whose setter performs complete JDBC binding for every occurrence. The setter receives the original application value, including null, and is responsible for JDBC NULL handling. The parameter retains the setter, and compiled statements retain the parameter; captured objects may therefore remain reachable for their lifetime. Choose reuse, mutable state, thread safety, and connection-specific or execution-specific dependencies to suit your use case.
      Type Parameters:
      JAVA - the application value type
      Parameters:
      setter - the complete JDBC binding strategy
      Returns:
      a new logical parameter identity
      Throws:
      NullPointerException - if setter is null
      Since:
      4.15

    • param

      public static <JAVA> SQLParameter param(int sqlType, SQLValueSetter<JAVA> setter)
      Creates a new logical parameter with JDBC type metadata and a complete JDBC value setter. The type remains parameter metadata, but the setter owns the entire JDBC operation. Captured objects may remain reachable through the parameter and compiled statement; callers choose suitable reuse, mutable state, thread safety, and connection-specific dependencies.
      Type Parameters:
      JAVA - the application value type
      Parameters:
      sqlType - the JDBC type metadata
      setter - the complete JDBC binding strategy
      Returns:
      a new logical parameter identity
      Throws:
      NullPointerException - if setter is null
      Since:
      4.15

    • literal

      public static SQLExpression literal(Object value)
      Creates an expression from an explicit literal value. Compilation supports null, strings, characters, booleans, finite numeric values, and SQL date/time values, subject to validation by the compiling adapter. Other Java types are rejected rather than rendered through arbitrary toString() conversion. Mutable SQL date, time, and timestamp values are snapshotted when this expression is created. Literals such as Boolean or SQL NULL may have dialect-specific semantics; use parameters for portable application values. Comparisons to NULL are not rewritten to IS NULL; use isNull(SQLExpression) or isNotNull(SQLExpression) explicitly.
      Parameters:
      value - the literal value represented by this expression
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • scalar

      public static SQLExpression scalar(SQLSubSelect query)
      Uses a complete SELECT definition as a scalar expression. It must project exactly one column; this is validated during compilation. Whether the query returns zero, one, or multiple rows is determined by SQL and the database at execution time.
      Parameters:
      query - the complete SELECT definition used by this expression
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • exists

      public static SQLCondition exists(SQLSubSelect query)
      Creates an EXISTS condition over a complete SELECT definition. Unlike scalar and IN subqueries, EXISTS does not require a single-column projection; width validation occurs during compilation.
      Parameters:
      query - the complete SELECT definition used by this expression
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • notExists

      public static SQLCondition notExists(SQLSubSelect query)
      Creates a NOT EXISTS condition over a complete SELECT definition. Unlike scalar and IN subqueries, NOT EXISTS does not require a single-column projection; width validation occurs during compilation.
      Parameters:
      query - the complete SELECT definition used by this expression
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • count

      public static SQLExpression count()
      Creates the COUNT(*) aggregate.
      Returns:
      a COUNT(*) expression
      Since:
      4.15

    • count

      public static SQLExpression count(SQLExpression expression)
      Creates COUNT over the non-NULL values produced by the supplied expression.
      Parameters:
      expression - the required expression whose non-NULL values are counted
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • countDistinct

      public static SQLExpression countDistinct(SQLExpression expression)
      Creates COUNT(DISTINCT expression).
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • min

      public static SQLExpression min(SQLExpression expression)
      Creates a MIN aggregate over the supplied expression.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • max

      public static SQLExpression max(SQLExpression expression)
      Creates a MAX aggregate over the supplied expression.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • sum

      public static SQLExpression sum(SQLExpression expression)
      Creates a SUM aggregate over the supplied expression.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • avg

      public static SQLExpression avg(SQLExpression expression)
      Creates an AVG aggregate over the supplied expression.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • abs

      public static SQLExpression abs(SQLExpression expression)
      Creates a structured absolute-value expression. The database determines numeric conversion, range, overflow, and result semantics; a referenced column remains available for parameter type inference.
      Parameters:
      expression - the expression whose absolute value is computed
      Returns:
      the corresponding immutable SQL expression
      Throws:
      NullPointerException - if the expression is null
      Since:
      4.15

    • add

      public static SQLExpression add(SQLExpression left, SQLExpression right)
      Creates a binary addition expression.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • subtract

      public static SQLExpression subtract(SQLExpression left, SQLExpression right)
      Creates a binary subtraction expression.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • multiply

      public static SQLExpression multiply(SQLExpression left, SQLExpression right)
      Creates a binary multiplication expression.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • divide

      public static SQLExpression divide(SQLExpression left, SQLExpression right)
      Creates a binary division expression.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • eq

      public static SQLCondition eq(SQLExpression left, SQLExpression right)
      Creates an equality condition using ordinary SQL comparison semantics. If either operand is SQL NULL, the result is not rewritten to IS NULL; use isNull(SQLExpression) for a NULL test.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • ne

      public static SQLCondition ne(SQLExpression left, SQLExpression right)
      Creates an inequality condition.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • lt

      public static SQLCondition lt(SQLExpression left, SQLExpression right)
      Creates a less-than condition.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • le

      public static SQLCondition le(SQLExpression left, SQLExpression right)
      Creates a less-than-or-equal condition.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • gt

      public static SQLCondition gt(SQLExpression left, SQLExpression right)
      Creates a greater-than condition.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • ge

      public static SQLCondition ge(SQLExpression left, SQLExpression right)
      Creates a greater-than-or-equal condition.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • like

      public static SQLCondition like(SQLExpression left, SQLExpression right)
      Creates a LIKE condition. Pattern escaping remains the responsibility of the supplied expression and database dialect.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • between

      public static SQLCondition between(SQLExpression expression, SQLExpression lower, SQLExpression upper)
      Creates a BETWEEN condition with inclusive lower and upper bounds according to SQL semantics.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      lower - the inclusive lower bound expression
      upper - the inclusive upper bound expression
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • isNull

      public static SQLCondition isNull(SQLExpression expression)
      Creates an IS NULL condition.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • isNotNull

      public static SQLCondition isNotNull(SQLExpression expression)
      Creates an IS NOT NULL condition.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • and

      public static SQLCondition and(SQLCondition left, SQLCondition right)
      Creates a condition that combines both supplied conditions with AND.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • or

      public static SQLCondition or(SQLCondition left, SQLCondition right)
      Creates a condition that combines both supplied conditions with OR.
      Parameters:
      left - the left operand
      right - the right operand
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • not

      public static SQLCondition not(SQLCondition condition)
      Creates a negated condition.
      Parameters:
      condition - the complete condition to compose or wrap
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • in

      public static SQLCondition in(SQLExpression expression, SQLSubSelect subSelect)
      Creates an IN condition whose values come from a complete SELECT definition. The subquery must project exactly one column; this is validated during compilation.
      Parameters:
      expression - the SQL expression to wrap or aggregate
      subSelect - the complete SELECT definition used by this expression
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • in

      public static SQLCondition in(SQLExpression expression, SQLExpression... values)
      Creates an IN condition over a snapshotted, non-empty ordered list of expressions. Parameter identities and SQL NULL semantics are preserved.
      Parameters:
      expression - the expression tested for membership
      values - the ordered values to compare against
      Returns:
      the corresponding immutable condition
      Throws:
      NullPointerException - if the expression, array, or any value is null
      IllegalArgumentException - if the list is empty
      Since:
      4.15

    • in

      public static SQLCondition in(SQLExpression expression, Iterable<? extends SQLExpression> values)
      Creates an IN condition from a single traversal of the supplied iterable. The expressions are snapshotted in iteration order, retaining parameter identity and ordinary SQL NULL semantics.
      Parameters:
      expression - the expression tested for membership
      values - the ordered values to compare against
      Returns:
      the corresponding immutable condition
      Throws:
      NullPointerException - if the expression, iterable, or any value is null
      IllegalArgumentException - if the iterable is empty
      Since:
      4.15

    • fragment

      public static SQLFragmentStep fragment()
      Begins a structured fragment. Raw text is trusted SQL, is not parsed for operator precedence, and must not contain ?, even in a quoted string. Add identifiers and parameters through their typed methods. Prefer structured expressions for operators; explicitly group fragment expressions when their internal precedence matters.
      Returns:
      the corresponding staged builder
      Since:
      4.15

    • expression

      public static SQLExpression expression(SQLFragment fragment)
      Wraps a structured fragment as an expression using the canonical factory. Structured operator expressions allow compiler-managed precedence; raw fragment text is not parsed, so explicitly group it where required.
      Parameters:
      fragment - the structured SQL fragment
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • condition

      public static SQLCondition condition(SQLFragment fragment)
      Wraps a structured fragment as a condition using the canonical factory.
      Parameters:
      fragment - the structured SQL fragment
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15

    • build

      public static SQLTemplate build(SQLFragment fragment)
      Treats the fragment as a complete template definition. This differs from the fragment builder’s build method, which returns the fragment itself.
      Parameters:
      fragment - the structured SQL fragment
      Returns:
      the corresponding immutable SQL API value
      Since:
      4.15