Interface Query

@NotThreadSafe public interface Query
Fluent builder for SQL statements.

Obtain instances via Database.query(String). Positional parameters via ? are not supported; use named parameters (e.g. :id) and bind(String, Object). Parameter-looking text inside SQL string literals, quoted identifiers, comments, PostgreSQL dollar-quoted strings, and SQL Server-style bracket-quoted identifiers is ignored. PostgreSQL JSONB/hstore ?, ?|, and ?& operators are supported and are escaped automatically for pgjdbc when the Database is configured or detected as PostgreSQL.

Example usage: 

// Query returning one row
Optional<Employee> employee = database.query("SELECT * FROM employee WHERE id = :id")
  .bind("id", 42)
  .fetchObject(Employee.class);

// Query returning multiple rows
List<Employee> employees = database.query("SELECT * FROM employee WHERE dept = :dept")
  .bind("dept", "Engineering")
  .fetchList(Employee.class);

// DML with no result
long rowsAffected = database.query("UPDATE employee SET active = :active WHERE id = :id")
  .bind("id", 42)
  .bind("active", false)
  .execute();

// DML with RETURNING clause
Optional<Employee> updated = database.query("UPDATE employee SET salary = :salary WHERE id = :id RETURNING *")
  .bind("id", 42)
  .bind("salary", new BigDecimal("150000"))
  .executeForObject(Employee.class);

Implementations of this interface are intended for use by a single thread.

Since:
4.0.0
Author:
Mark Allen
See Also:

  • Method Summary

    Modifier and Type
    Method
    Description
    default @NonNull Query
    batchChunkSize(@Nullable Integer batchChunkSize)
    Configures the maximum number of parameter groups to send in each JDBC batch execution.
    @NonNull Query
    bind(@NonNull String name, @Nullable Object value)
    Binds a named parameter to a value.
    @NonNull Query
    bindAll(@NonNull Map<@NonNull String, @Nullable Object> parameters)
    Binds all entries from the given map as named parameters.
    @NonNull Query
    customize(@NonNull PreparedStatementCustomizer preparedStatementCustomizer)
    Customizes the PreparedStatement before execution.
    @NonNull Long
    execute()
    Executes a DML statement (INSERT, UPDATE, DELETE) with no resultset.
    @NonNull List<Long>
    executeBatch(@NonNull List<@NonNull Map<@NonNull String, @Nullable Object>> parameterGroups)
    Executes a DML statement in batch over groups of named parameters.
    <T> @NonNull List<@Nullable T>
    executeForList(@NonNull Class<T> resultType)
    Executes a DML statement that returns multiple rows (for example, with PostgreSQL/SQLite/MariaDB RETURNING or SQL Server OUTPUT).
    <T> @NonNull Optional<T>
    executeForObject(@NonNull Class<T> resultType)
    Executes a DML statement that returns a single row (for example, with PostgreSQL/SQLite/MariaDB RETURNING or SQL Server OUTPUT).
    default <T> @NonNull Optional<T>
    executeReturningGeneratedKey(@NonNull Class<T> resultType)
    Executes a DML statement and maps a single JDBC-generated key row.
    default <T> @NonNull Optional<T>
    executeReturningGeneratedKey(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames)
    Executes a DML statement and maps a single JDBC-generated key row.
    default <T> @NonNull List<@Nullable T>
    executeReturningGeneratedKeys(@NonNull Class<T> resultType)
    Executes a DML statement and maps all JDBC-generated key rows.
    default <T> @NonNull List<@Nullable T>
    executeReturningGeneratedKeys(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames)
    Executes a DML statement and maps all JDBC-generated key rows.
    <T> @NonNull List<@Nullable T>
    fetchList(@NonNull Class<T> resultType)
    Executes the query and returns all results as a list.
    <T> @NonNull Optional<T>
    fetchObject(@NonNull Class<T> resultType)
    Executes the query and returns a single result.
    default @NonNull Query
    fetchSize(@Nullable Integer fetchSize)
    Configures the JDBC fetch size for this query.
    <T,R> @Nullable R
    fetchStream(@NonNull Class<T> resultType, @NonNull Function<Stream<@Nullable T>, R> streamFunction)
    Executes the query and provides a Stream backed by the underlying ResultSet.
    @NonNull Query
    id(@Nullable Object id)
    Associates an identifier with this query for logging/diagnostics.
    default @NonNull Query
    maxRows(@Nullable Integer maxRows)
    Configures the JDBC maximum row count for this query.
    default @NonNull Query
    queryTimeout(@Nullable Duration queryTimeout)
    Configures the JDBC query timeout for this query.

  • Method Details

    • bind

      @NonNull Query bind(@NonNull String name, @Nullable Object value)
      Binds a named parameter to a value.
      Parameters:
      name - the parameter name (without the leading :)
      value - the value to bind (may be null). Raw Collection and array values are not expanded; use Parameters.inList(java.util.Collection), Parameters.sqlArrayOf(String, Object[]), or Parameters.arrayOf(Class, Object) as appropriate.
      Returns:
      this builder, for chaining

    • bindAll

      @NonNull Query bindAll(@NonNull Map<@NonNull String, @Nullable Object> parameters)
      Binds all entries from the given map as named parameters.
      Parameters:
      parameters - map of parameter names to values
      Returns:
      this builder, for chaining

    • id

      @NonNull Query id(@Nullable Object id)
      Associates an identifier with this query for logging/diagnostics.

      If not called, a default ID will be generated.

      Parameters:
      id - the identifier
      Returns:
      this builder, for chaining

    • queryTimeout

      default @NonNull Query queryTimeout(@Nullable Duration queryTimeout)
      Configures the JDBC query timeout for this query.

      This maps to Statement.setQueryTimeout(int). null leaves the timeout unset so any Database.Builder.queryTimeout(Duration) default applies. Duration.ZERO disables the JDBC timeout. Positive sub-second durations are rounded up to one second because JDBC accepts whole seconds.

      Parameters:
      queryTimeout - timeout to apply, or null to inherit the database default
      Returns:
      this builder, for chaining
      Since:
      4.2.0

    • fetchSize

      default @NonNull Query fetchSize(@Nullable Integer fetchSize)
      Configures the JDBC fetch size for this query.

      This maps to Statement.setFetchSize(int). null leaves the fetch size unset so any Database.Builder.fetchSize(Integer) default applies. A value of 0 uses the driver's default fetch-size behavior.

      Parameters:
      fetchSize - fetch size to apply, or null to inherit the database default
      Returns:
      this builder, for chaining
      Since:
      4.2.0

    • maxRows

      default @NonNull Query maxRows(@Nullable Integer maxRows)
      Configures the JDBC maximum row count for this query.

      This maps to Statement.setMaxRows(int). null leaves the maximum row count unset so any Database.Builder.maxRows(Integer) default applies. A value of 0 disables the JDBC row limit.

      Parameters:
      maxRows - maximum rows to apply, or null to inherit the database default
      Returns:
      this builder, for chaining
      Since:
      4.2.0

    • batchChunkSize

      default @NonNull Query batchChunkSize(@Nullable Integer batchChunkSize)
      Configures the maximum number of parameter groups to send in each JDBC batch execution.

      This setting applies only to executeBatch(List). null leaves batch execution unchunked, preserving the default behavior of sending all parameter groups in one JDBC batch. A positive value causes Pyranid to execute multiple JDBC batches as needed and flatten the returned update counts in input order. If this setting is specified and a non-batch terminal operation is invoked, Pyranid throws IllegalStateException.

      Chunking is performed by Pyranid. JDBC drivers still execute each chunk as a normal JDBC batch. If a later chunk fails outside an explicit transaction, earlier chunks may already be committed depending on autocommit and driver behavior. Wrap chunked batches in Database.transaction(TransactionalOperation) when all-or-nothing behavior is required.

      Parameters:
      batchChunkSize - maximum parameter groups per JDBC batch execution, or null to execute one batch
      Returns:
      this builder, for chaining
      Throws:
      IllegalArgumentException - if batchChunkSize is less than or equal to 0
      Since:
      4.2.0

    • customize

      @NonNull Query customize(@NonNull PreparedStatementCustomizer preparedStatementCustomizer)
      Customizes the PreparedStatement before execution.

      If called multiple times, the most recent customizer wins. The customizer runs after database-wide statement settings and this query's queryTimeout(Duration), fetchSize(Integer), and maxRows(Integer) settings, so it can override them when needed.

      For dialect-managed streams, Pyranid may apply driver-specific stream settings after this callback unless this query explicitly configured fetchSize(Integer).

      For driver-specific cancellation beyond queryTimeout(Duration), application code may capture the PreparedStatement here and call Statement.cancel() from its own cancellation path. Cancellation behavior is JDBC-driver-specific.

      Parameters:
      preparedStatementCustomizer - customization callback
      Returns:
      this builder, for chaining

    • fetchObject

      <T> @NonNull Optional<T> fetchObject(@NonNull Class<T> resultType)
      Executes the query and returns a single result.
      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal each row to
      Returns:
      the single result, or empty if no rows
      Throws:
      DatabaseException - if more than one row is returned

    • fetchList

      <T> @NonNull List<@Nullable T> fetchList(@NonNull Class<T> resultType)
      Executes the query and returns all results as a list.
      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal each row to
      Returns:
      list of results (empty if no rows)

    • fetchStream

      <T,R> @Nullable R fetchStream(@NonNull Class<T> resultType, @NonNull Function<Stream<@Nullable T>, R> streamFunction)
      Executes the query and provides a Stream backed by the underlying ResultSet.

      This approach is useful for processing very large resultsets (e.g. millions of rows), where it's impractical to load all rows into memory at once.

      JDBC resources are closed automatically when streamFunction returns (or throws), so the stream must be fully consumed within that callback. Do not escape the stream from the function.

      The stream must be consumed within the scope of the transaction or connection that created it. If the stream participates in a Pyranid transaction, it must also be closed by the thread that opened it.

      Supported dialects apply driver-specific streaming setup automatically. PostgreSQL streams use a positive JDBC fetch size and an autocommit-disabled connection when no Pyranid transaction is active; MySQL streams use forward-only/read-only statements and the MySQL streaming fetch-size sentinel; MariaDB streams use forward-only/read-only statements without the MySQL sentinel. Use fetchSize(Integer) to override fetch-size behavior when needed.

      Type Parameters:
      T - the result type
      R - the return type
      Parameters:
      resultType - the type to marshal each row to
      streamFunction - function that consumes the stream and returns a result
      Returns:
      the value returned by streamFunction

    • execute

      @NonNull Long execute()
      Executes a DML statement (INSERT, UPDATE, DELETE) with no resultset.
      Returns:
      the number of rows affected

    • executeReturningGeneratedKey

      default <T> @NonNull Optional<T> executeReturningGeneratedKey(@NonNull Class<T> resultType)
      Executes a DML statement and maps a single JDBC-generated key row.

      This uses JDBC Statement.RETURN_GENERATED_KEYS. It is intended for database-generated values such as identity/auto-increment primary keys. If your SQL returns rows directly via database syntax such as PostgreSQL RETURNING or SQL Server OUTPUT, use executeForObject(Class) instead.

      For SQL Server multi-row identity inserts, use OUTPUT with executeForList(Class) instead of JDBC generated keys. For MySQL INSERT ... ON DUPLICATE KEY UPDATE, use the LAST_INSERT_ID(id) idiom if you need the existing row's ID returned on the update path.

      Oracle requires explicit generated-key column names; use executeReturningGeneratedKey(Class, String...) instead.

      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal the generated-key row to
      Returns:
      the single generated key row, or empty if the driver returns no generated keys
      Throws:
      DatabaseException - if more than one generated-key row is returned
      Since:
      4.2.0

    • executeReturningGeneratedKey

      default <T> @NonNull Optional<T> executeReturningGeneratedKey(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames)
      Executes a DML statement and maps a single JDBC-generated key row.

      This uses JDBC Connection.prepareStatement(String, String[]) with the supplied key column names. Some drivers require column names to return generated keys for specific columns, especially when more than one generated value is available. If keyColumnNames is empty, this behaves like executeReturningGeneratedKey(Class), except for dialects such as Oracle which require explicit names.

      For SQL Server multi-row identity inserts, use OUTPUT with executeForList(Class) instead of JDBC generated keys. For MySQL INSERT ... ON DUPLICATE KEY UPDATE, use the LAST_INSERT_ID(id) idiom if you need the existing row's ID returned on the update path.

      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal the generated-key row to
      keyColumnNames - generated-key column names requested from the driver
      Returns:
      the single generated key row, or empty if the driver returns no generated keys
      Throws:
      DatabaseException - if more than one generated-key row is returned
      Since:
      4.2.0

    • executeReturningGeneratedKeys

      default <T> @NonNull List<@Nullable T> executeReturningGeneratedKeys(@NonNull Class<T> resultType)
      Executes a DML statement and maps all JDBC-generated key rows.

      This uses JDBC Statement.RETURN_GENERATED_KEYS. It is intended for database-generated values such as identity/auto-increment primary keys. If your SQL returns rows directly via database syntax such as PostgreSQL RETURNING or SQL Server OUTPUT, use executeForList(Class) instead.

      For SQL Server multi-row identity inserts, use OUTPUT with executeForList(Class) instead of JDBC generated keys. For MySQL INSERT ... ON DUPLICATE KEY UPDATE, use the LAST_INSERT_ID(id) idiom if you need the existing row's ID returned on the update path.

      Oracle requires explicit generated-key column names; use executeReturningGeneratedKeys(Class, String...) instead.

      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal each generated-key row to
      Returns:
      list of generated key rows
      Since:
      4.2.0

    • executeReturningGeneratedKeys

      default <T> @NonNull List<@Nullable T> executeReturningGeneratedKeys(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames)
      Executes a DML statement and maps all JDBC-generated key rows.

      This uses JDBC Connection.prepareStatement(String, String[]) with the supplied key column names. Some drivers require column names to return generated keys for specific columns, especially when more than one generated value is available. If keyColumnNames is empty, this behaves like executeReturningGeneratedKeys(Class), except for dialects such as Oracle which require explicit names.

      For SQL Server multi-row identity inserts, use OUTPUT with executeForList(Class) instead of JDBC generated keys. For MySQL INSERT ... ON DUPLICATE KEY UPDATE, use the LAST_INSERT_ID(id) idiom if you need the existing row's ID returned on the update path.

      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal each generated-key row to
      keyColumnNames - generated-key column names requested from the driver
      Returns:
      list of generated key rows
      Since:
      4.2.0

    • executeBatch

      @NonNull List<Long> executeBatch(@NonNull List<@NonNull Map<@NonNull String, @Nullable Object>> parameterGroups)
      Executes a DML statement in batch over groups of named parameters.

      Any parameters already bound on this Query apply to all groups; group values override them. Each group must provide a complete set of parameter values after merging; groups must be non-null and expand to the same number of JDBC parameters (for example, IN-list sizes must match).

      Parameters:
      parameterGroups - groups of named parameter values (without the leading :)
      Returns:
      the number of rows affected by the SQL statement per-group

    • executeForObject

      <T> @NonNull Optional<T> executeForObject(@NonNull Class<T> resultType)
      Executes a DML statement that returns a single row (for example, with PostgreSQL/SQLite/MariaDB RETURNING or SQL Server OUTPUT).
      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal the row to
      Returns:
      the single result, or empty if no rows
      Throws:
      DatabaseException - if more than one row is returned

    • executeForList

      <T> @NonNull List<@Nullable T> executeForList(@NonNull Class<T> resultType)
      Executes a DML statement that returns multiple rows (for example, with PostgreSQL/SQLite/MariaDB RETURNING or SQL Server OUTPUT).
      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal each row to
      Returns:
      list of results