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 的配置文件里面添加相关的配置。
