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;
I’ve checked and this also works with PostgreSQL 8.4 JDBC4 (build 701)
LikeLike
thanks…
LikeLike
Thanks, you were right about a lack of clear examples. Can confirm the above method of inserting arrays works on server 9.3 and driver 9.3-1100-jdbc41. Thanks for publishing, got me on the right track.
LikeLike
thanks alot. it works for me.
LikeLike
Thanks – worked for me too. postgresql-9.4-1202.jdbc4 driver
LikeLike