</pre><h1>1、调用存储过程--返回list以及无结果返回以及只返回一个参数</h1><pre code_snippet_id="671267" snippet_file_name="blog_20150520_2_9698049" name="code" class="java">
/**
* 获取数据库内的存储过程--返回一个List
* @param procedureName 存储过程名
* @param inParameter 输入的参数
* @param outParamter 输出的参数
* @return
*/
public List<Map> callProcedure(final String procedureName,final List inParameter,final List outParamter){
if(procedureName==null || procedureName.length() == 0 ){
return null;
}
//没有返回参数
if(outParamter == null ){
if(callProcedureWithoutOut(procedureName,inParameter))return null;
}
List resultList = (List) DBUtility.getJdbcTemplate().execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
int inSize = inParameter==null?0:inParameter.size();
int outSize = outParamter==null?0:outParamter.size();
StringBuffer sbsql = new StringBuffer();
sbsql.append("{call "+procedureName).append("(");
for(int i=0;i<(inSize+outSize);i++){
if(i == 0){
sbsql.append("?");
}else{
sbsql.append(",?");
}
}
sbsql.append(")}");
CallableStatement cs = con.prepareCall(sbsql.toString());
// 设置输入参数的值
if(inSize > 0 ){
String typeName = null;
for(int i=0;i<inSize;i++){
typeName = inParameter.get(i).getClass().getName().toString();
cs.setObject(i+1, inParameter.get(i));
}
}
// 注册输出参数的类型
cs.registerOutParameter(inSize+1, OracleTypes.CURSOR);
return cs;
}
}, new CallableStatementCallback<List>() {
public List<Map> doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
int inSize = inParameter==null?0:inParameter.size();
List<Map> resultsMap = new ArrayList();
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(inSize+1);// 获取游标一行的值
while (rs.next()) {// 转换每行的返回值到Map中
Map rowMap = new HashMap();
for(int i=0;i<outParamter.size();i++){
String outP = outParamter.get(i).toString();
rowMap.put(outP.toLowerCase(),rs.getObject(outP.toUpperCase()) );
}
resultsMap.add(rowMap);
}
rs.close();
return resultsMap;
}
});
return resultList;
}
/**
* 获取数据库内的存储过程--没有输出
* @param procedureName 存储过程名
* @param inParameter 输出的参数
*/
public Boolean callProcedureWithoutOut(final String procedureName,final List inParameter){
if(procedureName==null || procedureName.length() == 0 ){
return false;
}
StringBuffer sbsql = new StringBuffer();
sbsql.append("{call "+procedureName).append("(");
for(int i=0;i<inParameter.size();i++){
if(i == 0){
sbsql.append(inParameter.get(i));
}else{
sbsql.append(","+inParameter.get(i));
}
}
sbsql.append(")}");
DBUtility.getJdbcTemplate().execute(sbsql.toString());
return true;
}
/**
* 获取数据库内的存储过程--输出只有一个参数
* @param procedureName
* @param inParameter
* @param outParamer
* @return
*/
public String callProcedureOnlyoneOut(final String procedureName,final List inParameter,final String outParamer){
if(procedureName==null || procedureName.length() == 0 ){
return null;
}
String result = (String) DBUtility.getJdbcTemplate().execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
int inSize = inParameter==null?0:inParameter.size();
StringBuffer sbsql = new StringBuffer();
sbsql.append("{call "+procedureName).append("(");
for(int i=0;i<(inSize+1);i++){
if(i == 0){
sbsql.append("?");
}else{
sbsql.append(",?");
}
}
sbsql.append(")}");
CallableStatement cs = con.prepareCall(sbsql.toString());
// 设置输入参数的值
if(inSize > 0 ){
String typeName = null;
for(int i=0;i<inSize;i++){
typeName = inParameter.get(i).getClass().getName().toString();
cs.setObject(i+1, inParameter.get(i));
}
}
// 注册输出参数的类型
cs.registerOutParameter(inSize+1, OracleTypes.CURSOR);
return cs;
}
}, new CallableStatementCallback<Object>() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
int inSize = inParameter==null?0:inParameter.size();
List<Map> resultsMap = new ArrayList();
cs.execute();
return cs.getObject(inSize+1);// 获取游标一行的值
}
});
return result;
}