Re: JDBC and arrays

On Fri, 2 Feb 2007, Miroslav Šulc wrote:

> I tried to find some info on how to work with arrays using PostgreSQL JDBC
> driver but didn't find anything useful except that I can implement
> java.sql.Array to get support for arrays. Can someone, please, send me a link
> on how to write and read array data using PostgreSQL JDBC driver or give me a
> brief explanation? I'm also interested how to work with inet[] arrays as they
> do not seem to be directly supported by JDBC API.

Reading arrays is pretty straightforward:

ResultSet rs = stmt.executeQuery("SELECT '{a,b}'::text[]");
rs.next();
Array arr = rs.getArray(1);
String s[] = (String)arr.getArray();

Writing arrays is not easy because prior to the JDBC4 spec there was no
way to create java.sql.Array objects without creating a class that
implements java.sql.Array yourself. Now JDBC4 offers
Connection.createArrayOf() which unfortunately we haven't implemented yet.
So while there is a light at the end of the tunnel you're still stuck
implementing java.sql.Array yourself. Check the mailing list for examples
of that.

Currently arrays of non-standard datatypes like inet are not supported.
Seems possible to do if you were willing to use PGobject for unknown
types, but no one has done it.

Also more complicated things like multi-dimensional arrays are note
supported.

Kris Jurka