Consider adding array helper methods to KiwiJdbc in kiwi

[sleberknight]

I propose adding some methods to KiwiJdbc for dealing with arrays in JDBC ResultSets. Modifications for null handling and/or name changes should also be considered and implemented. Here is a method that accepts a ResultSet, the expected type of array elements, and the column name:

// Use this when you want to assign the result to a variable
public static <T> T[] getArray(ResultSet rs, Class<T> clazz, String columnName) {
    return getArray(rs, columnName);
}

// Example:
var dates = getArray(rs, Date.class, "report_dates");  // dates is a Date[]

// You can be explicit with types too...
Date[] dates2 = getArray(rs, Date.class, "report_dates");

And here is a method that does not take the expected type, which can only be used with call sites where the type can be inferred by the compiler:

// Use this when passing directly into a method (e.g. a setter or in a builder) since the type can be inferred
@SuppressWarnings("unchecked")
public static <T> T[] getArray(ResultSet rs, String columnName) {
    var array = rs.getArray(columnName).getArray();

    var clazz = array.getClass();
    checkState(clazz.isArray(),  "expected an array in column '%s' but found: %s", columnLabel, clazz);

    // thanks to type erasure, we can't easily verify it's a array of T...so just try to cast it
    // ...we could add a try/catch if we wanted

    return (T[]) array;
}

// Example, assuming reportDates is declared as Date[]:
Survey.builder()
    .name(rs.getString("name")
    .reportDates(getArray(rs, "report_dates"))
    .build()

We probably should rename them to be consistent with the existing methods in KiwiJdbc, e.g. maybe arrayOrNull and they would therefore need to accommodate possible null values, which the above example methods do not. See the existing methods in KiwiJdbc; it's just a simple check using ResultSet#wasNull. But, I've not tested this (since our existing use cases do not permit null values) so would need to see what actually happens.

Note also that the above only work for reference types. We would need to have specialized methods for primitives, e.g. doubleArrayOrNull, intArrayOrNull, and so on.

Alas, the methods would need to include some ugliness, for example (without null handling):

public static double[] doubleAray(ResultSet rs, String columnName) {
  // ResultSet#getArray returns Object
  var array = (Double[]) rs.getArray(columnName).getArray();
  return Arrays.stream(array).mapToDouble(Double::doubleValue).toArray();
}

We could also add some type validation (again without null handling):

public static double[] doubleAray(ResultSet rs, String columnName) {
  var array = rs.getArray(columnName).getArray();

  var clazz = array.getClass();
  checkState(clazz.isArray(), "expected an array in column '%s' but found: %s", columnLabel, clazz);

  checkState(array instance of Double[],
    "expected array of java.lang.Double in column '%s' but found array of: %s",
    columnLabel, clazz.getComponentType());

  var doubleObjArray = (Double[]) array;
  return Arrays.stream(doubleObjArray).mapToDouble(Double::doubleValue).toArray();
}

[sleberknight]

Here are a few more helper methods that use the above getArray(ResultSet rs, String columnName) method:

public static <T> Set<T> arrayAsSet(ResultSet rs, String columnName) throws SQLException {
    T[] ts = getArray(rs, columnName);
    return Sets.newHashSet(ts);
}

public static <T> List<T> arrayAsList(ResultSet rs, String columnName) throws SQLException {
    T[] ts = getArray(rs, columnName);
    return Lists.newArrayList(ts);
}

The above return mutable collections. We could change them to use Set.of and List.of to get unmodifiable collections, or have separate methods, e.g. arrayAsUnmodifiableSet and arrayAsUnmodifiableList.

[sleberknight]

As a first step, these will be added to kiwi-beta. See Add JDBC array helper methods.

[sleberknight]

I am marking this discussed as Closed since it is now in kiwi-beta and may be moved to kiwi at some point.