The Arrow JDBC Adapter assists with working with JDBC and Arrow data. Currently, it supports reading JDBC ResultSets into Arrow VectorSchemaRoots.

This can be accessed via the JdbcToArrow class. The resulting ArrowVectorIterator will convert a ResultSet to Arrow data in batches of rows.

try (ArrowVectorIterator it = JdbcToArrow.sqlToArrowVectorIterator(resultSet, allocator)) {
  while (it.hasNext()) {
    VectorSchemaRoot root = it.next();
    // Consume the root…
  }
}

The batch size and type mapping can both be customized:

JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator, /*calendar=*/null)
    .setReuseVectorSchemaRoot(reuseVectorSchemaRoot)
    .setJdbcToArrowTypeConverter((jdbcFieldInfo -> {
      switch (jdbcFieldInfo.getJdbcType()) {
        case Types.BIGINT:
          // Assume actual value range is SMALLINT
          return new ArrowType.Int(16, true);
        default:
          return null;
      }
    }))
    .build();
try (ArrowVectorIterator iter = JdbcToArrow.sqlToArrowVectorIterator(rs, config)) {
  while (iter.hasNext()) {
    VectorSchemaRoot root = iter.next();
    // Consume the root…
  }
}

The JDBC type can be explicitly specified, which is useful since JDBC drivers can give spurious type information. For example, the Postgres driver has been observed to use Decimal types with scale and precision 0; these cases can be handled by specifying the type explicitly before reading. Also, some JDBC drivers may return BigDecimal values with inconsistent scale. A RoundingMode can be set to handle these cases:

Map<Integer, JdbcFieldInfo> mapping = new HashMap<>();
mapping.put(1, new JdbcFieldInfo(Types.DECIMAL, 20, 7));
JdbcToArrowConfig config = new JdbcToArrowConfigBuilder(allocator, /*calendar=*/null)
    .setBigDecimalRoundingMode(RoundingMode.UNNECESSARY)
    .setExplicitTypesByColumnIndex(mapping)
    .build();
try (ArrowVectorIterator iter = JdbcToArrow.sqlToArrowVectorIterator(rs, config)) {
  while (iter.hasNext()) {
    VectorSchemaRoot root = iter.next();
    // Consume the root…
  }
}

The mapping from JDBC type to Arrow type can be overridden via the JdbcToArrowConfig, but it is not possible to customize the conversion from JDBC value to Arrow value itself, nor is it possible to define a conversion for an unsupported type.

The JDBC to Arrow type mapping can be obtained at runtime from JdbcToArrowUtils.getArrowTypeFromJdbcType.

• (1) The list value type must be explicitly configured and cannot be inferred. Use setArraySubTypeByColumnIndexMap or setArraySubTypeByColumnNameMap.
• (2) By default, the scale of decimal values must match the scale in the type exactly; precision is allowed to be any value greater or equal to the type precision. If there is a mismatch, by default, an exception will be thrown. This can be configured by setting a different RoundingMode with setBigDecimalRoundingMode.
• (3) Not fully supported: while the type conversion is defined, the value conversion is not. See ARROW-17006.
• (4) If a Calendar is provided, then the timestamp will have the timezone of the calendar, else it will be a timestamp without timezone.

The adapter can bind rows of Arrow data from a VectorSchemaRoot to parameters of a JDBC PreparedStatement. This can be accessed via the JdbcParameterBinder class. Each call to next() will bind parameters from the next row of data, and then the application can execute the statement, call addBatch(), etc. as desired. Null values will lead to a setNull call with an appropriate JDBC type code (listed below).

final JdbcParameterBinder binder =
    JdbcParameterBinder.builder(statement, root).bindAll().build();
while (binder.next()) {
    statement.executeUpdate();
}
// Use a VectorLoader to update the root
binder.reset();
while (binder.next()) {
    statement.executeUpdate();
}

The mapping of vectors to parameters, the JDBC type code used by the converters, and the type conversions themselves can all be customized:

final JdbcParameterBinder binder =
    JdbcParameterBinder.builder(statement, root)
        .bind(/*parameterIndex*/2, /*columnIndex*/0)
        .bind(/*parameterIndex*/1, customColumnBinderInstance)
        .build();

The Arrow to JDBC type mapping can be obtained at runtime via a method on ColumnBinder.

• (1) Strings longer than Integer.MAX_VALUE bytes (the maximum length of a Java byte[]) will cause a runtime exception.
• (2) If the timestamp has a timezone, the JDBC type defaults to TIMESTAMP_WITH_TIMEZONE. If the timestamp has no timezone, technically there is not a correct conversion from Arrow value to JDBC value, because a JDBC Timestamp is in UTC, and we have no timezone information. In this case, the default binder will call setTimestamp(int, Timestamp), which will lead to the driver using the "default timezone" (that of the Java VM).