Java Database Connectivity (JDBC)

I've got a java middle tier communicating with Oracle 11.2 database, both on Linux CentOS servers, using Oracle's thin JDBC driver. So far I've done simple writes and reads using queries and ResultSet. I need to get some PL/SQL stored procedures working next. I've never done that before. The DBA knows enough to create the PL/SQL stored procedures, but doesn't know how to access what they return in java (thus my interest in figuring it out).

I'm trying to extract a template to help me understand how to work with PL/SQL stored procedures, and so far I've come up with the following.
---------------------

METHOD A:

import java.sql.CallableStatement

...

// connect to database
OracleDataSource ds = new OracleDataSource();
conn = ds.getConnection();

// create example variables
int in1 = 5;
String in2 = "mystring1";
String in3 = "mystring2";
String out1;
double out2;
int out3;
String FirstName;
String OfficeSupply;

// call a stored procedure with literal parameters
CallableStatement cs = conn.prepareCall("CALL myStoredProc(in1, in2, in3, out1, out2, out3)");

// ResultSet represents first query statement executed in the stored procedure
ResultSet res = cs.executeQuery();
while (res.next()) {
FirstName = res.getString("first_name");
...
}

// always retrieve ResultSet before OUT parameters
-------------------------------------

METHOD B:

import java.sql.CallableStatement
...
// create example variables
int in1 = 5;
String in2 = "mystring1";
String in3 = "mystring2";
String out1;
double out2;
int out3;

// call stored procedure using SQL92 syntax
CallableStatement cs = conn.prepareCall( "{call myStoredProc (?,?,?,?,?,?)}" );

// set IN parameters
cs.setInt(1, in1);
cs.setString(2, in2);
cs.setString(3, in3);

// register OUT parameter(s)
cs.registerOutParameter(4, Types.VARCHAR2);
cs.registerOutParameter(5, Types.BINARY_DOUBLE);
cs.registerOutParameter(6, Types.INTEGER);

// ResultSet represents first query statement executed in the stored procedure
ResultSet res = cs.executeQuery();
while (res.next()) {
...
}

// always retrieve ResultSet before OUT parameters
out1 = cs.getString(4);
out2 = cs.getBinaryDouble(5);
out3 = cs.getInt(6);
---------------------------

Q0: I show two methods above. Which one is a better template to work with?

Q1: Should I use an OracleCallableStatement instead of CallableStatement? Or, it doesn't make a difference? [http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/jdbc/OracleCallableStatement.html]

Q2: I didn't register any input or output parameters because I read this was only needed if placeholders were used as an IN or an OUT parameter (since I define the java data types for in1, in2, in3, out1, out2, and out3, is this OK)? [http://www.herongyang.com/JDBC/Oracle-CallableStatement-Overview.html]

Q3: What's the purpose of the OUT parameter(s)? Couldn't the stored procedure simply provide, for example, out1, out2, and out3 inside the ReturnSet (along with whatever else is in the ReturnSet)? Or, does this just provide another means to achieve the same thing (i.e. retrieve data from database) without using (or in addition to using) a ResultSet?

Q4: How to handle the scenario where two ReturnSet's are returned? Do I just add another

ResultSet res = cs.executeQuery();
while (res.next()) {
OfficeSupply = res.getString("office_item");
...
}

after the first one above (for example)?

Edited by: 918175 on Mar 2, 2012 2:48 PM