Hsqldb reads ARRAY type as Object[], not String[]

Not Answer! Just recommendation!

You will always face compatibility issues between HSQLDB(or H2) and real DB (MySQL, PostgreSQL). I use https://www.testcontainers.org/modules/databases/ . It is easy to use it. It's necessary just change connection url and JDBC driver. So, testcontainer starts up container with real DB for integration tests and destroys it after the tests.

Look at getArray with a mapping from SQL type to java class.

String[] getStringArray(ResultSet rs, int column) {
    Array array = rs.getArray(column);

    String sqlType = rs.getMetaData().getColumnTypeName(column);
    logger.info("SQL type: " + sqlType); // VARCHAR ARRAY?

    Map<String, Class<?>> columnTypes = new TreeMap<>();
    columnTypes.put("VARCHAR", String.class);
    columnTypes.put(sqlType, String.class);

    Object a = array.get(columnTypes);
    if (a instanceOf String[]) {
        logger.info("Probably VARCHAR worked");
        return (String[])a;
    }
    logger.info("The above did not help, use only the following.");
    return Stream.of((Object[] a).map(String.class::cast).toArray();
}

It is not clear whether HSQLDB provides this support, as it did not out-of-the-box.

HSQLDB always returns an Object[] from the resultSet.getArray(columnName).getArray() call.

Is there a way to tweak the way HSQLDB maps columns to Java types? I can't for find it, for the life of me.

You can modify the source of org.hsqldb.jdbc.JDBCArray to return a Sring[] when the object type is a character type.

You can do

String[] values = new ArrayList();
while (rs.next()) {
values.add(rs.getString("column_name");
}
return values.toArray(new String[list.size()]);