Convert java.util.List<String> into java.sql.Array.

Question

How would you convert a java.util.List<String> instance into a java.sql.Array?

Answer 1

Use connection.createArrayOf(...)

For example:

String[] data = yourList.toArray(new String[yourList.size()];
connection.createArrayOf(typeName, data);

Where typeName is:

the SQL name of the type the elements of the array map to. The typeName is a database-specific name which may be the name of a built-in type, a user-defined type or a standard SQL type supported by this database. This is the value returned by Array.getBaseTypeName

---

As noted in the comments, this is Java 1.6. For older versions you can't create this in a driver-independent way. You are only supposed to get arrays, not create them. If you want to, you can instantiate the implementing class from your jdbc driver, but this is non-portable.

Answer 2

Thanks Bozho for the quick response. Basically, I am in the process of calling a Postgres Stored Procedure from Java 1.6 code.

The calling code is as follows

List selGr; CallableStatement proc = conn.prepareCall("{call sp_get_survivors_los(?,?,?)}"); proc.setDate(1, fDate); proc.setDate(2, tDate); java.sql.Array arr = conn.createArrayOf("VARIADIC character varying[]", selGr.toArray()); proc.setObject(3, arr); proc.registerOutParameter(1, Types.OTHER); proc.execute();

following is the SP

CREATE OR REPLACE FUNCTION sp_get_xxx(IN startdatetime timestamp without time zone, IN enddatetime timestamp without time zone, VARIADIC selectcriteria character varying[]) RETURNS refcursor AS

When I execute the Java code, I am getting the following exception. org.postgresql.util.PSQLException: Unable to find server array type for provided name VARIADIC character varying[]. at org.postgresql.jdbc4.AbstractJdbc4Connection.createArrayOf(AbstractJdbc4Connection.java:67) at org.postgresql.jdbc4.Jdbc4Connection.createArrayOf(Jdbc4Connection.java:21)

Any thoughts on this?

Answer 3

The type argument to createArrayOf is the element type, not the array type, so you probably want something like "varchar" or "text". VARIADIC is a function argument modifier, not a type specifier.