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

Question

I have a table in an in-memory HSQLDB database for integration test, with an ARRAY column (categories VARCHAR(256) ARRAY NOT NULL), notice it's defined as VARCHAR array.

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.

When the array column is read (with org.hsqldb.jdbc.JDBCDriver), resultSet.getArray(columnName).getArray() returns an Object[], and not a String[].

This results in cast exception, since the calling code (that I'm not controlling) expects a String[]. resultSet.getArray(columnName) returns org.hsqldb.jdbc.JDBCArray, and ideally I would like it to return PostgreSQLTextArray so I can end up with a String[] (that's what's used in prod).

• HSQLDB: "org.hsqldb:hsqldb:2.4.0"
• Java: 1.8.131

Adios

Can you check the actual type that .getArray() is returning? Use getArray()[0].getClass().getName(). – Springlet

Answer 1

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.

Bookmark

Thanks! Maybe we should. But then all developers have to have Docker installed where they want to run these tests, right? – Adios

Yes, anyway, docker is mainstream )) – Bookmark

Answer 2

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.

Fattish

Could work, but I don't control how the ResultSet is used. – Adios

H2 does much on being capable to run in dialects of several db vendors. Sorry. – Fattish

Answer 3

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.

Emphysema

Answer 4

You can do

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

Wei

getString() returns a String not an array – Extramundane

the while loops through the array – Wei

The while loops over the rows from the ResultSet, not over the array elements of the column. – Extramundane