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

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

You may customize the underlying PreparedStatement before execution with a PreparedStatementCustomizer:

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  """)
  .customize((statementContext, preparedStatement) -> {
    preparedStatement.setFetchSize(500);
    preparedStatement.setQueryTimeout(10);
  })
  .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.

References:

Previous
Configuration
Next
Statements