try {
conn = DBConnection.getDBConnection();
String SQL = "{call PACKAGE.PROCEDURE(?, ?)}";
CallableStatement cstmt = conn.prepareCall(QL);
cstmt.setInt(1, id);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.executeQuery();
rs = (ResultSet) cstmt.getObject(2);
while (rs.next())
{
Vector temp = new Vector();
temp.add(rs.getString("id"));
temp.add(rs.getString("description"));
routeVector.add(temp);
}
}catch (Exception e)
{
logger.log(e);
}finally
{
DBConnection.closeResultSet(rs);
DBConnection.closeCallableStatement(cstmt);
DBConnection.freeConnection(conn);
}
Refactorings
No refactoring yet !
nicerobot
March 15, 2010, March 15, 2010 21:40, permalink
I think you're actually asking for two different things. 1) Improve the pattern used for almost all JDBC code. 2) How to generically return a ResultSet from a query or PL/SQL call. Neither is really a simple refactoring solution. The latter is somewhat subjective. The prior is partly what JDO and JPA help resolve. But a few years ago i wrote a framework that improved on the JDBC-pattern issue. It's relatively easy to understand and will be left as an exercise to implement it yourself :) It's not all that complicated for basic, common query functionality. The benefit is that SQL statements can be completely isolated into static references which includes all the metadata about the statement, i.e. including the inputs and outputs. So anywhere the statement is used, it's referred to similar to a collection (which is pretty much all it is anyway).
Query execute = new Query("{any select or pl/sql}",
// Inputs and Outputs are for bind variables.
new SQL.Inputs(Integer.class, ...),
// Outputs is only meaningful for PL/SQL since the
// ResultSetMetaData should be used to obtain queried columns.
new SQL.Outputs(String.class));
// This can also be encapsulated in the Query to return a Collection but
// that's kinda subjective and depends on implementation and
// performance requirements.
for (Row r : execute.query(conn, id)) {
// Handle the rows
}
In my project, I do see lots and lots of code like the below one. This web application uses enormous amount of PL/SQL procedures for its jobs. Each time the Java code needs to execute a pl/sql procedure, such code is written.
I do not like the way vector inside a vector is returned and the way tons and tons of times similar code is being executed. I have some idea on refactoring the code, but would like to get ideas from you guys.