Class Select

java.lang.Object
org.tentackle.dbms.dsl.Select
All Implemented Interfaces:
SqlElement
public class Select extends Object implements SqlElement
A SELECT-statement.

Created by DSL.select(Expression...) and refined by the clause methods, each returning this select: 

  List<Turnover> turnovers =
      DSL.select(ORDER.CUSTOMER, DSL.sum(ORDER.NET).as("turnover"))
         .from(ORDER)
         .where(ORDER.ORDERED.ge(firstOfYear))
         .groupBy(ORDER.CUSTOMER)
         .orderBy(ORDER.CUSTOMER.asc())
         .toList(db, Turnover.class);
The clauses may be added in any order and more than once. Conditions passed to where(Condition...) and having(Condition...) are combined by a logical AND, which is what queries assembled step by step usually need: 
  Select select = DSL.select().from(CUSTOMER);
  if (name != null) {
    select.where(CUSTOMER.NAME.like(name + "%"));
  }
A select is a mutable builder and thus not thread-safe. The expressions and conditions it is built from, however, are immutable and may be shared.

Selects deliver plain data, not PDOs, and there is no security manager involved. Use them for projections, aggregates and reports, not to load domain objects.

Author:
harald

  • Constructor Details

    • Select

      public Select(boolean distinct, Collection<? extends Expression<?>> projection)
      Creates a select.
      Parameters:
      distinct - true for SELECT DISTINCT
      projection - the selected expressions, empty to select all columns

  • Method Details

    • from

      public Select from(Table... tables)
      Adds tables to the FROM-clause.
      Parameters:
      tables - the tables
      Returns:
      this select

    • join

      public JoinStep join(Table table)
      Adds an inner join.
      Parameters:
      table - the joined table
      Returns:
      the step expecting the join condition

    • leftJoin

      public JoinStep leftJoin(Table table)
      Adds a left outer join.
      Parameters:
      table - the joined table
      Returns:
      the step expecting the join condition

    • rightJoin

      public JoinStep rightJoin(Table table)
      Adds a right outer join.
      Parameters:
      table - the joined table
      Returns:
      the step expecting the join condition

    • fullJoin

      public JoinStep fullJoin(Table table)
      Adds a full outer join.
      Parameters:
      table - the joined table
      Returns:
      the step expecting the join condition

    • join

      public JoinStep join(JoinType joinType, Table table)
      Adds a join.
      Parameters:
      joinType - the kind of join
      table - the joined table
      Returns:
      the step expecting the join condition

    • where

      public Select where(Condition... conditions)
      Adds conditions to the WHERE-clause.
      All conditions of a select are combined by a logical AND.
      Parameters:
      conditions - the conditions
      Returns:
      this select

    • groupBy

      public Select groupBy(Expression<?>... expressions)
      Adds expressions to the GROUP BY-clause.
      Parameters:
      expressions - the expressions
      Returns:
      this select

    • having

      public Select having(Condition... conditions)
      Adds conditions to the HAVING-clause.
      All conditions of a select are combined by a logical AND.
      Parameters:
      conditions - the conditions
      Returns:
      this select

    • orderBy

      public Select orderBy(SortField... sortFields)
      Adds sort fields to the ORDER BY-clause.
      Parameters:
      sortFields - the sort fields
      Returns:
      this select

    • orderBy

      public Select orderBy(Expression<?>... expressions)
      Adds ascending expressions to the ORDER BY-clause.
      Parameters:
      expressions - the expressions
      Returns:
      this select

    • limit

      public Select limit(int limit)
      Sets the maximum number of rows to retrieve.
      The LIMIT-clause is backend-specific and thus added by the Backend. It applies to the executed statement only, i.e. a select used as a sub-select must not define a limit.
      Parameters:
      limit - the maximum number of rows, 0 if unlimited (default)
      Returns:
      this select
      See Also:

    • offset

      public Select offset(int offset)
      Sets the number of rows to skip.
      The OFFSET-clause is backend-specific and thus added by the Backend. It applies to the executed statement only, i.e. a select used as a sub-select must not define an offset.
      Parameters:
      offset - the number of rows to skip, 0 if none (default)
      Returns:
      this select
      See Also:

    • fetchSize

      public Select fetchSize(int fetchSize)
      Sets the fetch size.
      Parameters:
      fetchSize - the fetch size, 0 for the driver's default
      Returns:
      this select
      See Also:

    • maxRows

      public Select maxRows(int maxRows)
      Sets the maximum number of rows fetched in total.
      Parameters:
      maxRows - the maximum number of rows, 0 if unlimited
      Returns:
      this select
      See Also:

    • statementCached

      public Select statementCached(boolean statementCached)
      Enables caching of the prepared statement.
      Parameters:
      statementCached - true to prepare only once and reuse the cached statement, false if one-shot (default)
      Returns:
      this select
      See Also:

    • render

      public void render(RenderContext context)
      Renders this select as a sub-select.

      Renders this element into the given context.

      Specified by:
      render in interface SqlElement
      Parameters:
      context - the render context

    • toSql

      public String toSql(Backend backend)
      Creates the SQL-code of this select.
      Notice that the LIMIT- and OFFSET-clauses are added by the backend when the query is executed and thus are not part of the returned code.
      Parameters:
      backend - the backend
      Returns:
      the SQL-code

    • toQuery

      public Query toQuery(Backend backend)
      Creates the query for this select.
      Parameters:
      backend - the backend
      Returns:
      the query

    • toQuery

      public Query toQuery(Db db)
      Creates the query for this select.
      Parameters:
      db - the session
      Returns:
      the query

    • execute

      public ResultSetWrapper execute(Db db)
      Executes this select.
      The application is responsible for closing the returned resultset.
      Parameters:
      db - the session
      Returns:
      the resultset

    • toList

      public <T> List<T> toList(Db db, Class<T> dtoClass)
      Executes this select and maps the rows to DTOs.
      Type Parameters:
      T - the DTO type
      Parameters:
      db - the session
      dtoClass - the DTO class
      Returns:
      the list of DTOs
      See Also:

    • toOptional

      public <T> Optional<T> toOptional(Db db, Class<T> dtoClass)
      Executes this select and maps at most one row to a DTO.
      Type Parameters:
      T - the DTO type
      Parameters:
      db - the session
      dtoClass - the DTO class
      Returns:
      the optional DTO, empty if no row at all
      Throws:
      PersistenceException - if more than one row was returned

    • toScalarList

      public <T> List<T> toScalarList(Db db, Class<T> type)
      Executes this select and retrieves the value of the first expression of the projection for each row.
      Type Parameters:
      T - the value type
      Parameters:
      db - the session
      type - the java type of the value
      Returns:
      the list of values

    • toScalar

      public <T> Optional<T> toScalar(Db db, Class<T> type)
      Executes this select and retrieves the value of the first expression of the projection of at most one row.
      Type Parameters:
      T - the value type
      Parameters:
      db - the session
      type - the java type of the value
      Returns:
      the optional value, empty if no row at all or if the value is null
      Throws:
      PersistenceException - if more than one row was returned

    • getRowCount

      public int getRowCount(Db db)
      Gets the number of rows returned by this select.
      The rows are counted by the backend, i.e. they are not retrieved.
      Parameters:
      db - the session
      Returns:
      the number of rows
      See Also:

    • toString

      public String toString()
      Overrides:
      toString in class Object