How to insert an array of UUIDs via JDBC's PreparedStatement into a HyperSQL database

Question

Given a table in HyperSQL with a column of an array of UUIDs, what is the proper way to construct an INSERT PreparedStatement to populate such a field?

SSCCE:

public class Example{
    public static void main(String[] args) throws Exception{
        // Create UUID array
        UUID[] exmapleArray = new UUID[5];

        for(int i = 0; i < exmapleArray.length; i++){
            exmapleArray[i] = UUID.randomUUID();
        }

        // Load database class
        Class.forName("org.hsqldb.jdbc.JDBCDriver");

        // Connect to database
        try(Connection databaseConnection = DriverManager.getConnection("jdbc:hsqldb:mem:foo")){
            try(Statement createStatement = databaseConnection.createStatement()){
                createStatement.executeUpdate("CREATE TABLE bar(test_field UUID ARRAY[10])");
            }

            try(PreparedStatement preparedStatement = databaseConnection.prepareStatement("INSERT INTO bar VALUES (?)")){
                // None of the below examples work

                // preparedStatement.setArray(1, databaseConnection.createArrayOf("UUID", exmapleArray));
                // preparedStatement.setArray(1, databaseConnection.createArrayOf("VARCHAR", exmapleArray));
                // preparedStatement.setArray(1, databaseConnection.createArrayOf("UUID", Arrays.stream(exmapleArray).map(UUID::toString).toArray()));
                // preparedStatement.setArray(1, databaseConnection.createArrayOf("VARCHAR", Arrays.stream(exmapleArray).map(UUID::toString).toArray()));

                // preparedStatement.setArray(1, new JDBCArrayBasic(exmapleArray, Type.BINARY_UUID));
                // preparedStatement.setArray(1, new JDBCArrayBasic(Arrays.stream(exmapleArray).map(UUID::toString).toArray(), Type.BINARY_UUID));

                preparedStatement.executeUpdate();
            }
        }
    }
}

Whilst populating a plain UUID can be done with PreparedStatement.setString() populating an array however does not seem to work, yielding several different exceptions depending on the choice of statement above, the most common being:

Exception in thread "main" java.sql.SQLSyntaxErrorException: incompatible data type in conversion
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.createArrayOf(Unknown Source)
    at Example.main(Example.java:31)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: org.hsqldb.HsqlException: incompatible data type in conversion
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.types.CharacterType.convertToDefaultType(Unknown Source)
    at org.hsqldb.types.CharacterType.convertJavaToSQL(Unknown Source)
    ... 7 more

The latter two of the commented-out attempts uses HSQLDB-specific classes (specifically JDBCArrayBasic) which similarly yields incompatible data type in conversion.

Answer 1

I suggest o use a loop to populate your PreparedStetement, to insert an array you have to use for example :

INSERT INTO bar VALUES ARRAY[3.45, 23.64, 14.01]

So to fill your array you have to use a loop like this :

String query = "INSERT INTO bar VALUES ARRAY[";
String del = "";
for (int i = 0; i < 10; i++) {
    query += del+"?" ;
    del = ",";
}
query += "]";
System.out.println(query);

your query until now look like this :

INSERT INTO bar VALUES ARRAY[?,?,?,?,?,?,?,?,?,?]

and you can complete like this :

PreparedStatement preparedStatement = connection.prepareStatement(query);
for (int i = 1; i <= 10; i++) {
    preparedStatement.setString(i, "valu_"+i);
}
System.out.println("Query = " + preparedStatement.toString());

so in the end your preparedStetement look like this for example :

INSERT INTO bar VALUES ARRAY['valu_1','valu_2','valu_3','valu_4','valu_5','valu_6','valu_7','valu_8','valu_9','valu_10']

Hope this can gives you an idea.