JDBC动态执行存储过程

案例一：

/**
 * 使用JDBC调用数据库存储过程做分页查询（支持输出参数）
 * 
 * @author xpy
 * 
 * @param procName
 *            存储过程名称
 * @param inParams
 *            过程的输入参数（包括分页变量：第几页pageNo、每页多少条pageSize，如果是空字符串，请将其转化为null）
 * @param outParams
 *            过程的输出参数（包括总记录数totalSize，参数的值是存储过程中定义的输出参数的变量类型，如Types.DECIMAL）
 *            请务必保证输入输出参数的变量名称与存储过程定义的变量名称相同
 * @return
 * @throws SQLException
 */
@Override
public PageResult<List<Map<String, Object>>> callProcedureForPagedata1(String procName, Map<String, Object> inParams,
		Map<String, Integer> outParams) throws SQLException {
	Connection con = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
	ResultSet rs = null;
	CallableStatement call = con.prepareCall("{CALL " + procName + "}");
	// 设置输入参数
	if (inParams != null && !inParams.isEmpty()) {
		for (String one : inParams.keySet()) {
			call.setObject(one, inParams.get(one));
		}
	}
	// 注册输出参数
	outParams.put("totalSize", Types.INTEGER);
	if (outParams != null && !outParams.isEmpty()) {
		for (String one : outParams.keySet()) {
			call.registerOutParameter(one, outParams.get(one));
		}
	}
	call.execute();
	rs = call.getResultSet();// 获取查询结果集
	List<Object> list = new ArrayList<Object>();
	// 将结果集封装出List类型的集合
	while (rs != null && rs.next()) {
		Map<String, Object> rowData = new HashMap<String, Object>();
		ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
		int columnCount = md.getColumnCount();
		for (int i = 1; i <= columnCount; i++) {
			rowData.put(md.getColumnLabel(i), rs.getObject(i));
		}
		list.add(rowData);
	}
	long totalSize = call.getLong("totalSize");// 总记录数的输出参数
	int pageNo = inParams.get("pageNo") == null ? 0 : (int) inParams.get("pageNo");
	int pageSize = (int) (inParams.get("pageSize") == null ? totalSize : (int) inParams.get("pageSize"));
	PageResult<List<Map<String, Object>>> pageResult = new PageResult(pageNo, totalSize, pageSize, list);

	// 释放资源
	call.close();
	con.close();

	return pageResult;
}

相关对象

public class PageResult<E> {
	private int pageIndex; //从第几条开始
	private long countRow; // 总记录数
	private int pageSize; // 每页记录数
	private int countPage; // 总页数
	private List<E> data; // 数据集合

	public PageResult() {
	}

	// 构造初始化(并且计算总页数和设置分页参数)
	public PageResult(int pageIndex, long countRow, int pageSize, List<E> data) {
		this.data = (data == null ? new ArrayList<E>() : data);
		this.pageIndex = pageIndex;
		this.pageSize = pageSize;
		this.countRow = countRow;
		if (countRow != 0) {
			if (countRow % pageSize == 0) {
				this.countPage = (int) countRow / pageSize;
			} else {
				this.countPage = (int) countRow / pageSize + 1;
			}
		} else {
			this.countPage = 0;
		}
	}
	get/set()
	public Map<String,Object> toMap(){
		Map<String,Object> res = new HashMap<>();
		res.put("pageIndex", pageIndex);
		res.put("countPage", countPage);
		res.put("pageSize", pageSize);
		res.put("countRow", countRow);

		return res;
	}
}

案例二：

/**
 * 使用JDBC执行存储过程
 * @param callStr {CALL CREATE_T_ACE_APPRAISE(?,?)}
 * @param inParams
 * @param outParam
 */
public int callProcedures(String callStr,Map<String,Object> inParams,Map<String,Integer> outParam){
	try {
		Connection con = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
		CallableStatement call = con.prepareCall(callStr);
		//注册IN值 
		if (inParams != null && !inParams.isEmpty()) {
			for (String one : inParams.keySet()) {
				call.setObject(one, inParams.get(one));
			}
		}
		//注册OUT值
		outParam.put("isOrNo", Types.INTEGER);
		if(outParam!=null && !outParam.isEmpty()){
			for(String key:outParam.keySet()){
				call.registerOutParameter(key, outParam.get(key));
			}
		}
		call.execute();
		
		//释放资源
		call.close();
		con.close();
		
		return call.getInt("isOrNo");
	} catch (Exception e) {
		e.printStackTrace();
	}
	return -1;
}