Pyranid | Queries

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

An object for each row is created by your InstanceProvider.

Rules for how Resultset data gets into your 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.

Usage

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

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.queryForObject(
  "SELECT * FROM car LIMIT 1", Car.class);

// A single car, passing prepared statement parameters via varargs
Optional<Car> specificCar = database.queryForObject(
  "SELECT * FROM car WHERE id=?", Car.class, 123);

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

// 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.queryForObject(
  "SELECT id FROM widget LIMIT 1", UUID.class);

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

// A single car
Optional<Car> car = database.queryForObject(
  "SELECT * FROM car LIMIT 1", Car.class);

// A single car, passing prepared statement parameters via varargs
Optional<Car> specificCar = database.queryForObject(
  "SELECT * FROM car WHERE id=?", Car.class, 123);

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

// 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.queryForObject(
  "SELECT id FROM widget LIMIT 1", UUID.class);

List<BigDecimal> balances = database.queryForList(
  "SELECT balance FROM account", 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.queryForObject("""
  SELECT *
  FROM employee
  WHERE email=?
  """, Employee.class, "name@example.com");

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

Optional<Employee> employee = database.queryForObject("""
  SELECT *
  FROM employee
  WHERE email=?
  """, Employee.class, "name@example.com");

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