JDBC - Gateway to call the stored procdures - Any comments are appreciated

/*
* Created on Apr 29, 2004
*
*
*/
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
/**
* This class helps as utility to avoid redundant code of exception handling and setting the in/out/inout params
* to invoke the db function & procedures. */
public class CallableStatementFactory {
//public constructor
public CallableStatementFactory() {
}
/**
* @param m_objConn
* @param procName
* @param execType
* @param parametersList
* @return The return object <code>Object</code>.
* @throws StoreAccessException
*/
public Object invokeStoredProcedure(
Connection m_objConn,
String procName,
String execType,
ArrayList parametersList)
throws StoreAccessException {
CallableStatement objCallableStmt = null;
Object retVal = null;
int index = 0;
int dataType = 0;
String paramType = "";
try {
//check connection exists
if (null == m_objConn) {
//log error
log.fatal("Failed to open a connection to the oracle database");
}
//check the procName been supplied
if (null == procName) {
log.error("Procedure name is null");
}
//check the parameters list is null
if (null == parametersList) {
log.error("set the required params for the proc");
}
//TODO:check if the size of the list is > 0 - can be zero also???
if (parametersList.size() < 1) {
log.error("Parameter list cannot be less than 1");
}
//Create the CallableStatement.
objCallableStmt =
m_objConn.prepareCall("{call " + procName + "}");
for (int i = 0; i < parametersList.size(); i++) {
ProcParams params = (ProcParams) parametersList.get(i);
index = params.getIndex();
log.debug("index to set " + index);
dataType = params.getDataType();
log.debug("data type to set to proc " + dataType);
paramType = params.getParamType();
log.debug("param type " + paramType);
if (paramType.equalsIgnoreCase("out")) {
objCallableStmt.registerOutParameter(index, dataType);
} else {
Object value = params.getParamValue();
log.debug(value);
if (value != null) {
objCallableStmt.setObject(index, value);
} else {
objCallableStmt.setNull(index, Types.VARCHAR);
}
}
}
//clear the list after you obtain the params
//might be an index parameter of an out parameter is required for which
//the objCallableStmt.getObject(index); can be invoked. So not sure, to clear
// the array list right here.
parametersList.clear();
//Execute the stored proc. {This is conditional}.
if (execType.equalsIgnoreCase("EQ")) {
// the below returns the ResultSet. Either we can return as it is back or deal with
//the out param index as below
objCallableStmt.executeQuery();
//index of out parameter is required for which
//the objCallableStmt.getObject(index); can be invoked
retVal = objCallableStmt.getObject(index);
} else if (execType.equalsIgnoreCase("EU")) {
//executeUpdate always returns an int.
int updateCnt = objCallableStmt.executeUpdate();
//TODO:should we check no of rows returned by executeUpdate???
if (updateCnt > 0) {
retVal = new Boolean(true);
} else {
retVal = new Boolean(false);
}
} else {
//index of out parameter is required for which
//the objCallableStmt.getObject(index); can be invoked
objCallableStmt.execute();
retVal = objCallableStmt.getObject(index);
}
} catch (SQLException e) {
log.fatal(e);
if (StoreAccessException.isKnownCode(e.getErrorCode())) {
throw new StoreAccessException(
StoreAccessException.SEVERITY_ERROR,
e.getErrorCode(),
e);
} else {
throw new StoreAccessException(
StoreAccessException.SEVERITY_FATAL,
StoreAccessException.SAE_GENERAL_SQL_EXCEPTION,
new String[] {
Integer.toString(e.getErrorCode()),
e.getMessage()},
e);
}
} finally {
try {
if (objCallableStmt != null) {
objCallableStmt.close();
}
} catch (SQLException e) {
log.error(e.toString());
}
}
return retVal;
}
/** This method helps to build the ProcParams instance.
*
*/
public ProcParams buildProcParam(
int index,
int dataType,
String paramType,
Object paramValue) {
ProcParams p = new ProcParams();
p.setIndex(index);
p.setDataType(dataType);
p.setParamType(paramType);
p.setParamValue(paramValue);
return p;
}
}

Welcome to JavaRanch, fisher!
When posting code, please be sure to surround the code with the [code] and [/code] UBB Tags. This will help to preserve the formatting of the code, thus making it easier to read and understand.
Note that you can edit your own posts - just click on the icon that looks like a piece of paper.
I'm moving this to the JDBC forum...

The last parameter in your input parameter list must be your output parameter? When your return value is a ResultSet, I'd be interested in seeing the code that calls this code. Do you return the ResultSet back to a business tier? Or, do you convert the ResultSet to something else for usage in the business tier?

I would give the resultset to the database wrapper classes.

[ April 30, 2004: Message edited by: fisher kanth ]
[ April 30, 2004: Message edited by: fisher kanth ]