Spring JdbcTemplate实现有java.sql.ResultSet结果集返回的存储过程调用

一、调用存储过程

方式一：

/**
 * 方法功能说明：通过存储过程分页，获取分页信息以及查询记录（装在list中）；
 * List.get(0)= hsTable; //存放分页信息(tableName/token/pageNo/pageSize/recordsCount/pagesCount)--hsTable.get(key)
 * List.get(1)= recordsList; //存放分页查询所获的记录--遍历记录(每一条记录是一个map)
 * 创建：2012-10-16 by hsy 
 * 修改：日期 by 修改者
 * 修改内容：
 * @参数： @param inputXML
 * @参数： @return    
 * @return List   
 * @throws
 */
private List getOutputRecords(String inputXML)throws Exception{
List list = new ArrayList();
try {
String[] params = xxm.parseXml4GetPageRecords(inputXML);//{tableName,sqlWhere,orderColumn,orderType,pageSize,pageNo,token};
final String tableName = params[0];
final String sqlWhere = params[1];
final String orderColumn = params[2];
final String orderType = params[3];
final int pageSize = Integer.parseInt(params[4]);
final int pageNo = Integer.parseInt(params[5]);
final String token = params[6];
String procedure = "{call prc_query(?,?,?,?,?,?,?,?,?)}"; 

list = (List) this.getJdbcTemplate().execute(procedure,new CallableStatementCallback(){
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.setString(1, tableName);
cs.setString(2, sqlWhere);
cs.setString(3, orderColumn);
cs.setString(4, orderType);
cs.setInt(5, pageNo);
cs.setInt(6, pageSize);
cs.registerOutParameter(5,OracleTypes.INTEGER);//OracleTypes.INTEGER  java.sql.Types.INTEGER
cs.registerOutParameter(6,OracleTypes.INTEGER);
cs.registerOutParameter(7,OracleTypes.INTEGER);
cs.registerOutParameter(8,OracleTypes.INTEGER);
cs.registerOutParameter(9,OracleTypes.CURSOR);
cs.execute();
List newList = new ArrayList();
Hashtable hsTable = new Hashtable();
hsTable.put("tableName", tableName);
hsTable.put("token", token);
String pageNo = cs.getObject(5).toString();
hsTable.put("pageNo", pageNo+"");
String pageSize = cs.getObject(6).toString();
hsTable.put("pageSize", pageSize+"");
String recordsCount = cs.getObject(7).toString();
hsTable.put("recordsCount", recordsCount);
String pagesCount = cs.getObject(8).toString();
hsTable.put("pagesCount", pagesCount);
ResultSet rs = (ResultSet)cs.getObject(9);
newList.add(hsTable);
//每循环一次遍历出来1条记录，记录对应的所有列值存放在map中(columnName:columnValue)
List recordsList = getResultSet(rs);
newList.add(recordsList);
return newList;
}
});

} catch (Exception e) {
e.printStackTrace();
}
return list;
}

方式二：

/**
 * 方法功能说明：通过存储过程分页，获取分页信息以及查询记录（装在list中）；
 * List.get(0)= hsTable; //存放分页信息(tableName/token/pageNo/pageSize/recordsCount/pagesCount)--hsTable.get(key)
 * List.get(1)= recordsList; //存放分页查询所获的记录--遍历记录(每一条记录是一个map)
 * 创建：2012-10-16 by hsy 
 * 修改：日期 by 修改者
 * 修改内容：
 * @参数： @param inputXML
 * @参数： @return    
 * @return List   
 * @throws
 */
private List getOutputRecords(String inputXML)throws Exception{
List list = new ArrayList();
try {
String[] params = xxm.parseXml4GetPageRecords(inputXML);//{tableName,sqlWhere,orderColumn,orderType,pageSize,pageNo,token};
final String tableName = params[0];
final String sqlWhere = params[1];
final String orderColumn = params[2];
final String orderType = params[3];
final int pageSize = Integer.parseInt(params[4]);
final int pageNo = Integer.parseInt(params[5]);
final String token = params[6];
String procedure = "{call prc_query(?,?,?,?,?,?,?,?,?)}"; 

list = (List) this.getJdbcTemplate().execute(new CallableStatementCreator(){
public CallableStatement createCallableStatement(Connection conn) throws SQLException {
CallableStatement cs = conn.prepareCall(procedure);
cs.setString(1, tableName);
cs.setString(2, sqlWhere);
cs.setString(3, orderColumn);
cs.setString(4, orderType);
cs.setInt(5, pageNo);
cs.setInt(6, pageSize);
cs.registerOutParameter(5,OracleTypes.INTEGER);//OracleTypes.INTEGER  java.sql.Types.INTEGER
cs.registerOutParameter(6,OracleTypes.INTEGER);
cs.registerOutParameter(7,OracleTypes.INTEGER);
cs.registerOutParameter(8,OracleTypes.INTEGER);
cs.registerOutParameter(9,OracleTypes.CURSOR);
return cs;
}
},new CallableStatementCallback(){
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.execute();
List newList = new ArrayList();
Hashtable hsTable = new Hashtable();
hsTable.put("tableName", tableName);
hsTable.put("token", token);
String pageNo = cs.getObject(5).toString();
hsTable.put("pageNo", pageNo+"");
String pageSize = cs.getObject(6).toString();
hsTable.put("pageSize", pageSize+"");
String recordsCount = cs.getObject(7).toString();
hsTable.put("recordsCount", recordsCount);
String pagesCount = cs.getObject(8).toString();
System.out.println("\n");
hsTable.put("pagesCount", pagesCount);
ResultSet rs = (ResultSet)cs.getObject(9);
newList.add(hsTable);
//每循环一次遍历出来1条记录，记录对应的所有列值存放在map中(columnName:columnValue)
List recordsList = getResultSet(rs);
newList.add(recordsList);
return newList;
}
});

} catch (Exception e) {
e.printStackTrace();
}
return list;
}

二：取结果集方法

/**
 * 方法功能说明：将分页取出的结果集ResultSet对象组装成 List<--Map<--(columnName:columnValue),
 * 每一个map对应一条记录，map长度 == column数量
 * 创建：2012-10-16 by hsy 
 * 修改：日期 by 修改者
 * 修改内容：
 * @参数： @param rs
 * @参数： @return    
 * @return Map   
 * @throws
 */
private List getResultSet(ResultSet rs)throws SQLException{
List list = new ArrayList();
try {
ResultSetMetaData rsmd = rs.getMetaData();
//每循环一次遍历出来1条记录，记录对应的所有列值存放在map中(columnName:columnValue)
while(rs.next()){
Map map = new HashMap(); 
int columnCount = rsmd.getColumnCount();
for(int i=0;i<columnCount;i++){
String columnName = rsmd.getColumnName(i+1);
map.put(columnName, rs.getObject(i+1));
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}

希望对大家有用！