java - 如何通过JDBC的PreparedStatement将UUID数组插入HyperSQL数据库

给定HyperSQL中的一个表，其中包含一个UUID数组的列，构造INSERT PreparedStatement来填充此类字段的正确方法是什么？

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();
            }
        }
    }
}

虽然可以用UUID填充数组来完成普通的PreparedStatement.setString()的填充，但是似乎不起作用，取决于上面的语句选择，会产生几种不同的异常，最常见的是:

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

被注释掉的后两个尝试使用特定于HSQLDB的类(特别是JDBCArrayBasic)，该类类似地产生incompatible data type in conversion。

最佳答案

我建议o使用循环填充您的PreparedStetement，例如，您必须使用insert an array:

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

因此，要填充数组，您必须使用如下循环:

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

您的查询直到现在看起来像这样:

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

您可以像这样完成:

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

所以最后您的prepareStetement看起来像这样:

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']

希望这可以给您一个想法。