Pyranid Logo

Core Concepts

ResultSet Mapping

When you execute a SQL query, Pyranid will walk each row 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
  public <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.

Return Optional.empty() to represent a mapped SQL NULL; a mapper itself must not return null.

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
  public <T> Optional<T> map(
    @NonNull StatementContext<T> statementContext,
    @NonNull ResultSet resultSet,
    @NonNull Class<T> resultSetRowType,
    @NonNull InstanceProvider instanceProvider
  ) throws SQLException {
    // Do your mapping here
    return Optional.empty();
  }
};

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.

The default InstanceProvider uses public constructors and the standard mapper uses public JavaBean accessors. Define mapped beans, records, constructors, getters, and setters as public. An application-owned InstanceProvider can instantiate an intentionally non-public record. Mapping a non-public JavaBean generally requires a custom ResultSetMapper, because the standard mapper invokes the bean's setters itself.

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

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

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

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

  public UUID getSystemToken() { return this.systemToken; }
  public 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();

Rows As Maps

When you don't want to define a type at all - exploratory queries, admin tooling, dynamic-shape SELECT *, exports - fetch rows as maps with Query::mapRowType():

List<Map<String, Object>> rows = database.query("SELECT * FROM car")
  .fetchList(Query.mapRowType());
Object make = rows.get(0).get("make");

Optional<Map<String, Object>> row = database.query("""
  SELECT *
  FROM car
  WHERE id = :id
  """)
  .bind("id", 123)
  .fetchObject(Query.mapRowType());

Each row is an insertion-ordered LinkedHashMap in column order. Keys are normalized (lowercase) column labels, so lookups behave identically across databases regardless of whether the driver reports unquoted labels uppercased (Oracle, HSQLDB), lowercased (PostgreSQL), or as written (MySQL, SQL Server). AS aliases are respected (and lowercased); NULL columns are present with a null value; values use the same dialect-aware extraction as record/bean mapping. Duplicate column labels - e.g. an unaliased multi-table join - fail fast with a DatabaseException; use column aliases to disambiguate.

The raw Map.class and LinkedHashMap.class tokens are also accepted anywhere a result type token is accepted, but Java's type erasure means they produce raw result types:

List<Map> rows = database.query("SELECT * FROM car").fetchList(Map.class);

The well-known JDK map tokens (HashMap.class, TreeMap.class, Hashtable.class, ConcurrentHashMap.class, and the SortedMap/NavigableMap/ConcurrentMap interfaces) are rejected with a clear error; other classes that happen to implement Map continue to map via the JavaBean path.

The special case lives in the default ResultSetMapper implementation only - a custom database-wide or per-query ResultSetMapper receiving the token decides for itself how to handle it.

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.

Vector columns (e.g. pgvector), which drivers surface as text such as [0.1,0.2,0.3], map to float[] and double[] targets - Pyranid parses the vector literal. See Vector in the Parameter Binding docs for the bind side.

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 
public 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.get(0);
// 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:

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

Per-Query Mapping and Binding

The ResultSetMapper and PreparedStatementBinder SPIs are normally configured database-wide at build time. As of 4.5.0 they can also be overridden for a single query via Query::resultSetMapper(...) and Query::preparedStatementBinder(...) - no new concepts, the same contracts applied per query.

This is the idiomatic way to inline-map an ad-hoc projection (a join, computed columns, a tuple) without defining a database-wide mapper - both SPIs are functional interfaces, so a lambda works:

public record NameCount(String name, Long total) {}

List<NameCount> counts = database.query("""
  SELECT name, COUNT(*) AS total
  FROM employee
  GROUP BY name
  """)
  .resultSetMapper((ctx, rs, type, ip) ->
    Optional.of(type.cast(new NameCount(rs.getString(1), rs.getLong(2)))))
  .fetchList(NameCount.class);

The override applies to every row this query maps - including Query::fetchStream(...) rows and DML-returning results - and to every parameter this query binds, including expanded IN-list elements and each batch group. Other queries on the same Database are unaffected; passing null restores the database-wide instance. Metrics, statement logging, and exception diagnostics behave identically with an override present.

Custom binders receive bound-ready raw values: SecureParameter and Optional wrappers are unwrapped by Pyranid before the binder is invoked, and null parameters never reach the binder (Pyranid binds them via PreparedStatement::setNull(...) even when an override is present).

Previous
Parameter Binding
Next
Error Handling