调用带返回结果集存储过程:
/**
* 调用带返回结果集存储过程
*
* @param procName
* @param param
* @return
* @throws SQLException
* @throws NoFreeConnectionException
*/
public DataSource execuceProc(String procName, String[] param) throws SQLException, NoFreeConnectionException {
Connection con = null;
CallableStatement getResults = null;
ResultSet rs = null;
StringBuffer build = new StringBuffer();
build.append("{ call ").append(procName);
build.append("(");
if (param != null && param.length > 0) {
for (int i = 0; i < param.length; i++) {
build.append("'").append(param[i]).append("',");
}
}
build.append("?) }");
try {
con = ConnectDBBean.getConnection(ReadWriteDBPool.readPool);
getResults = con.prepareCall(build.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
getResults.registerOutParameter(1, OracleTypes.CURSOR);
getResults.execute();
rs = (ResultSet) getResults.getObject(1);
this.rsToDataSource(rs);
} finally {
try {
if (rs != null) {
rs.close();
}
if (getResults != null) {
getResults.close();
}
} catch (SQLException e) {
log.error(e);
}
ConnectDBBean.closeConnection(ReadWriteDBPool.readPool, con);
}
return new DataSourceType();
}
调用带参存储过程:
/**
* 调用带参存储过程
*
* @param procName
* @param param
* @return
* @throws SQLException
* @throws NoFreeConnectionException
*/
@SuppressWarnings("unchecked")
public static void execuceProc(String procName, List param) throws SQLException, NoFreeConnectionException {
Connection con = null;
CallableStatement getResults = null;
StringBuffer build = new StringBuffer();
build.append("{ call ").append(procName);
build.append("(");
if (param != null && param.size() > 0) {
for (int i = 0; i < param.size(); i++) {
if (i > 0)
build.append(",");
build.append("'").append(param.get(i)).append("'");
}
}
build.append(") }");
try {
con = ConnectDBBean.getConnection(ReadWriteDBPool.readPool);
getResults = con.prepareCall(build.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
getResults.execute();
} finally {
try {
if (getResults != null) {
getResults.close();
}
} catch (Exception e) {
log.error(e);
}
ConnectDBBean.closeConnection(ReadWriteDBPool.readPool, con);
}
}