title: How to insert an array of UUIDs via JDBC's PreparedStatement into a HyperSQL database
body: <p>Given a table in <a href="http://hsqldb.org" rel="nofollow noreferrer">HyperSQL</a> with a column of an array of UUIDs, what is the proper way to construct an <code>INSERT</code> <code>PreparedStatement</code> to populate such a field?</p>
  
  <p>SSCCE:</p>
  
  <pre><code>public class Example{
      public static void main(String[] args) throws Exception{
          // Create UUID array
          UUID[] exmapleArray = new UUID[5];
  
          for(int i = 0; i &lt; 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();
              }
          }
      }
  }
  </code></pre>
  
  <p>Whilst populating a plain <code>UUID</code> can be done with <code>PreparedStatement.setString()</code> populating an array however does not seem to work, yielding several different exceptions depending on the choice of statement above, the most common being:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The latter two of the commented-out attempts uses HSQLDB-specific classes (specifically <code>JDBCArrayBasic</code>) which similarly yields <code>incompatible data type in conversion</code>.</p>