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.

Calling planCachingEnabled(false) disables mapping-plan caching without changing conversion behavior. Caching never retains row values or live JDBC resources. Custom mappers run in registration order for each non-null value; a handled-null result is distinct from fallback.

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.with(dataSource, DatabaseType.POSTGRESQL)
  .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.with(dataSource, DatabaseType.POSTGRESQL)
  .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);

Large Object Values

The default mapper converts SQL CLOB and NCLOB values to String, and BLOB values to byte[], for both scalar results and matching bean properties or record components. Materialized values are detached from the JDBC cursor. When aliases map one source LOB to multiple compatible properties, Pyranid reads it once and shares the detached value.

public record Document(String body, byte[] attachment) {}

Document document = database.query("SELECT body, attachment FROM document WHERE id = :id")
  .bind("id", 123)
  .fetchObject(Document.class)
  .orElseThrow();

Built-in materialization is limited to 16 MiB per binary LOB and 8,388,608 UTF-16 code units per character LOB by default. Exceeding a limit raises DatabaseException. Configure larger limits when your application needs to read larger values:

ResultSetMapper mapper = ResultSetMapper.withPlanCachingEnabled(true)
  .maxLobBytes(64L * 1024 * 1024)
  .maxLobCharacters(32L * 1024 * 1024)
  .build();

Database database = Database.with(dataSource, DatabaseType.POSTGRESQL)
  .resultSetMapper(mapper)
  .build();

Both maxLobBytes(...) and maxLobCharacters(...) require a positive Long. The unboundedLobMaterialization() method explicitly disables both limits; a later finite setter re-enables its own limit independently. Java array/string size limits still apply. These limits cover Pyranid's built-in LOB materialization, including supported nested typed values. They do not limit ordinary VARCHAR or binary columns, driver buffering, custom mapper allocations, or the combined size of a result list. Already detached driver values are checked before acceptance, but the driver has already allocated their storage.

Other scalar LOB conversions retain the JDBC driver's getter behavior, such as DuckDB BLOB-to-String. These cross-type driver conversions are outside the built-in BLOB-to-byte[] and CLOB/NCLOB-to-String limits.

Custom column mappers run before built-in LOB conversion and may handle the value themselves, return a mapped null, or request fallback. Pyranid closes readers and streams it opens and frees LOB handles it exclusively owns after all row bindings finish. A handle exposed to a custom mapper is treated as borrowed, even when that mapper returns fallback, because the mapper could retain it. Raw Object, map, and JDBC-handle results retain their native/dialect behavior and cursor-lifetime constraints; do not assume a raw handle remains usable after the statement closes. A query stream does not extend the lifetime of a raw handle beyond its cursor.

LOB handles returned by custom mappers, including nested mappings, remain borrowed. Copy their contents into detached values when results must outlive the cursor. Custom Iterable and Map outputs are left untouched; keep any JDBC handles they contain within the cursor's lifetime. Do not rely on Pyranid to discover or free handles hidden inside application objects.

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.

After that one-match minimum, matching is otherwise partial: Pyranid does not require every writable JavaBean property or Record component to have a selected column. Pyranid supplies null for an unmatched reference-type Record component; an unmatched primitive Record component raises a DatabaseException. For an unmatched JavaBean property, Pyranid does not invoke its setter, so the instance supplied by the InstanceProvider keeps its current value, including constructor or field-initializer values and primitive defaults such as 0 or false.

An omitted column differs from a selected column containing SQL NULL. A selected NULL is mapped as null for reference targets - including invoking a JavaBean setter with null - and raises a DatabaseException for a primitive target. Prefer a projection type whose members match the selected columns, or provide a custom ResultSetMapper, when your application requires every target member to be populated.

The default InstanceProvider uses public constructors and the standard mapper uses public JavaBean accessors. Define mapped beans, records, constructors, getters, and setters as public. Your own 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(...).

Zoned timestamps mapped to LocalDate or java.sql.Date use the configured database time zone to choose the calendar date, consistently for scalar, record, and JavaBean results.

SQLite TIMESTAMP text is parsed strictly and retains fractional seconds when the driver uses its default JDBC date string format. With that default configuration, explicit offsets are preserved for OffsetDateTime, and Instant preserves the instant they identify. Local date/time targets use the configured database time zone; text without an offset is interpreted in that zone. Explicit custom SQLite JDBC date string formats retain JDBC driver conversion semantics, including their validation and precision limits. Numeric timestamp storage also retains JDBC driver semantics.

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

Typed temporal ARRAY elements follow the same time-zone rules as scalar columns when the driver supplies their SQL type. Pyranid uses Array::getBaseType() and Array::getBaseTypeName() to distinguish zone-less TIMESTAMP values from TIMESTAMP WITH TIME ZONE, including PostgreSQL TIMESTAMPTZ; nested arrays preserve that information. SQL DATE elements map through their calendar date, including when the destination is Instant, and timestamp nanoseconds are retained. Raw Object, List, and Set targets keep native element representations.

SQL TIME arrays mapped to LocalTime[], typed List<LocalTime> or Set<LocalTime> properties, or nested arrays preserve the fractional seconds supplied by the driver's typed reads. For drivers that expose array contents as legacy java.sql.Time[], Pyranid reads typed LocalTime elements before freeing the JDBC array. This also preserves the driver's end-of-day representation: a database value such as 24:00:00 cannot be represented directly by LocalTime, so Pyranid retains the driver's typed value (for example, LocalTime.MAX) instead of deriving midnight from the legacy value. If the driver cannot provide the required typed read, built-in temporal mapping raises DatabaseException rather than silently losing precision or changing that meaning.

PostgreSQL TIMETZ scalars and arrays preserve the original offset and fractional seconds when mapped to OffsetTime, including nested arrays. For drivers that expose zoned time arrays as legacy java.sql.Time[], Pyranid reads typed array elements before freeing the JDBC array. If the driver cannot supply a lossless typed read, built-in temporal mapping fails instead of inventing an offset; native results and custom mapper inputs retain their driver representations.

Mapping TIMETZ to LocalTime keeps its wall-clock time and fractional seconds while dropping the offset. This rule is the same for scalar results, record and JavaBean properties, and typed array elements.

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 supported PostgreSQL JDBC drivers as PGobject, and DuckDB JSON values returned as driver nodes, 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.

Text-backed vector columns such as pgvector, which supported PostgreSQL JDBC drivers surface as literals like [0.1,0.2,0.3], map to float[] and double[] targets by parsing that literal. DuckDB fixed-size vector ARRAYs use Pyranid's SQL ARRAY conversion path. See Vector in the Parameter Binding docs for the bind side.

SQL STRUCT Results

On databases whose dialects surface SQL STRUCT columns as attribute-name-to-value maps - currently DuckDB - STRUCT values map onto Java records, recursively. Attribute names follow the same matching rules as result columns: camelCase components match snake_case attributes, and @DatabaseColumn aliases apply.

Typed DuckDB STRUCT timestamp attributes follow the scalar time-zone rules: zone-less timestamps use the configured database time zone, while timezone-bearing timestamps preserve their instant. This applies to nested records and records inside arrays; raw Map and Object attributes retain native values.

CREATE TABLE employee (
  employee_id BIGINT PRIMARY KEY,
  person STRUCT(name VARCHAR, email_address VARCHAR),
  badges STRUCT(label VARCHAR, level INTEGER)[]
);

public record Person(String name, String emailAddress) {}
public record Badge(String label, Integer level) {}
public record Employee(Long employeeId, Person person, List<Badge> badges) {}

// STRUCT columns become record components; STRUCT lists become record lists
List<Employee> employees = database.query("SELECT * FROM employee")
  .fetchList(Employee.class);

// A single-column STRUCT result maps directly to a record target...
Optional<Person> person = database.query("SELECT person FROM employee WHERE employee_id=:id")
  .bind("id", 1L)
  .fetchObject(Person.class);

// ...including STRUCT projections built in SQL (note the space after ':' - see SQL Parsing Rules)
Optional<Person> projected = database.query("""
  SELECT {'name': name, 'email_address': email_address} AS person
  FROM legacy_employee
  WHERE employee_id=:id
  """)
  .bind("id", 1L)
  .fetchObject(Person.class);

Nested STRUCTs map to nested records to any depth, and a record component typed Map<String, Object> receives the attribute map itself. Map row targets surface STRUCT values as plain insertion-ordered maps with no driver-specific types, including elements of top-level STRUCT/JSON list columns. Ordinary by-name row mapping always takes precedence: the single-column direct mapping applies only when no result column matches a record component. A NULL STRUCT maps to a null component (or an empty Optional for single-column fetches). Top-level row records - the single-column direct mapping included - are constructed via the configured InstanceProvider; nested records are constructed via their canonical constructors, and CustomColumnMappers are not consulted for individual STRUCT attributes.

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.with(dataSource, DatabaseType.POSTGRESQL)
  .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.with(dataSource, DatabaseType.POSTGRESQL)
  .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. They can also be overridden for a single query via Query::resultSetMapper(...) and Query::preparedStatementBinder(...); the same contracts apply per query.

These SPIs declare generic methods. Implement map(...) in an anonymous class to handle an ad-hoc projection (a join, computed columns, a tuple) for one query:

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

List<NameCount> counts = database.query("""
  SELECT name, COUNT(*) AS total
  FROM employee
  GROUP BY name
  """)
  .resultSetMapper(new ResultSetMapper() {
    @Override
    public <T> Optional<T> map(
      StatementContext<T> ctx, ResultSet rs, Class<T> type,
      InstanceProvider ip
    ) throws SQLException {
      return 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
Notifications