Ah, that type conversion error is a classic gotcha with HSQLDB's array handling—let's break down what's going wrong and how to fix it:

Root Cause #

The incompatible data type in conversion error tells us the array you're passing via setArray() doesn't match the type HSQLDB expects for your array1 column. HSQLDB is strict about type alignment between JDBC parameters and table schema, especially for complex types like arrays.

Fix 1: Use Standard JDBC Array Creation (Recommended) #

Your custom JDBCArrayBasic class might be misconfiguring the array type. Instead of rolling your own implementation, use the standard JDBC Connection.createArrayOf() method—it handles HSQLDB's type requirements out of the box:

private static ParameterizedPreparedStatementSetter<Data> preparedStatementSetter() {
    return (preparedStatement, data) -> {
        // The first argument is the ELEMENT type of your array (VARCHAR(256)), not the array type itself
        java.sql.Array array = preparedStatement.getConnection()
            .createArrayOf("VARCHAR(256)", data.getY().toArray());
        preparedStatement.setArray(1, array);
        preparedStatement.setString(2, data.getX());
    };
}

This ensures the array you create matches exactly what HSQLDB expects for the array1 column.

Fix 2: Align Your Table Definition (Optional but Clearer) #

While your original table syntax (VARCHAR(256) ARRAY) is valid, HSQLDB recommends using the more explicit ARRAY OF <type> syntax to avoid ambiguity:

CREATE TABLE Table1 (
    array1 ARRAY OF VARCHAR(256),
    obj1 VARCHAR(256)
);

Temporary Workaround (If You Need a Quick Test) #

If you still hit issues, you can use HSQLDB's array literal syntax as a temporary fix. Convert your array to a string formatted like ARRAY['val1', 'val2'] and pass it as a string parameter:

private static ParameterizedPreparedStatementSetter<Data> preparedStatementSetter() {
    return (preparedStatement, data) -> {
        // Important: Escape special characters (like quotes) in your strings for production use!
        String arrayLiteral = "ARRAY['" + String.join("','", data.getY()) + "']";
        preparedStatement.setString(1, arrayLiteral);
        preparedStatement.setString(2, data.getX());
    };
}

Note: This is a workaround, not a long-term solution—proper escaping is critical to avoid SQL injection or syntax errors.

Quick Sanity Checks #

• Ensure you're using HSQLDB 2.0 or later (older versions have limited support for array types).
• Double-check your database URL doesn't include conflicting syntax modes (like sql.syntax_mys=true) that might alter how arrays are parsed.

内容的提问来源于stack exchange，提问作者Henil Shah