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;
	}

希望对大家有用！