Better Array Support in H2 1.4.194

2017 March 11

The H2 database has had support for SQL ARRAYs for quite some time. However arrays were historically hard to use in H2 because key JDBC methods were not implemented. This changes with version 1.4.194. H2 now implements the following JDBC methods:

Connection#createArrayOf
PreparedStatement#setArray
PreparedStatement#setObject with an java.sql.Array instance

Note that ResultSet#getArray(int) and ResultSet#getArray(String) have been supported for a long time.

When working with arrays in H2 it is worth keeping a few things in mind. Arrays in H2 can be heterogeneous, multi dimensional and contain NULL. As a consequence the implementation currently has the following idiosyncrasies regarding the JDBI API:

Array#getBaseType always returns Types#NULL.
Array#getBaseTypeName always returns "NULL".
The metadata on the ResultSet returned by Array#getResultSet is not very helpful.

If you’re using Maven the coordinates are:

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.194</version>
</dependency>