Pyranid Logo

Core Concepts

ResultSet Mapping

When you execute a SQL query, Pyranid will walk each row in the in the javax.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);
}

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.

Out of the box, Pyranid is configured to use its own DefaultResultSetMapper, which is normally what you want. Its behavior is outlined below in Default Mapping Behavior. If you have special types that can't easily be constructed mechanically, see Custom Mapping, which describes how to have full control over instantiation and mapping.

Default Mapping Behavior

The DefaultResultSetMapper supports user-defined types that follow the JavaBean getter/setter conventions, primitives, and some additional common JDK types.

Record types are also supported.

User-defined Types

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

Column names are assumed to be in snake_case and will be mapped automatically to their camelCase equivalents. 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();

References:

Supported Primitives

  • Byte
  • Short
  • Integer
  • Long
  • Float
  • Double
  • Boolean
  • Char
  • 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
  • ZoneId
  • TimeZone
  • Locale (IETF BCP 47 "language tag" format, e.g. en-US or pt-BR)

Other Types

Kotlin Types

Kotlin data class result set 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())

Custom Mapping

To have Pyranid use a custom ResultSetMapper, provide an instance when you create your Database.

Why might you do this?

One reason is for mapping to types that have special construction rules, e.g a custom constructor, Factory, or Builder.

ZoneId timeZone = ZoneId.of("UTC");
DataSource dataSource = obtainDataSource();

// Generally, you'll want to subclass or otherwise reuse 
// DefaultResultSetMapper so you can handle one-off cases 
// specially and fall back to default behavior otherwise.
ResultSetMapper resultSetMapper = new DefaultResultSetMapper(timeZone) {
  @Nonnull
  @Override
  public <T> Optional<T> map(@Nonnull StatementContext<T> statementContext,
                             @Nonnull ResultSet resultSet,
                             @Nonnull Class<T> resultSetRowType,
                             @Nonnull InstanceProvider instanceProvider) {
    // MySpecialType requires a custom Builder
    if(resultSetRowType instanceof MySpecialType) {
      try {
        return (T) MySpecialType.builder()
          .name(resultSet.getString("name"))
          .rewardsPoints(resultSet.getLong("points"))
          .build();
      } catch(SQLException e) {
        throw new DatabaseException(e);
      }            
    } else {
      // Other types get the default behavior
      return super.map(statementContext, resultSet, 
        resultSetRowType, instanceProvider);
    }
  }
};

Database customDatabase = Database.forDataSource(dataSource)
  .timeZone(timeZone)
  .resultSetMapper(resultSetMapper)
  .build();
Previous
Transactions
Next
Error Handling