Pyranid Logo

Core Concepts

ResultSet Mapping

When you execute a SQL query, Pyranid will walk each row in the in the java.sql.ResultSet and ask your ResultSetMapper to provide an object representation of that row.

@FunctionalInterface
public interface ResultSetMapper {
  // T is the type to which we're mapping this row.
  @NonNull
  <T> Optional<T> map(
    @NonNull StatementContext<T> statementContext,
    @NonNull ResultSet resultSet,
    @NonNull Class<T> resultSetRowType,
    @NonNull InstanceProvider instanceProvider
  ) throws SQLException;
}

The ResultSetMapper is given an InstanceProvider so it can create an object to hold the row's data. It's the mapper's job to copy the row's data into that object and return it.

The out-of-the-box implementation supports mapping common JDK types as well as your JavaBeans and Record types, and generally "just works" as you would expect.

If you need to customize mapping behavior, you might bring your own list of CustomColumnMapper...

// CustomColumnMappers supply "surgical" overrides to handle custom types.
// If multiple mappers apply, Pyranid tries them in list order.
// Normalization locale should match the language of your database tables/column names.
// Plan caching (on by default) trades memory for faster mapping of wide ResultSets
ResultSetMapper resultSetMapper = ResultSetMapper.withCustomColumnMappers(List.of(...))
  .normalizationLocale(Locale.forLanguageTag("pt-BR"))  
  .planCachingEnabled(false)
  .build();

Database database = Database.withDataSource(dataSource)
  .resultSetMapper(resultSetMapper)
  .build();

...or you might choose to directly implement the ResultSetMapper interface for fine-grained control:

ResultSetMapper resultSetMapper = new ResultSetMapper() {
  @Override
  @NonNull
  <T> Optional<T> map(
    @NonNull StatementContext<T> statementContext,
    @NonNull ResultSet resultSet,
    @NonNull Class<T> resultSetRowType,
    @NonNull InstanceProvider instanceProvider
  ) throws SQLException {
    // Do your mapping here
  }
};

Database database = Database.withDataSource(dataSource)
  .resultSetMapper(resultSetMapper)
  .build();

Standard Types

When querying for a single column, e.g. a SQL COUNT, it's often useful to map to a standard type like String, Integer, or Boolean.

There's no need to create a custom "row" type to hold the result.

// Returns Optional<Long>, which we immediately unwrap because COUNT(*) is never null
Long count = database.query("SELECT COUNT(*) FROM car")
  .fetchObject(Long.class)
  .orElseThrow();

// Standard primitives and JDK types are supported by default
Optional<UUID> id = database.query("SELECT id FROM employee LIMIT 1")
  .fetchObject(UUID.class);

// Lists work as you would expect
List<String> names = database.query("SELECT name FROM employee")
  .fetchList(String.class);

User-defined Types

In the case of user-defined types and Record types, the standard ResultSetMapper examines the names of columns in the ResultSet and matches them to corresponding fields via reflection. The @DatabaseColumn annotation allows per-field customization of mapping behavior.

JavaBean and Record mapping require at least one selected column to match a writable property or record component. If no selected columns match, Pyranid raises a DatabaseException instead of returning an object with all default values. Use column aliases or a custom ResultSetMapper when the default property matching is not appropriate.

By default, column names are assumed to be separated by _ characters and are mapped to their camel-case equivalent. For example:

class Car {
  Long carId;
  Color color;
  
  // For schema flexibility, Pyranid will match both "deposit_amount1" and "deposit_amount_1" column names
  BigDecimal depositAmount1;
  
  // Use this annotation to specify variants if the field name doesn't match the column name
  @DatabaseColumn({"systok", "sys_tok"})
  UUID systemToken;

  Long getCarId() { return this.carId; }
  void setCarId(Long carId) { this.carId = carId; }

  Color getColor() { return this.color; }
  void setColor(Color color) { this.color = color; }
  
  BigDecimal getDepositAmount1() { return this.depositAmount1; }
  void setDepositAmount1(BigDecimal depositAmount1) { this.depositAmount1 = depositAmount1; }  

  UUID getSystemToken() { return this.systemToken; }
  void setSystemToken(UUID systemToken) { this.systemToken = systemToken; }
}

Car car = database.query("""
  SELECT car_id, color, systok
  FROM car
  LIMIT 1
""")
  .fetchObject(Car.class)
  .orElseThrow();

// Output might be "Car ID is 123 and color is BLUE. Token is d73c523a-8344-44ef-819c-40467662d619"
out.printf("Car ID is %s and color is %s. Token is %s\n",
  car.getCarId(), car.getColor(), car.getSystemToken());

// Column names will work with wildcard queries as well
car = database.query("""
  SELECT *
  FROM car
  LIMIT 1
""")
  .fetchObject(Car.class)
  .orElseThrow();

// Column aliases work too
car = database.query("""
  SELECT some_id AS car_id, some_color AS color
  FROM car
  LIMIT 1
""")
  .fetchObject(Car.class)
  .orElseThrow();

Supported Primitives

Supported JDK Types

Temporal result-set mapping uses JDBC ResultSetMetaData for the returned column. Zone-less TIMESTAMP values are wall-clock values; when mapping them to Instant, OffsetDateTime, ZonedDateTime, or Date, Pyranid interprets that wall clock in Database.Builder::timeZone(...). Mapping a zone-less TIMESTAMP to LocalDateTime keeps the wall clock unchanged. TIMESTAMP WITH TIME ZONE values already identify an instant; mapping to Instant preserves that instant, and mapping to ZonedDateTime represents it in the configured Database.Builder::timeZone(...).

Some JDBC drivers expose temporal columns as strings instead of JDBC temporal objects. For JavaBean and record mapping, Pyranid parses ISO-8601 strings and common JDBC timestamp strings such as 2020-01-02 03:04:05 when the target property or component is LocalDate, LocalTime, LocalDateTime, Instant, OffsetDateTime, or ZonedDateTime.

Pyranid preserves the fractional-second precision returned by your JDBC driver; it does not round or truncate Instant, OffsetDateTime, or ZonedDateTime values to milliseconds. The maximum precision is still determined by the database column and driver (for example, PostgreSQL timestamps are stored at microsecond precision).

Invalid TimeZone or Locale values in your resultset will raise a DatabaseException instead of silently falling back to defaults.

SQL ARRAY and JSON Results

SQL ARRAY columns can be mapped to Java array targets such as String[] or UUID[]. They can also be mapped to List<T> or Set<T> when Pyranid can see the generic element type from a Record component or JavaBean setter, for example record Row(List<String> tags, Set<String> labels) {} or void setTags(Set<String> tags). Raw scalar List.class and Set.class targets are supported too, but the element type is whatever the JDBC driver returns because Java's Class<List> and Class<Set> tokens carry no generic type argument. Set targets use insertion order from the SQL ARRAY and apply normal Set semantics, so duplicates collapse and multiple SQL NULL elements become one null.

PostgreSQL JSON/JSONB values returned by pgjdbc as PGobject map to String by default. Pyranid does not parse JSON into application objects; register a CustomColumnMapper when you want to inflate JSON into your own type.

Custom Mapping

Fine-grained control of mapping is supported by registering CustomColumnMapper instances. For example, you might want to "inflate" a JSONB column into a Java type:

When multiple custom column mappers apply, Pyranid tries them in the order supplied. Returning MappingResult::fallback() lets the next applicable mapper run; if none handles the value, normal mapping continues.

When mapping JavaBeans or records, custom column mappers run against matched properties or components. Row-type custom mapping is for single-column results that represent the target value itself.

// Your application-specific type
class MySpecialType {
  List<UUID> uuids;
  Currency currency;	 
}

Just add a CustomColumnMapper that handles it:

ResultSetMapper resultSetMapper = ResultSetMapper.withCustomColumnMappers(List.of(new CustomColumnMapper() {
  @NonNull
  @Override
  public Boolean appliesTo(@NonNull TargetType targetType) {
    // Can also apply to parameterized types, e.g.
    // targetType.matchesParameterizedType(List.class, UUID.class) for List<UUID>
    return targetType.matchesClass(MySpecialType.class);
  }

  @NonNull
  @Override
  public MappingResult map(
    @NonNull StatementContext<?> statementContext,
    @NonNull ResultSet resultSet,
    @NonNull Object resultSetValue,
    @NonNull TargetType targetType,
    @NonNull Integer columnIndex,
    @Nullable String columnLabel,
    @NonNull InstanceProvider instanceProvider
  ) {
    // Pull JSON String data from the ResultSet and inflate it
    String json = resultSetValue.toString();
    MySpecialType mySpecialType = GSON.fromJson(json, MySpecialType.class);

    // Or return MappingResult.fallback() to let the next applicable custom mapper run.
    // If none handles the value, Pyranid continues with normal mapping behavior.
    return MappingResult.of(mySpecialType);
  }
}))
.build();

// Construct your database with the custom mapper
Database database = Database.withDataSource(...)
  .resultSetMapper(resultSetMapper)
  .build();

With the custom mapper in place, and a table like this...

CREATE TABLE row (
  row_id UUID PRIMARY KEY,
  my_special_type JSONB NOT NULL
);

INSERT INTO row (row_id, my_special_type) VALUES (
  'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
  '
    {
      "uuids": ["bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"],
      "currency": "BRL"
    }
  '::jsonb
);

...your application code might look like this:

// A ResultSet row with our special type as a column 
record MyRow(UUID rowId, MySpecialType mySpecialType) {}

// Query for data
List<MyRow> rows = database.query("SELECT * FROM row").fetchList(MyRow.class);

// Examine the first row of the ResultSet
MyRow myRow = rows.getFirst();
// Our custom mapper has instantiated this for us
MySpecialType mySpecialType = myRow.mySpecialType();
// Prints contents of List<UUID>, as expected
out.println(mySpecialType.uuids);
// e.g. "Real brasileiro" for Brazilian Real
out.println(mySpecialType.currency.getDisplayName(Locale.forLanguageTag("pt-BR")));

Your CustomColumnMapper also works for the single-column "Standard Type" scenario.

// Pull back the column for a single row
Optional<MySpecialType> mySpecialType =
  database.query("SELECT my_special_type FROM row LIMIT 1")
    .fetchObject(MySpecialType.class);

// Pull back a list of just the column values 
List<MySpecialType> mySpecialTypes = 
  database.query("SELECT my_special_type FROM row")
    .fetchList(MySpecialType.class);

Parameterized-Type Example

Custom mapping is not limited to concrete classes. You can target parameterized types directly.

Suppose your database stores localized strings in a JSONB column and you want Pyranid to map it to a Map from Locale to String.

ResultSetMapper resultSetMapper = ResultSetMapper.withCustomColumnMappers(List.of(new CustomColumnMapper() {
  @NonNull
  @Override
  public Boolean appliesTo(@NonNull TargetType targetType) {
    return targetType.matchesParameterizedType(Map.class, Locale.class, String.class);
  }

  @NonNull
  @Override
  public MappingResult map(
    @NonNull StatementContext<?> statementContext,
    @NonNull ResultSet resultSet,
    @Nullable Object rawColumnValue,
    @NonNull TargetType targetType,
    @Nullable Integer columnIndex,
    @Nullable String columnLabel,
    @NonNull InstanceProvider instanceProvider
  ) {
    if (rawColumnValue == null)
      return MappingResult.of(null);

    Type mapType = new TypeToken<Map<Locale, String>>() {}.getType();
    Map<Locale, String> valuesByLocale = GSON.fromJson(rawColumnValue.toString(), mapType);
    return MappingResult.of(valuesByLocale);
  }
})).build();

Database database = Database.withDataSource(dataSource)
  .resultSetMapper(resultSetMapper)
  .build();

With that mapper in place, a row type like this works as expected:

record Product(
  UUID productId,
  Map<Locale, String> nameTranslations
) {}

Optional<Product> product = database.query("""
  SELECT product_id, name_translations
  FROM product
  WHERE product_id = :productId
  """)
  .bind("productId", UUID.fromString("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"))
  .fetchObject(Product.class);

If you also need to write the same type, pair this mapper with a matching CustomParameterBinder as shown in the Parameterized-Type Example in the Parameter Binding docs.

Previous
Parameter Binding
Next
Error Handling