Java 里Spring如何调用oracle存储过程：OracleCallableSupport

Usage：

        /**
	 * 调用存储过程返回结果集
	 * 
	 * @param proc
	 *            存储过程名称
	 * @param map
	 *            存储过程绑定参数
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> callProc(String proc,
			final Map<String, Object> map) {
		OracleCallableSupport support = new OracleCallableSupport(proc, map);
		List<Map<String, Object>> result = (List<Map<String, Object>>) getJdbc()
				.execute(support.getCreator(), support.getCallback());

		return result;
	}

OracleCallableSupport.java：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ColumnMapRowMapper;

/**
 * 支持ORACLE存储过程调用.<br>
 * 约定:被调用的存储过程，最后一个参数为OUT SYS_REFCURSOR类型，其他参数为IN类型
 */
public class OracleCallableSupport implements CallableStatementCreator {
	static final String SQL_IN_ARGS = "select t.POSITION,t.ARGUMENT_NAME from all_arguments t"
			+ " where t.in_out = 'IN' and t.OWNER = %s and t.object_name = upper(:object_name)";

	private StoredProc storedProc;
	/**
	 * 存储过程绑定参数
	 */
	private Map<String, Object> bindParam;

	private int resultSetIndex;

	/**
	 * Constructs a callable statement.
	 * 
	 * @param proc
	 *            The stored procedure's name.
	 * @param params
	 *            Input parameters.
	 * @param outResultCount
	 *            count of output result set.
	 */
	public OracleCallableSupport(String proc, Map<String, Object> bindParam) {
		this.bindParam = bindParam;
		this.storedProc = new StoredProc(proc);
	}

	/**
	 * Get the CallableStatementCreator
	 */
	public CallableStatementCreator getCreator() {
		return this;
	}

	/**
	 * Get the CallableStatementCallback
	 */
	public CallableStatementCallback getCallback() {
		return new Callback();
	}

	protected Map<Integer, String> getInArgs() {
		String sql = String.format(SQL_IN_ARGS, this.storedProc.owner);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("object_name", this.storedProc.name);
		List<Map<String, Object>> args = AbstractDao.DAO.getSimpleJdbc()
				.queryForList(sql, map);

		Map<Integer, String> result = new HashMap<Integer, String>();
		for (Map<String, Object> arg : args) {
			int pos = Integer.valueOf(String.valueOf(arg.get("POSITION")));
			result.put(pos, String.valueOf(arg.get("ARGUMENT_NAME")));
		}
		setResultSetIndex(result.size() + 1);
		return result;
	}

	protected int getResultSetIndex() {
		return this.resultSetIndex;
	}
	
	protected void setResultSetIndex(int index) {
		 this.resultSetIndex = index;
	}

	/**
	 * Returns a callable statement
	 * 
	 * @param conn
	 *            Connection to use to create statement
	 * @return cs A callable statement
	 */
	public CallableStatement createCallableStatement(Connection conn) {
		StringBuffer sql = new StringBuffer("call ");
		sql.append(storedProc).append("(");
		// set input parameters
		Map<Integer, String> args = getInArgs();
		for (int i = 0; i < args.size(); i++) {
			sql.append("?").append(",");
		}
		// set ountput parameters
		sql.append("?").append(")");

		CallableStatement cs = null;
		try {
			cs = conn.prepareCall(sql.toString());
			// set the paramters
			for (Map.Entry<Integer, String> arg : args.entrySet()) {
				cs.setObject(arg.getKey(), this.bindParam.get(arg.getValue()));
			}
			// set the last parameter is OracleTypes.CURSOR for oracle stored
			// procedure
			cs.registerOutParameter(getResultSetIndex(), OracleTypes.CURSOR);
		} catch (SQLException e) {
			throw new DaoException(e);
		}
		return cs;
	}

	protected static class StoredProc {
		String owner;
		String name;
		String proc;

		StoredProc(String proc) {
			this.proc = proc;
			int i = proc.indexOf(".");
			if (i < 0) {
				owner = "user";
				name = proc;
			} else {
				owner = "'" + proc.substring(0, i) + "'";
				name = proc.substring(i + 1);
			}
		}

		@Override
		public String toString() {
			return this.proc;
		}
	}

	/**
	 * 
	 * The ProcCallableStatementCallback return a result object, for example a
	 * collection of domain objects.
	 * 
	 */
	public class Callback implements CallableStatementCallback {
		/**
		 * Constructs a ProcCallableStatementCallback.
		 */
		Callback() {
		}

		/**
		 * Returns a List(Map) collection.
		 * 
		 * @param cs
		 *            object that can create a CallableStatement given a
		 *            Connection
		 * @return resultsList: List&lt;Map&gt; a result object returned by the action
		 */
		public Object doInCallableStatement(CallableStatement cs) {
			List<Object> resultList = new ArrayList<Object>();
			try {
				cs.execute();
				ResultSet rs = (ResultSet) cs.getObject(getResultSetIndex());
				ColumnMapRowMapper mapper = new ColumnMapRowMapper();
				int i = 0;
				while (rs.next()) {
					Object obj = mapper.mapRow(rs, i++);
					resultList.add(obj);
				}
				rs.close();
			} catch (SQLException e) {
				throw new DaoException(e);
			}
			return resultList;
		}
	}
}