Spring 调用存储过程返回结果集

Spring 的JdbcTemplate 提够了对存储过程调用的支持
但是直接体构一种常用的返回结果集，sqlServer(ResultSet),Oracle(游标)的方式
很多情况下我们需要把这些信息封装成List<Map>,或者List<Bean>的形式返回给应用层。

实现方式：

@Override
public List<Map<String, Object>> callProcedure(String spName,Object[] paramValues) throws SQLException {
		List<Map<String, Object>> rows = null;
		CommonStoredProcedure sp = new CommonStoredProcedure(this.getJdbcTemplate(),spName);
		rows = sp.queryList(Arrays.asList(paramValues));
		return rows==null?new ArrayList(0):rows;
	}

CommonStoredProcedure
继承 StoredProcedure抽象类

public List queryList(List inParams){
		List result = null;
		Map<String,Object> paramMap = 
new HashMap<String,Object>();
		for(int i=0;i<inParams.size();i++){
    		Object paramValue = inParams.get(i);
//按照顺序设置如参数的值    		
String paramName = "In_"+String.valueOf(i);
    		int paramType = getSqlType(paramValue);
    		setParameter(paramName,paramType);   		paramMap.put(paramName, paramValue);
    	}
		setInParam(paramMap);
//设置返回结果级		
declareParameter(new SqlReturnResultSet("Out",rowMapper));
		Map<String,Object> map = execute();
		result = (List)map.get("Out");
    	return result == null?new ArrayList(0):result;
	}

上面的代码对DB2和SQLSERVER都可以使用，但
oracle 比较不一样的
他设定返回的必须用SqlOutParameter，

引用

Oracle的版本是非常相似的，只不过Oracle使用正规的输出参数传回结果集。这个参数必须在Oracle.jdbc.OracleTypes.CURSOR类型之前声明，而且它还必须传入一个RowMapper实现作为第三个参数。因为这是正规的SqlOutParameter输出参数，它应该以相同的次序声明，就好像它是任何其他类型的输出参数一样

declareParameter(new SqlOutParameter("genre", oracle.jdbc.OracleTypes.CURSOR, new MapGenre()));
    

完整代码

public class CommonStoredProcedure extends StoredProcedure {
	
	private Map<String,?> inParam;
	private RowMapper rowMapper = new ColumnMapRowMapper();
	
	public CommonStoredProcedure(DataSource ds, String spName){
		super(ds,spName);
	}
	public CommonStoredProcedure(JdbcTemplate jdbcTemplate, String name) {
		super(jdbcTemplate,name);
	}
	
	public void setMapperBean(Class mappedClass){
		rowMapper = new HeipBeanPropertyRowMapper(mappedClass);
	}
	
	public List queryList(List inParams){
		List result = null;
		Map<String,Object> paramMap = 
new HashMap<String,Object>();
		for(int i=0;i<inParams.size();i++){
    		Object paramValue = inParams.get(i);
    		String paramName = "In_"+String.valueOf(i);
    		int paramType = getSqlType(paramValue);
    		setParameter(paramName,paramType);   		paramMap.put(paramName, paramValue);
    	}
		setInParam(paramMap);
		declareParameter(new SqlReturnResultSet("Out",rowMapper));
		Map<String,Object> map = execute();
		result = (List)map.get("Out");
    	return result == null?new ArrayList(0):result;
	}
	
	
	public Map<String,Object> execute(){
		compile();
		return execute(inParam);
	}
	public void setInParam(Map<String,?> inParam){
		this.inParam = inParam;
	}
	public void setOutParameter(String column,int type,RowMapper rowMapper){
		declareParameter(new SqlOutParameter(column,type,rowMapper));
	}
	public void setParameter(String column,int type){
		declareParameter(new SqlParameter(column,type));
	}
	public void setParameters(String[] columns, int[] types) {
		for (int i = 0; i < columns.length; i++) {
			setParameter(columns[i],types[i]);
		}
	}
	
	private static int getSqlType(Object obj){
		if (obj instanceof String) {
			return Types.VARCHAR;
		}
		if (obj instanceof Long
		    	||obj instanceof BigDecimal
		    	||obj instanceof Double
		    	||obj instanceof Integer) {
			return Types.NUMERIC;
		}
		if (obj instanceof Date) {
			return Types.DATE;
		}
		/*if (obj instanceof Boolean) {
			return Types.NUMERIC;
		}*/
		
		return Types.OTHER;
	 }
}

摘自：
Spring DAO之存储过程的高级用法
http://westzq.blog.hexun.com/6353026_d.html
Spring DAO之存储过程的高级用法
http://blog.163.com/z278440337@126/blog/static/186995032008420102635244/