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
# 1 楼答案
我建议使用一个循环来填充您的PreparedSteement,到insert an array您必须使用例如:
因此,要填充数组,必须使用如下循环:
到目前为止,您的查询如下所示:
你可以这样完成:
因此,最终你的准备陈述如下所示:
希望这能给你一个想法