Pyranid Logo

Core Concepts

Queries

Queries are for pulling data out of your database and saying "I expect a single object" or "I expect a list (or stream) of objects".

An object for each row is created by your InstanceProvider.

Rules for how Pyranid binds your plain-Java parameters to Prepared Statement placeholders are outlined in the Parameter Binding section.

Rules for how Resultset data gets copied back into your Java objects are outlined in the ResultSet Mapping section.

Pyranid is opinionated regarding nullability and embraces the use of Optional<T>.

If you ask for a single object, e.g. querying by an identifier, Optional::empty() is returned if the resultset has no rows. Similarly, if you ask for a list of objects and none match your query criteria, the empty list is returned as opposed to null.

Querying Basics

Use Database::query(String) and bind values by name with Query::bind(...). Only named parameters are supported.

SQL Parsing Rules

Pyranid scans SQL before handing it to JDBC so it can translate named parameters into PreparedStatement placeholders. It ignores parameter-looking text inside string literals, quoted identifiers, comments, PostgreSQL dollar-quoted strings, and SQL Server-style bracket-quoted identifiers. Unterminated quotes, dollar-quoted strings, and block comments fail fast with an IllegalArgumentException.

PostgreSQL JSONB/hstore ?, ?|, and ?& operators are supported. When the Database is configured or detected as PostgreSQL, Pyranid automatically emits pgjdbc's escaped ?? form for those operators while preserving named-parameter binding.

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

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  WHERE department_id IN (:departmentIds)
  """)
  .bind("departmentIds", Parameters.inList(List.of(1, 2, 3)))
  .fetchList(Employee.class);

You can also bind multiple parameters at once:

Map<String, Object> params = Map.of(
  "departmentId", 8,
  "minSalary", 100_000
);

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  WHERE department_id = :departmentId
    AND salary >= :minSalary
  """)
  .bindAll(params)
  .fetchList(Employee.class);

Common JDBC statement settings are available directly on Query:

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  WHERE department_id = :departmentId
  """)
  .bind("departmentId", departmentId)
  .queryTimeout(Duration.ofSeconds(10))
  .fetchSize(500)
  .maxRows(1_000)
  .fetchList(Employee.class);

Use Database.Builder::queryTimeout(...), Database.Builder::fetchSize(...), and Database.Builder::maxRows(...) to configure database-wide defaults. Per-query settings override database defaults, and Query::customize(...) runs last.

Query::queryTimeout(...) maps to JDBC Statement::setQueryTimeout(...). For driver-specific cancellation beyond timeouts, capture the PreparedStatement in Query::customize(...) and call Statement::cancel() from your application's cancellation path.

You may customize the underlying PreparedStatement before execution beyond built-in statement settings with a PreparedStatementCustomizer:

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  """)
  .customize((statementContext, preparedStatement) -> {
    preparedStatement.setPoolable(false);
  })
  .fetchList(Employee.class);

Plain Old Java Objects

Suppose we have a custom Car like this:

enum Color { BLUE, RED }

// Follows JavaBean conventions for getters/setters
class Car {
  Long id;
  Color color;

  // You may explicitly specify the name of the resultset column
  // if you'd like a different name in your Java code
  @DatabaseColumn("vehicle_identifier")
  String vin;

  Long getId() { return this.id; }
  void setId(Long id) { this.id = id; }

  Color getColor() { return this.color; }
  void setColor(Color color) { this.color = color; }

  String getVin() { return this.vin; }
  void setVin(String vin) { this.vin = vin; }  
}

We might query for it like this:

// A single car
Optional<Car> car = database.query("""
  SELECT *
  FROM car
  WHERE id = :id
  """)
  .bind("id", 123)
  .fetchObject(Car.class);

// Multiple cars
List<Car> blueCars = database.query("""
  SELECT *
  FROM car
  WHERE color = :color
  """)
  .bind("color", Color.BLUE)
  .fetchList(Car.class);

// In addition to custom types, you can map to primitives 
// and many JDK builtins out of the box.
// See 'ResultSet Mapping' section for details
Optional<UUID> id = database.query("""
  SELECT id
  FROM widget
  LIMIT 1
  """)
  .fetchObject(UUID.class);

List<BigDecimal> balances = database.query("""
  SELECT balance
  FROM account
  """)
  .fetchList(BigDecimal.class);

By default, Pyranid will invoke your mutator methods as opposed to directly assigning values to fields. For example, Car::setColor(Color) would be used instead of Car::color.

References:

Records

Record types are also supported:

record Employee(String name, @DatabaseColumn("email") String emailAddress) {}

Optional<Employee> employee = database.query("""
  SELECT *
  FROM employee
  WHERE email = :email
  """)
  .bind("email", "name@example.com")
  .fetchObject(Employee.class);

By default, Pyranid will invoke the canonical constructor for Record types.

Streaming Results

If you'd like to process large resultsets (e.g. millions of rows) without loading everything into memory, use Query::fetchStream(Class<T>, Function<Stream<T>, R>). The Stream<T> passed to your callback is backed by the underlying java.sql.ResultSet, and Pyranid closes JDBC resources automatically when the callback returns (or throws).

List<Employee> employees = database.query("""
    SELECT *
    FROM employee
    WHERE department_id = :departmentId
    """)
    .bind("departmentId", 42)
    .fetchStream(Employee.class, (stream) ->
      stream.filter((employee) -> employee.departmentId() == 42)
        .toList());

Heads Up!

To avoid resource leaks, make sure that you consume the entire stream within the callback. Don't store off any references to the stream which would cause it to "escape" the callback. Inside a Pyranid transaction, the stream must be closed by the thread that opened it.

Supported dialects apply driver-specific streaming setup automatically. PostgreSQL streams use an autocommit-disabled connection and a positive JDBC fetch size when no Pyranid transaction is active. MySQL streams use forward-only/read-only statements and Connector/J's streaming fetch-size sentinel. MariaDB streams use forward-only/read-only statements without the MySQL sentinel. Use Query::fetchSize(...) to override the dialect default when needed, including 0 when you deliberately want the driver default.

List<Employee> employees = database.query("""
    SELECT *
    FROM employee
    ORDER BY employee_id
    """)
    .fetchSize(1_000)
    .fetchStream(Employee.class, (stream) ->
      stream.limit(10_000).toList());

References:

Raw JDBC Connection Access

If you need a JDBC escape hatch for driver-specific features, stored procedures, CallableStatement, or other operations that do not fit Pyranid's query API, use Database::useRawConnection(...).

Optional<Integer> result = database.useRawConnection(connection -> {
  try (CallableStatement statement = connection.prepareCall("{ ? = call calculate_bonus(?) }")) {
    statement.registerOutParameter(1, Types.INTEGER);
    statement.setLong(2, employeeId);
    statement.execute();
    return Optional.of(statement.getInt(1));
  }
});

Database::useRawConnection(...) automatically participates in an active Pyranid transaction. Outside a transaction, Pyranid borrows a connection for the raw connection callback and closes it when the callback returns.

database.transaction(() -> {
  database.useRawConnection(connection -> {
    try (PreparedStatement statement = connection.prepareStatement("INSERT INTO audit_log VALUES (?)")) {
      statement.setString(1, "bonus calculated");
      statement.executeUpdate();
    }

    return Optional.empty();
  });
});

Pyranid Owns the Connection Lifecycle

The Connection passed to your callback is a Pyranid-managed guarded handle. Do not close it, retain it, perform transaction lifecycle operations on it, or mutate connection-wide state on it. Methods such as close(), commit(), rollback(), setAutoCommit(...), setTransactionIsolation(...), setCatalog(...), setSchema(...), setClientInfo(...), setNetworkTimeout(...), and JDBC Savepoint controls throw immediately. Statement::getConnection() and DatabaseMetaData::getConnection() return the guarded Pyranid handle, and ResultSet::getStatement() returns a guarded statement. Guarded statements, resultsets, and metadata refuse driver-specific unwrap(...) calls that could expose the driver's underlying connection. Use Database::transaction(...), Database::participate(...), and Transaction savepoint APIs for transaction management. Close any Statement or ResultSet instances you create inside the callback.

Previous
Configuration
Next
Statements