Java 调存储过程返回游标变量

Java 开发中有时候需要调用一个返回结果集的存储过程，批量返回查询数据结果。下面用个简单的存储过程演示下调用和处理过程。
数据库脚本执行：

Create Table student (grade varchar2(5), num number(6), Name Varchar2(20), age Integer);
Insert Into student Values('101', 100001, 'Jack', 20);
Insert Into student Values('102', 100001, 'Mike', 21);
Insert Into student Values('101', 100002, 'Merry', 19);
Insert Into student Values('102', 100002, 'Tony', 20);

存储过程：

Create Or Replace Package Pkg_School Is
  Type Ref_Cursor Is Ref Cursor;
  Procedure Find_Students_By_Grade(p_Grade    Varchar2,
                                   Out_Cursor Out Ref_Cursor);
End Pkg_School;

Create Or Replace Package Body Pkg_School Is
  Procedure Find_Students_By_Grade(p_Grade    Varchar2,
                                   Out_Cursor Out Ref_Cursor) Is
  Begin
    Open Out_Cursor For
      Select Num, Name, Age From Student Where Grade = p_Grade;
  End Find_Students_By_Grade;
End Pkg_School;

Java 代码：

package callprocedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import org.apache.log4j.Logger;

import junit.framework.TestCase;

public class DAOTest extends TestCase {
	// private JdbcTemplate jdbcTemplate;
	private Logger logger = Logger.getLogger(DAOTest.class);

	private String url;
	private String username;
	private String password;
	private String driver;
	private Connection con;
	private CallableStatement cs;
	private ResultSet rs;
	private String grade;

	public void setUp() throws Exception {
		super.setUp();
		grade = "101";

		url = "jdbc:oracle:thin:@127.0.0.1:1521:oradb";
		username = "ora";
		password = "ora";
		driver = "oracle.jdbc.driver.OracleDriver";

		Class.forName(driver);
		con = DriverManager.getConnection(url, username, password);
	}

	public void tearDown() throws Exception {
		super.tearDown();
		rs = null;
		cs = null;
		con = null;
	}

	public void testGetStudentByGrade() throws Exception {
		cs = con.prepareCall("call pkg_school.find_students_by_grade(?, ?)");
		cs.setString(1, grade);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();

		rs = (ResultSet) cs.getObject(2);
		ResultSetMetaData rsmd = rs.getMetaData();

		String columnName;
		String columnValue;
		while (rs.next()) {
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				columnName = rsmd.getColumnName(i);
				columnValue = rs.getString(columnName);
				logger.debug(columnName + "=" + columnValue);
			}
			logger.debug("---------------------------");
		}
	}
}

JUnit 测试结果：

DAOTest - NUM=100001
DAOTest - NAME=Jack
DAOTest - AGE=20
DAOTest - ---------------------------
DAOTest - NUM=100002
DAOTest - NAME=Merry
DAOTest - AGE=19
DAOTest - ---------------------------

final Map result = new HashMap();
		
Object obj = jdbcTemplate.execute("call pkg_school.find_students_by_grade(?, ?)", new CallableStatementCallback(){

	public Object doInCallableStatement(CallableStatement cs)
			throws SQLException, DataAccessException {
		cs.setString(1, grade);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		
		rs = (ResultSet) cs.getObject(2);
		ResultSetMetaData rsmd = rs.getMetaData();
		}
});

第二个方法我就写个大概了，跟第一个方法的区别就是用的是 spring 的 jdbcTemplate 来执行的，这样的话连接这块就不要自己去获取了，要在 spring 的配置文件里面添加相关的配置。