Oracle and JDBC, multiple objects one query

In a world of JPA and various ORM's, sometimes the lowly JDBC is completely forgotten. However, JDBC has a powerful advantage over ORM and that is that it gives the developer complete control over what is executed and how the data is mapped relationally. More often than not, we retrieve data from multiple tables that we then map to multiple objects. There are several ways to accomplish this, we can make a "super-query" with all the columns for all the tables we need on one row and we do some fancy "if" statement fun to model the data to our objects. Or we can make a proc on the Oracle side and declare cursors and the whole deal to get what we want. I'm actually not a big fan of other one, because there is a little heavy lifting involved either way. My preferred method of getting records back is to use Oracle's "cursor expressions". Cursor expressions are simply queries embedded in the SQL that come back as ref cursors. So let's see them in action.

1. select d.*,  
2. cursor(select e.* from employees e where e.department_id = d.department_id) as employees  
3. from departments d;  

Using Oracle 10g Express Edition's hr schema, I wanted all departments, with all the employees in each department in one call. Using the cursor keyword, I now have a ref cursor pointing to those employees per department record.

Enough already! Show me how to get this back in JDBC. Alright, since you were so nice about it. Its actually dirt simple.

1. //... normal jdbc type execution code here  
2. //my result set object is called rs  
3. ResultSet employees = (ResultSet)rs.getObject("employees");  

You simply use the getObject call using the ResultSet object returned by JDBC when executing a select statement, and then cast it to a ResultSet object, which in my example will be a ResultSet containing employee information.

So why should you use this? Um, because its fast and its easy. The way to get the most out of your database call is to get the most amount of data back at a time as you can. Using cursor expressions allows you to do that, without creating lots of procedures to return various ref cursors.

But Nick, what about all those open cursors won't I exceed max number of cursors on Oracle? The answer is no, you won't. You just have to remember to close those ResultSet object you are creating. So if you do get that message, check to make sure all your ResultSet's are being closed properly.

Have fun with your fast JDBC code, your customers will love you for it.