Skip to content
accelerando

Tag Archives: JDBC

How to retrieve tables of custom object types with JDBC

24-Jul-12

The following is not only a personal reminder for me but also a first try to answer some questions on Stack Overflow

You can create custom object types in an Oracle database like so:

CREATE TYPE t_demo_object AS OBJECT (
  some_number  NUMBER,
  some_string  varchar2(32)
)
/

Those types can have member functions, custom constructors and some pretty neat features inside oracle.

There also exists tables of them like so

CREATE TYPE t_demo_objects IS TABLE OF t_demo_object
/

They can be used to do stuff like pipelined functions.

Often those types are used as results of stored functions and procedures, probably like so:

CREATE OR REPLACE PROCEDURE p_generate_demo_objects(p_num IN NUMBER, p_data OUT t_demo_objects) AS 
BEGIN
	p_data := t_demo_objects();
	p_data.extend(p_num);
	FOR i IN 1..p_num LOOP
		p_data(i) := t_demo_object(i, 'Index ' || i);
	END LOOP;
END p_generate_demo_objects;

As long as this is used only within the database, everything is fine with Oracle Object Types… But often a procedure like this will be called from the outside through jdbc.

How to retrieve this object type? As an java.sql.Array of java.sql.Structs.

I’m doing it like so with minimal dependencies on the Oracle JDBC driver:

package tests.jdbc;
 
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Struct;
import java.sql.Types;
 
import oracle.sql.StructDescriptor;
 
public class OracleTableOfResult {
	public static void main(String...a) throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		Connection connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>");
 
		final String typeName = "T_DEMO_OBJECT";
		final String typeTableName = "T_DEMO_OBJECTS";
 
		// Get a description of your type (Oracle specific)
		final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);		
		final ResultSetMetaData metaData = structDescriptor.getMetaData();
 
		// Call the procedure (or whatever else) that returns the table of a custom type
		CallableStatement cs = connection.prepareCall("{call p_generate_demo_objects(?, ?)}");
		cs.setInt(1, 5);
		// Result is an java.sql.Array...
		cs.registerOutParameter(2, Types.ARRAY, typeTableName);		
		cs.execute();
 
		// ...who's elements are java.sql.Structs
		Object[] data = (Object[]) ((Array) cs.getObject(2)).getArray();
		for(Object tmp : data) {
			Struct row = (Struct) tmp;
			// Attributes are index 1 based...
			int idx = 1;
			for(Object attribute : row.getAttributes()) {				
				System.out.println(metaData.getColumnName(idx) + " = " + attribute);											
				++idx;
			}
			System.out.println("---");
		}
		cs.close();		
		connection.close();
	}
}

In the original question is also room for improving the pl/sql procedure.

You can fill the resulting object procedural with a loop but you can also use a “bulk collect into”:

SELECT t_demo_object(colum_a, column_b) bulk collect INTO p_data FROM some_table;

which should be considerable faster on large datasets.

Note that it is not possible to use package level array types of record types in JDBC:

Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types.

Accessing PL/SQL Index-by Tables

Sadly, we have a legacy application that makes extensive use of such types… We had to rewrite most of the procedures.

Oracle, JBDC, CallableStatements and named parameters

23-Jul-12

Update: Please take a note of comment #5 by DSurber. He works for Oracle and sheds some light on named parameters. In short: The names of the parameter do not refer to named placeholders in statement (like in JPA for example) but to PL/SQL named parameters!. Thank you very much.

You might have wondered what happened here:

Well easy, i had fun with the Oracle JDBC Driver and named parameters. I modified one call, added a parameter and set this parameter not in order… No problem, i thought, as i was using a callable statement with named parameter. As i it turns out, named parameters (“:blah”) are not so named, after all, but the order of setting them does matter, see the following tests:

package tests.jdbc;
 
import java.sql.*;
 
import org.junit.*;
 
public class OracleCallableStatementTest {
	protected static Connection connection;
 
	@BeforeClass
	public static void setup() throws Exception {	
		try{
			Class.forName("oracle.jdbc.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:<USER>/<PASS>@<DATABASEHOST>:1521:<SERVICE>");
			PreparedStatement ps = connection.prepareStatement("Create table callable_statement_test(column1 varchar(16), column2 varchar(16))");
			ps.executeUpdate();
			ps.close();
			ps = connection.prepareStatement(
					"CREATE OR REPLACE procedure p_callable_test (p1 IN VARCHAR, p2  IN  VARCHAR) AS " + 
							"BEGIN " + 
							"  INSERT INTO callable_statement_test(column1, column2) values(p1, p2); " + 
							"END;"
					);
			ps.executeUpdate();
			ps.close();
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		}		
	}
 
	@Before
	public void prepareTable() throws SQLException {
		Statement st = connection.createStatement();
		st.executeUpdate("Truncate table callable_statement_test");
		st.close();
	}
 
	private void verify() throws SQLException {
		final Statement st = connection.createStatement();
		ResultSet rs = st.executeQuery("Select * from callable_statement_test");
		rs.next();
 
		String column1 = rs.getString("column1");
		String column2 = rs.getString("column2");
 
		rs.close();
		st.close();		
 
		Assert.assertEquals("1", column1);
		Assert.assertEquals("2", column2);		
	}
 
	@Test
	public void testCallableStatementParameterInOrder() throws SQLException {		
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");		
		ps.setString("p1", "1");	
		ps.setString("p2", "2");					
		ps.execute();
		ps.close();
 
		verify();
	}	
 
	@Test
	public void testCallableStatementParameterNoOrder() throws SQLException {
		final CallableStatement ps = connection.prepareCall("{Call p_callable_test(:p1, :p2)}");
		ps.setString("p2", "2");	
		ps.setString("p1", "1");							
		ps.execute();
		ps.close();		
 
		verify();
	}	
 
	@AfterClass
	public static void tearDown() throws SQLException {
		if(connection == null)
			throw new RuntimeException("No connection...");
 
		PreparedStatement ps = connection.prepareStatement("Drop procedure p_callable_test");
		ps.executeUpdate();
		ps.close();
 
		ps = connection.prepareStatement("Drop table callable_statement_test");
		ps.executeUpdate();
 
		connection.close();
	}
}

In this test i actually used the same names of parameters like in the stored procedure and you clearly see p1 should be inserted in column1 and p2 in column2.

#testCallableStatementParameterInOrder succeeds, but #testCallableStatementParameterNoOrder fails. The only difference is that i set p2 before p1… To me, this is a severe bug as named parameters imply a very different behavior.

I strongly advice my colleagues to be very careful about using plain named parameters. The order in which they are set is important!

I could reproduce this behavior with OJDBC 11.1.0.7 and 11.2.0.3 against Oracle 10g and 11g. The example does not work at all with OJDBC 10.2.x.

Fixing hibernate “Cannot release connection” exception using DBCP and MySQL.

21-Nov-11

Every 8 hours i got a Hibernate exception “Cannot release connection” within a Java application using Hibernate, Apache DBCP on Tomcat:

org.hibernate.exception.GenericJDBCException: Cannot release connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
    ..
    ..
Caused by: java.sql.SQLException: Already closed.

Not only that the messages polluted my inbox, the exception was visible to the enduser, resulting in a HTTP 500 error. An older blog post i found suggested dismissing DBCP and using c3p0, a solution that i’m not quite found of. At least, the post helped to reproduce the problem within my development setup. The underlying problem was indeed the MySQL wait_timeout.

There’s quite a long documentation on the Tomcat JDBC Connection Pool. Although the Tomcat team recommends their own solution since Tomcat 7, i still wanted to go with DBCP.

The relevant keywords are “testOnBorrow”, “testOnReturn”, “testWhileIdle”, “validationQuery” and “timeBetweenEvictionRunsMillis”. The first 3 are boolean values. If set to true, the query given as validationQuery is executed on borrowing a connection from the pool, on returning or when idling. The first option is not an option on production use as the query is executed before each call. Although “Select 1″ is probably very fast, i just don’t want to have. Also: The problem is an invalidated, idle connection so i set testWhileIdle to true. And what happened? Nothing! The problem stayed. So there is the last option timeBetweenEvictionRunsMillis which should, according to the docs, default to 5 seconds but it doesn’t. The documentation is wrong. It’s under zero, so the eviction thread that tests idle connections never run. I’ve tweeted the tomcat team, but there was no reaction.

So the correct configuration for a DBCP pool database source is:

<Resource
	type="javax.sql.DataSource"
	driverClassName="com.mysql.jdbc.Driver"
	maxActive="100"
	maxIdle="30"
	maxWait="10000"
	testOnBorrow="false"
	testOnReturn="false"
	testWhileIdle="true"
	validationQuery="Select 1"
	timeBetweenEvictionRunsMillis="1800000"
/>

This way the eviction thread runs every 30 minutes, testing idle connections with the query “Select 1″ and removing them from the pool. The timeBetweenEvictionRunsMillis should not be to low. It should be adapted to the configured MySQL wait_timeout.

JDBC: Get autogenerated keys on a Oracle DB

09-Oct-07

With the current and latest Oracle JDBC Drivers it’s possible to retrieve one automatically generated key based on a sequence or any other arbitrary value (autogenerated-keys or identity columns in other databases).

Certainly it isn’t as simple as just use using Statement.html#getGeneratedKeys() as it simply returns an emtpy resultset.

After browsing around, i saw that java.sql.Connection can prepare a statement with a flag to return generated keys like so:

connection.prepareStatement("INSERT INTO FOOBAR VALUES(id.nextval, 1), Statement.RETURN_GENERATED_KEYS);

(By the way, it’s always a good idea to use prepared statements from a performance point of view as they can be reused)

Anyway, it wouldn’t be an Oracle product if something is different like any other products and i still was left alone in the dark with an empty result set.

Follow the path to enlightment:

final String sql = "INSERT INTO foobar(id, b) VALUES (id.nextval, ?)";
stmt = connection.prepareStatement(sql,new String[]{"ID"});			
stmt.setString(1, "bar");
stmt.execute();		
rs = stmt.getGeneratedKeys();
rs.next();
rv = rs.getInt(1);

Telling oracle which column contains the generated value does the trick. It’s really well hidden on their website. Be aware, you cannot refer to the returned keys by name, you need to address them 1 based.

Be aware that this doesn’t work inside a 9.2.x.x or 10.2.x.x Oracle Database as a Java Stored Procedure. Either the driver isn’t JDBC 3 (9.2) or the methods are not supported (10.2). You can work around this problem with 2 statements, first select id.nextval from somewhere, then execute your insert. Lame, but i didn’t find any other solution.

Ran on the client side with the latest JDBC from Oracle on the other hand works just fine.