Postgres and multi-dimensions arrays in JDBC

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;