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 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

    • resultSetMapper

      default @NonNull Query resultSetMapper(@Nullable ResultSetMapper resultSetMapper)
      Overrides the Database-wide ResultSetMapper for this query only.

      This enables per-query inline mapping - for example, projecting an ad-hoc join or computed columns - without configuring a database-wide mapper. ResultSetMapper is a functional interface, so a lambda works: 

       database.query("SELECT name, COUNT(*) AS total FROM employee GROUP BY name")
  .resultSetMapper((ctx, rs, type, ip) -> Optional.of(type.cast(new NameCount(rs.getString(1), rs.getLong(2)))))
  .fetchList(NameCount.class);
      The override applies to every row this query maps (including fetchStream(Class, Function) rows and DML-returning results). Other queries on the same Database are unaffected. Metrics, statement logging, and exception diagnostics behave identically with an override present.
      Parameters:
      resultSetMapper - the mapper to use for this query, or null to inherit the database-wide mapper
      Returns:
      this builder, for chaining
      Since:
      4.5.0

    • preparedStatementBinder

      default @NonNull Query preparedStatementBinder(@Nullable PreparedStatementBinder preparedStatementBinder)
      Overrides the Database-wide PreparedStatementBinder for this query only.

      The override receives every non-null parameter for this query - including expanded IN-list elements and each batch group's parameters. As with the database-wide SPI, SecureParameter and Optional wrappers are unwrapped by Pyranid before the binder is invoked, so custom binders receive bound-ready raw values and need no unwrap logic. null parameters never reach the binder; Pyranid binds them via PreparedStatement.setNull(int, int) even when an override is present.

      Other queries on the same Database are unaffected.

      Parameters:
      preparedStatementBinder - the binder to use for this query, or null to inherit the database-wide binder
      Returns:
      this builder, for chaining
      Since:
      4.5.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. It runs before Pyranid binds parameters.

      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

    • mapRowType

      static @NonNull Class<Map<String,Object>> mapRowType()
      Acquires a result type token for fetching rows as insertion-ordered Map<String, Object> instances, usable anywhere a result type token is accepted.

      Java's type erasure means there is no Map<String, Object>.class literal - a raw Map.class token can only ever infer the raw Map type at fetch sites. This method returns the same runtime Map.class token, statically typed as Class<Map<String, Object>> so results are properly parameterized without caller-side casts: 

       List<Map<String, Object>> rows = database.query("SELECT * FROM car")
  .fetchList(Query.mapRowType());
      This is safe because Pyranid's default mapping produces LinkedHashMap<String, Object> rows for this token. Note that a custom ResultSetMapper receiving this token observes the raw Map.class.
      Returns:
      a Map<String, Object> result type token
      Since:
      4.5.0

    • 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 RETURNING, MariaDB INSERT ... 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 RETURNING, MariaDB INSERT ... RETURNING, or SQL Server OUTPUT).
      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal each row to
      Returns:
      list of results