Class Query

java.lang.Object
org.tentackle.dbms.Query
public class Query extends Object
A database query.

Combines the generation of an SQL-string and parameter set for the ResultSetWrapper. Useful for dynamic one-time queries that, for whatever reason, cannot be generated by wurblets. Notice that by default the underlying prepared statement is closed when the result-set (or cursor) is closed by the application. You can change that behavior, however.

Notice further that the application must provide the columns returned by the SELECT via add() before execution. The SELECT, however, and LIMIT/OFFSET clauses are provided by the query automatically, because they are backend-specific.

Instead of writing the SQL code by hand, projection queries can be built from typed expressions by the DSL, which creates a Query for the resulting Select.

Author:
harald

  • Constructor Details

    • Query

      public Query()
      Creates a query.

  • Method Details

    • setStatementCached

      public void setStatementCached(boolean statementCached)
      Enables caching of the prepared statement.
      By default, queries are one-time prepared statements that will be closed when the resultset is closed. However, if a query uses a fixed SQL string and is executed a significant number of times, the prepared statement can be prepared only once and re-used afterward. Please notice that once prepared with caching, the statement will remain open until it is closed explicitly.
      Parameters:
      statementCached - true to prepare only once and reuse the cached statement, false if one-shot

    • isStatementCached

      public boolean isStatementCached()
      Returns whether statement caching is enabled.
      Returns:
      true to prepare only once and reuse the cached statement, false if one-shot (default)

    • setOffset

      public void setOffset(int offset)
      Sets an offset, i.e., number of rows to skip in a query.
      By default, the offset is 0.
      Parameters:
      offset - the offset, 0 to disable

    • getOffset

      public int getOffset()
      Gets the offset of this query.
      Returns:
      the number of rows to skip, 0 if no offset

    • setLimit

      public void setLimit(int limit)
      Sets the maximum number of rows to retrieve for this query. e
      Parameters:
      limit - the maximum number of rows, 0 if unlimited (default)

    • getLimit

      public int getLimit()
      Gets the maximum number of rows for this query.
      Returns:
      the maximum number of rows, 0 if unlimited (default)

    • add

      public void add(CharSequence sql, Object... data)
      Appends an SQL part and corresponding parameters to this query. 
      Example: add(" AND CN_ID=?", object.getId());
         add(" AND CN_MONEY=?", object.getAmount());
      Parameters:
      sql - the SQL-string, null or empty if just data
      data - an array of parameters

    • add

      public void add(CharSequence sql, List<Object> data)
      Appends an SQL part and corresponding parameters to this query.
      Same as add(CharSequence, Object...) but with a List of parameters instead of an array or varargs.
      Parameters:
      sql - the SQL-string, null or empty if just data
      data - the List of parameters

    • add

      public void add(SqlSupplier sqlSupplier)
      Appends a backend-specific SQL part to this query.
      Parameters:
      sqlSupplier - supplier for backend-specific SQL code

    • add

      public void add(int index, CharSequence sql, Object... data)
      Appends an SQL part and corresponding parameters to this query. 
      Example: add(" AND CN_ID=?", object.getId());
         add(" AND CN_MONEY=?", object.getAmount());
      Parameters:
      index - index at which the specified SQL is to be inserted
      sql - the SQL string, null or empty if just data
      data - an array of parameters

    • add

      public void add(int index, CharSequence sql, List<Object> data)
      Appends an SQL part and corresponding parameters to this query.
      Same as add(int, CharSequence, Object...) but with a List of parameters instead of an array or varargs.
      Parameters:
      index - index at which the specified SQL is to be inserted
      sql - the SQL string, null or empty if just data
      data - the List of parameters

    • add

      public void add(int index, SqlSupplier sqlSupplier)
      Appends a backend-specific SQL part to this query.
      Parameters:
      index - index at which the specified SQL is to be inserted
      sqlSupplier - supplier for backend-specific SQL code

    • setFetchSize

      public void setFetchSize(int fetchSize)
      Sets the optional fetch size. 0 = driver's default.
      Parameters:
      fetchSize - the fetch size
      See Also:

    • getFetchSize

      public int getFetchSize()
      Gets the fetch size.
      Returns:
      the fetch size
      See Also:

    • setMaxRows

      public void setMaxRows(int maxRows)
      Sets the optional maximum row count for this cursor.
      Parameters:
      maxRows - the max rows, 0 = no limit

    • getMaxRows

      public int getMaxRows()
      Gets the maximum row count for this cursor
      Returns:
      the max rows, 0 = no limit

    • execute

      public ResultSetWrapper execute(Db db, int resultSetType, int resultSetConcurrency)
      Executes the query.
      Parameters:
      db - the session
      resultSetType - one of ResultSet.TYPE_...
      resultSetConcurrency - is one of ResultSet.CONCUR_..
      Returns:
      the result set

    • execute

      public ResultSetWrapper execute(Db db, int resultSetType)
      Executes the query with ResultSet.CONCUR_READ_ONLY.
      Parameters:
      db - is the session
      resultSetType - is one of ResultSet.TYPE_...
      Returns:
      the result set

    • execute

      public ResultSetWrapper execute(Db db)
      Executes the query with ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY.
      Parameters:
      db - is the session
      Returns:
      the result set

    • getRowCount

      public int getRowCount(Db db)
      Gets the number of objects returned by this query.

      This is not done by retrieving all rows but by a "SELECT COUNT(1)". Applications may use this in conjunction with limit and offset for pagination (in web pages, for example).

      Parameters:
      db - the session
      Returns:
      the number of rows for this query

    • apply

      public void apply(Db db, PreparedStatementWrapper st)
      Applies the query parameters to the statement.
      Optionally applies limit, offset as well.
      Parameters:
      db - the session
      st - the prepared statement

    • applyParameters

      public int applyParameters(PreparedStatementWrapper st, int ndx)
      Apply the query parameters only.
      All predefined datatypes are supported. Values of other types are applied via their DataType, if there is one registered for the value's class.

      Notice that some datatypes span more than one column (see DataType.getColumnCount(Backend)). The application must provide the corresponding number of question marks in the SQL code.

      Parameters:
      st - the statement
      ndx - the starting index
      Returns:
      the next parameter index

    • createSql

      public StringBuilder createSql(Backend backend)
      Creates the SQL code of this query.
      Optionally modifies the query according to limit and offset.
      Parameters:
      backend - the backend
      Returns:
      the SQL code

    • createStatementKey

      public StatementKey createStatementKey(String sql)
      Creates the statement key.
      Parameters:
      sql - the SQL string sent to the backend
      Returns:
      the key

    • buildInnerSql

      public StringBuilder buildInnerSql(Backend backend)
      Builds the inner sql query string.
      Parameters:
      backend - the backend
      Returns:
      the SQL string