SQL Arrays in H2

[Jon Blower]

Hi all,

I am having problems using the SQL ARRAY type in H2. I am trying to
store an array of doubles in an H2 database, but if I try to retrieve
the array, I get an Object[] array with a single String entry.

My table is created like this:
create table foo (
id identity not null primary key,
values array
);

I am inserting the array like this:
double[] values = new double[10];
// populate values array here...
PreparedStatement ps = conn.prepareStatement("insert into foo(values)
values(?)");
ps.setObject(1, values); // setArray() is not supported according to
H2 javadocs
ps.executeUpdate();

I am retrieving the array like this:
PreparedStatement ps = conn.prepareStatement("select values from foo
where id=?");
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
Array array = rs.getArray(1);
Object[] objArr = array.getArray(); // Always returns Object[]
according to H2 javadocs

Now objArr is always an array with a single String value, which
presumably contains all the values of the original array in some
encoded form. I get an equivalent result if I use
array.getResultSet().

Is it OK to use ps.setObject() to add an array to a prepared
statement? setArray() doesn't seem to be supported in H2. How can I
decode the String that is returned?

Thanks in advance,
Jon

[Thomas Mueller]

Hi,

Thanks for your feedback!

Instead of

double[] values = new double[10];

try:
Double[] values = new Double[] {new Double(1), new Double(2), new Double(3) };

double[] is currently serialized (using the standard Java object
serialization). I will try to support this for the next release; if it
is not as easy as I thought I will add a feature request. The same
with supporting setArray.

Regards,
Thomas

[Jon Blower]

Hi Thomas,

Great, thanks, I'll try that. What are the pros and cons of storing a
double[] array (or a Double[] array) as an Array versus storing as an
object (using the "other" data type)? Which is more suitable for
large arrays of, say, 10000 elements? I am interested in both the
speed of retrieval of the array (e.g. deserialization) and the speed
of finding a row in my table based on the array values, i.e:

create table foo (
id identity not null primary key,
values array
);

select id from foo where values=(an,array,of,values)

I presume that the above select statement will internally use
Object.equals() or similar so I guess the speed of the statement
depends on how efficient the equals() method is? Is this likely to be
different for a Double[] array (stored as an Array type) and an array
that is stored as a serialized object?

Thanks, Jon

[Thomas Mueller]

Hi,

> Great, thanks, I'll try that. What are the pros and cons of storing a
> double[] array (or a Double[] array) as an Array versus storing as an
> object (using the "other" data type)?

There is no big difference. Using the ARRAY data type has the
advantage that you can easier view the data in tools like the H2
Console.

> Which is more suitable for
> large arrays of, say, 10000 elements?

I'm not fully sure, but I think that ARRAY needs less space (Java
serialization is not very space efficient).

> select id from foo where values=(an,array,of,values)

In this case you probably need to use ARRAY if you want to use
(an,array,of,values) as text in the SQL statement.

Regards,
Thomas