這是總結(jié)以前使用spring調(diào)用Oracle存儲過程，并用cursor返回結(jié)果集的一個完整實例，希望能對大家有幫助。
1. 創(chuàng)建表：
代碼
- create table TEST_USERS
- (
- USER_ID VARCHAR2(10) not null,
- NAME VARCHAR2(10) not null,
- PASSWORD VARCHAR2(20) not null
- )
2. 創(chuàng)建存儲過程：
代碼
- create or replace package display_users_package is
- type search_results is ref cursor;
- procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type);
- end display_users_package;
-
- create or replace package body display_users_package is
- procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type)
- is
- begin
- if userId is not null then
- open results_out for select * from test_users where user_id like userId || '%';
- else
- open results_out for select * from test_users;
- end if;
- end display_users_proc;
- end display_users_package;
這個results_out是一個游標類型，用來返回查找的結(jié)果集。
3. 完整實現(xiàn)代碼：
代碼
- 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 javax.sql.DataSource;
-
- import oracle.jdbc.OracleTypes;
-
- import org.springframework.dao.DataAccessException;
- import org.springframework.jdbc.core.CallableStatementCallback;
- import org.springframework.jdbc.core.CallableStatementCreator;
- import org.springframework.jdbc.core.JdbcTemplate;
-
- import com.spring.stored.procedure.util.DataContextUtil;
-
-
-
-
-
- public class SpringStoredProce {
-
- public List<Map> execute(String storedProc, String params){
- List<Map> resultList = null;
- try{
- final DataSource ds = DataContextUtil.getInstance().getDataSource();
- final JdbcTemplate template = new JdbcTemplate(ds);
- resultList = (List<Map>)template.execute(new ProcCallableStatementCreator(storedProc, params),
- new ProcCallableStatementCallback());
- }catch(DataAccessException e){
- throw new RuntimeException("execute method error : DataAccessException " + e.getMessage());
- }
- return resultList;
- }
-
-
-
-
-
- private class ProcCallableStatementCreator implements CallableStatementCreator {
- private String storedProc;
- private String params;
-
-
-
-
-
-
-
-
- public ProcCallableStatementCreator(String storedProc, String params) {
- this.params = params;
- this.storedProc = storedProc;
- }
-
-
-
-
-
-
- public CallableStatement createCallableStatement(Connection conn) {
- StringBuffer storedProcName = new StringBuffer("call ");
- storedProcName.append(storedProc + "(");
-
- storedProcName.append("?");
- storedProcName.append(", ");
-
-
- storedProcName.append("?");
- storedProcName.append(")");
-
- CallableStatement cs = null;
- try {
-
- cs = conn.prepareCall(storedProcName.toString());
- cs.registerOutParameter (1, OracleTypes.CURSOR);
-
- cs.setObject(2, params);
- } catch (SQLException e) {
- throw new RuntimeException("createCallableStatement method Error : SQLException " + e.getMessage());
- }
- return cs;
- }
-
- }
-
-
-
-
-
-
-
- private class ProcCallableStatementCallback implements CallableStatementCallback {
-
-
-
-
- public ProcCallableStatementCallback() {
- }
-
-
-
-
-
-
- public Object doInCallableStatement(CallableStatement cs){
- List<Map> resultsMap = new ArrayList<Map>();
- try {
- cs.execute();
- ResultSet rs = (ResultSet) cs.getObject(1);
- while (rs.next()) {
- Map<String, String> rowMap = new HashMap<String, String>();
- rowMap.put("userId", rs.getString("USER_ID"));
- rowMap.put("name", rs.getString("NAME"));
- rowMap.put("password", rs.getString("PASSWORD"));
- resultsMap.add(rowMap);
- }
- rs.close();
- }catch(SQLException e) {
- throw new RuntimeException("doInCallableStatement method error : SQLException " + e.getMessage());
- }
- return resultsMap;
- }
- }
- }
4. 測試代碼，在這里使用了Junit4測試：
代碼
- import static org.junit.Assert.assertNotNull;
- import static org.junit.Assert.assertTrue;
-
- import java.util.List;
- import java.util.Map;
-
- import org.junit.After;
- import org.junit.Before;
- import org.junit.Test;
-
-
-
-
-
- public class SpringStoredProceTest {
-
- private SpringStoredProce springStoredProce;
-
-
-
-
- @Before
- public void setUp() throws Exception {
- springStoredProce = new SpringStoredProce();
- }
-
-
-
-
- @After
- public void tearDown() throws Exception {
- springStoredProce = null;
- }
-
-
-
-
- @Test
- public void testExecute() {
- final String storedProcName = "display_users_package.display_users_proc";
- final String param = "test";
- List<Map> resultList = springStoredProce.execute(storedProcName, param);
- assertNotNull(resultList);
- assertTrue(resultList.size() > 0);
- for (int i = 0; i < resultList.size(); i++) {
- Map rowMap = resultList.get(i);
- final String userId = rowMap.get("userId").toString();
- final String name = rowMap.get("name").toString();
- final String password = rowMap.get("password").toString();
- System.out.println("USER_ID=" + userId + "\t name=" + name + "\t password=" + password);
- }
-
- }
- }
5. 測試的輸出結(jié)果：
代碼
- USER_ID=test1 name=aa password=aa
- USER_ID=test2 name=bb password=bb
- USER_ID=test3 name=cc password=cc