Java 里Spring如何调用oracle存储过程：OracleCallableSupport

Usage：

/**
* 调用存储过程返回结果集
*
* @param proc
*            存储过程名称
* @param map
*            存储过程绑定参数
*/
@SuppressWarnings("unchecked")
public List<Map<String, Object>> callProc(String proc,
final Map<String, Object> map) {
OracleCallableSupport support = new OracleCallableSupport(proc, map);
List<Map<String, Object>> result = (List<Map<String, Object>>) getJdbc()
.execute(support.getCreator(), support.getCallback());
return result;
}


OracleCallableSupport.java：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import oracle.jdbc.OracleTypes;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ColumnMapRowMapper;
/**
* 支持ORACLE存储过程调用.<br>
* 约定:被调用的存储过程，最后一个参数为OUT SYS_REFCURSOR类型，其他参数为IN类型
*/
public class OracleCallableSupport implements CallableStatementCreator {
static final String SQL_IN_ARGS = "select t.POSITION,t.ARGUMENT_NAME from all_arguments t"
+ " where t.in_out = 'IN' and t.OWNER = %s and t.object_name = upper(:object_name)";
private StoredProc storedProc;
/**
* 存储过程绑定参数
*/
private Map<String, Object> bindParam;
private int resultSetIndex;
/**
* Constructs a callable statement.
*
* @param proc
*            The stored procedure's name.
* @param params
*            Input parameters.
* @param outResultCount
*            count of output result set.
*/
public OracleCallableSupport(String proc, Map<String, Object> bindParam) {
this.bindParam = bindParam;
this.storedProc = new StoredProc(proc);
}
/**
* Get the CallableStatementCreator
*/
public CallableStatementCreator getCreator() {
return this;
}
/**
* Get the CallableStatementCallback
*/
public CallableStatementCallback getCallback() {
return new Callback();
}
protected Map<Integer, String> getInArgs() {
String sql = String.format(SQL_IN_ARGS, this.storedProc.owner);
Map<String, Object> map = new HashMap<String, Object>();
map.put("object_name", this.storedProc.name);
List<Map<String, Object>> args = AbstractDao.DAO.getSimpleJdbc()
.queryForList(sql, map);
Map<Integer, String> result = new HashMap<Integer, String>();
for (Map<String, Object> arg : args) {
int pos = Integer.valueOf(String.valueOf(arg.get("POSITION")));
result.put(pos, String.valueOf(arg.get("ARGUMENT_NAME")));
}
setResultSetIndex(result.size() + 1);
return result;
}
protected int getResultSetIndex() {
return this.resultSetIndex;
}
protected void setResultSetIndex(int index) {
this.resultSetIndex = index;
}
/**
* Returns a callable statement
*
* @param conn
*            Connection to use to create statement
* @return cs A callable statement
*/
public CallableStatement createCallableStatement(Connection conn) {
StringBuffer sql = new StringBuffer("call ");
sql.append(storedProc).append("(");
// set input parameters
Map<Integer, String> args = getInArgs();
for (int i = 0; i < args.size(); i++) {
sql.append("?").append(",");
}
// set ountput parameters
sql.append("?").append(")");
CallableStatement cs = null;
try {
cs = conn.prepareCall(sql.toString());
// set the paramters
for (Map.Entry<Integer, String> arg : args.entrySet()) {
cs.setObject(arg.getKey(), this.bindParam.get(arg.getValue()));
}
// set the last parameter is OracleTypes.CURSOR for oracle stored
// procedure
cs.registerOutParameter(getResultSetIndex(), OracleTypes.CURSOR);
} catch (SQLException e) {
throw new DaoException(e);
}
return cs;
}
protected static class StoredProc {
String owner;
String name;
String proc;
StoredProc(String proc) {
this.proc = proc;
int i = proc.indexOf(".");
if (i < 0) {
owner = "user";
name = proc;
} else {
owner = "'" + proc.substring(0, i) + "'";
name = proc.substring(i + 1);
}
}
@Override
public String toString() {
return this.proc;
}
}
/**
*
* The ProcCallableStatementCallback return a result object, for example a
* collection of domain objects.
*
*/
public class Callback implements CallableStatementCallback {
/**
* Constructs a ProcCallableStatementCallback.
*/
Callback() {
}
/**
* Returns a List(Map) collection.
*
* @param cs
*            object that can create a CallableStatement given a
*            Connection
* @return resultsList: List&lt;Map&gt; a result object returned by the action
*/
public Object doInCallableStatement(CallableStatement cs) {
List<Object> resultList = new ArrayList<Object>();
try {
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(getResultSetIndex());
ColumnMapRowMapper mapper = new ColumnMapRowMapper();
int i = 0;
while (rs.next()) {
Object obj = mapper.mapRow(rs, i++);
resultList.add(obj);
}
rs.close();
} catch (SQLException e) {
throw new DaoException(e);
}
return resultList;
}
}
}