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

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

    • customize

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

      If called multiple times, the most recent customizer wins.

      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.

      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

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

      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 (e.g., with RETURNING clause).
      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 (e.g., with RETURNING clause).
      Type Parameters:
      T - the result type
      Parameters:
      resultType - the type to marshal each row to
      Returns:
      list of results