For a side project I’m doing I needed to use multi-dimension arrays in PostgreSQL using JDBC. There were no clear examples online on how to do this – and some forum posts claimed it wasn’t doable, so I wrote this short JUnit class to test the functionality. Needless to say – it works…
Some points – JDBC spec recommends you call the Array.free() method after using the array, but in the PostgreSQL driver version I was using (9.0 build 801) this was not supported.
public class TestMultiDimensionalArray {
public Connection getConnection() throws Exception {
Class.forName("org.postgresql.Driver");
Connection c = DriverManager.getConnection(
"jdbc:postgresql://localhost/engine", "user", "pass");
return c;
}
@Test
public void testCallableMultiDimensionRetValue() throws Exception {
Connection c = getConnection();
CallableStatement stmt = c.prepareCall("select * from select_schedules()");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Array outputArray = rs.getArray(1);
String[][] realArray = (String[][])outputArray.getArray();
System.out.println(realArray.length + "-->" + Arrays.toString(realArray[0]));
}
stmt.close();
c.close();
}
@Test
public void testCallableMultiDimensionInOutParams() throws Exception {
Connection c = getConnection();
CallableStatement stmt = c.prepareCall("{ call select_schedules_params(?,?)}");
String[][] elements = new String[2][];
elements[0] = new String[] {"meeting_m","lunch_m"};
elements[1] = new String[] {"training_m","presentation_m"};
Array inArray = c.createArrayOf("text", elements);
stmt.setArray(1, inArray);
stmt.registerOutParameter (2, java.sql.Types.ARRAY);
stmt.execute();
Array outputArray = stmt.getArray(2);
Assert.assertNotNull(outputArray);
String[][] realArray = (String[][])outputArray.getArray();
Assert.assertEquals(2, realArray.length);
stmt.close();
c.close();
}
@Test
public void testInsertSingleDimension() throws Exception {
Connection c = getConnection();
PreparedStatement stmt = c.prepareStatement("INSERT INTO sal_emp VALUES ('Bill',?,'{{"meeting", "lunch"}, {"training", "presentation"}}');");
Array myArray = c.createArrayOf("integer", new Integer[] {1000,1000,1000,1000});
stmt.setArray(1, myArray);
stmt.execute();
stmt.close();
c.close();
}
@Test
public void testInsertMultiDimension() throws Exception {
Connection c = getConnection();
PreparedStatement stmt = c.prepareStatement("INSERT INTO sal_emp VALUES ('multi_Bill',?,?);");
Array intArray = c.createArrayOf("integer", new Integer[] {1000,1000,1000,1000});
String[][] elements = new String[2][];
elements[0] = new String[] {"meeting_m","lunch_m"};
elements[1] = new String[] {"training_m","presentation_m"};
//Note - although this is a multi-dimensional array, we still supply the base element of the array
Array multiArray = c.createArrayOf("text", elements);
stmt.setArray(1, intArray);
stmt.setArray(2, multiArray);
stmt.execute();
//Note - free is not implemented
// myArray.free();
stmt.close();
c.close();
}
@Test
public void testSelectSingleDimension() throws Exception {
Connection c = getConnection();
Statement stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("SELECT 1 || ARRAY[2,3] AS array;");
if (rs.next()) {
Array outputArray = rs.getArray(1);
Integer[] intArray = (Integer[]) outputArray.getArray();
Assert.assertEquals(3, intArray.length);
Assert.assertEquals(intArray[0].intValue(), 1);
Assert.assertEquals(intArray[1].intValue(), 2);
Assert.assertEquals(intArray[2].intValue(), 3);
} else {
Assert.fail("Didn't get array results");
}
rs.close();
stmt.close();
c.close();
}
@Test
public void testSelectMultiDimension() throws Exception {
Connection c = getConnection();
Statement stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array");
if (rs.next()) {
Array outputArray = rs.getArray(1);
Integer[][] intArray = (Integer[][]) outputArray.getArray();
Assert.assertEquals(2, intArray.length);
Assert.assertEquals(1, (int) intArray[0][0]);
Assert.assertEquals(2, (int) intArray[0][1]);
Assert.assertEquals(3, (int) intArray[1][0]);
Assert.assertEquals(4, (int) intArray[1][1]);
} else {
Assert.fail("Didn't get array results");
}
c.close();
}
}
Initial SQL configuration was:
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
CREATE OR REPLACE FUNCTION select_schedules() RETURNS setof sal_emp.schedule%TYPE AS $$
DECLARE
row sal_emp.schedule%TYPE;
BEGIN
return query select schedule from sal_emp;
return;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION select_schedules_params(query text[][],OUT data text[][]) AS $$
DECLARE
row sal_emp.schedule%TYPE;
BEGIN
select schedule into data from sal_emp where schedule[1][1]=query[1][1];
END
$$ LANGUAGE plpgsql;
Tona Consulting 