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;

Using Oracle tkprof with JDBC thin client application

When attempting to profile a Hibernate based application, I found a statement that was incredibly slow and caused the system to basically halt for a few seconds before resuming execution. I wanted to profile it at the database level, and the best tool for the job is Oracle’s own tkprof.
The input for tkprof is a session trace file, and enabling one is a bit tricky. The reason – a JavaEE application, with multiple threads, have multiple connections, multiple database sessions, and the SQL_TRACE is on a per session level (I didn’t want to configure it for the entire database – the trace file would be totally unusable…)
So, I took the code and ran it in a standalone Java application, and enabled SQL Trace. Here’s how:

package com.tona.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import oracle.jdbc.OracleCallableStatement;

public class TestJDBC {
	public static void main(String[] args) throws Exception {
		Connection c = DriverManager.getConnection(JDBC_CONNECTION_STRING);
		
                // Set SQL_Trace to on
		Statement stmt = c.createStatement();
		stmt.executeUpdate("ALTER SESSION SET SQL_TRACE=TRUE");
		stmt.close();
		
                // Set SQL Trace location
		stmt = c.createStatement();
		stmt.executeUpdate("alter system set USER_DUMP_DEST='/tmp'");
		stmt.close();

		// JDBC logic comes here...

		c.close();
	}
}

Note
Changing the USER_DUMP_DEST parameter did not have any effect, and the SQL_TRACE was written to the default trace log directory on the server (in my case it was ./u01/app/oracle/diag/rdbms/SID/SID/trace)