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.
// 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 or 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.queryForObject("SELECT COUNT(*) FROM car", Long.class).get();

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

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

User-defined Types

In the case of user-defined types and Records, 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.

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

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

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

Supported Primitives

  • Byte
  • Short
  • Integer
  • Long
  • Float
  • Double
  • Boolean
  • Character
  • String
  • byte[]

Supported JDK Types

  • Enum<E>
  • UUID
  • BigDecimal
  • BigInteger
  • Date
  • Instant
  • LocalDate for DATE
  • LocalTime for TIME
  • LocalDateTime for TIMESTAMP
  • OffsetTime for TIME WITH TIMEZONE
  • OffsetDateTime for TIMESTAMP WITH TIMEZONE
  • java.sql.Timestamp
  • java.sql.Date
  • ZoneId
  • TimeZone
  • Locale (IETF BCP 47 "language tag" format)
  • Currency

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:

// 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 indicate "I don't want to do custom mapping"
    // and Pyranid will fall back to the registered ResultSetMapper's 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.queryForList("SELECT * FROM row", 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.queryForObject("SELECT my_special_type FROM row LIMIT 1", MySpecialType.class);

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

Kotlin Types

Kotlin data class ResultSet mapping is possible through the primary constructor of the data class.

  • Nullable and non-null columns are supported.
  • Default parameters are supported.
  • Data classes support the same list of JDK types as above
  • Extension functions for direct KClass support are provided
data class Car(carId: UUID, color: Color = Color.BLUE, ownerId: String?)

val cars = database.queryForList("SELECT * FROM cars", Car::class)

When query parameters are supplied as a list they must be flattened first, either as separate lists or one big list:

val cars = database.queryForList(
  "SELECT * FROM cars WHERE car_id IN (?,?) LIMIT ?",
  Car::class, car1Id, car2Id, 10)

val cars = database.queryForList(
  "SELECT * FROM cars WHERE car_id IN (?,?) LIMIT ?",
  Car::class, *listOf(car1Id, car2Id).toTypedArray(), 10)

val cars = database.queryForList(
  "SELECT * FROM cars WHERE car_id IN (?,?) LIMIT ?",
  Car::class, *listOf(car1Id, car2Id, 10).toTypedArray())
Previous
Parameter Binding
Next
Error Handling