spring调用Oracle存储过程,并返回结果集的完整实例

这是总结以前使用spring调用Oracle存储过程，并用cursor返回结果集的一个完整实例，希望能对大家有帮助。 

1. 创建表： 

Java代码  

1. create table TEST_USERS   
2. (   
3.   USER_ID  VARCHAR2(10) not null,   
4.   NAME     VARCHAR2(10) not null,   
5.   PASSWORD VARCHAR2(20) not null   
6. )  

2. 创建存储过程： 

Java代码  

1. create or replace package display_users_package is   
2.      type search_results is ref cursor;   
3.      procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type);   
4. end display_users_package;   
5.   
6. create or replace package body display_users_package is   
7.      procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type)   
8.           is   
9.           begin   
10.           if userId is not null then   
11.               open results_out for select * from test_users where user_id like userId || '%';   
12.           else   
13.               open results_out for  select * from test_users;   
14.           end if;   
15.       end display_users_proc;   
16. end display_users_package;  

这个results_out是一个游标类型，用来返回查找的结果集。 

3.　完整实现代码： 

Java代码  

1. import java.sql.CallableStatement;  
2. import java.sql.Connection;  
3. import java.sql.ResultSet;  
4. import java.sql.SQLException;  
5. import java.util.ArrayList;  
6. import java.util.HashMap;  
7. import java.util.List;  
8. import java.util.Map;  
9.   
10. import javax.sql.DataSource;  
11.   
12. import oracle.jdbc.OracleTypes;  
13.   
14. import org.springframework.dao.DataAccessException;  
15. import org.springframework.jdbc.core.CallableStatementCallback;  
16. import org.springframework.jdbc.core.CallableStatementCreator;  
17. import org.springframework.jdbc.core.JdbcTemplate;  
18.   
19. import com.spring.stored.procedure.util.DataContextUtil;  
20.   
21. /** 
22.  * @author Jane Jiao 
23.  * 
24.  */  
25. public class SpringStoredProce {  
26.       
27.     public List<Map> execute(String storedProc, String params){  
28.         List<Map> resultList = null;  
29.         try{  
30.             final DataSource ds = DataContextUtil.getInstance().getDataSource();  
31.             final JdbcTemplate template = new JdbcTemplate(ds);  
32.             resultList = (List<Map>)template.execute(new ProcCallableStatementCreator(storedProc, params),  
33.                                                      new ProcCallableStatementCallback());  
34.         }catch(DataAccessException e){  
35.             throw new RuntimeException("execute method error : DataAccessException " + e.getMessage());  
36.         }  
37.          return resultList;  
38.     }  
39.       
40.       
41.     /** 
42.      * Create a callable statement in this connection. 
43.      */  
44.     private class ProcCallableStatementCreator implements CallableStatementCreator {  
45.         private String storedProc;  
46.         private String params;  
47.           
48.       
49.         /** 
50.          * Constructs a callable statement. 
51.          * @param storedProc                  The stored procedure's name. 
52.          * @param params                      Input parameters. 
53.          * @param outResultCount              count of output result set. 
54.          */  
55.         public ProcCallableStatementCreator(String storedProc, String params) {  
56.             this.params = params;  
57.             this.storedProc = storedProc;  
58.         }  
59.           
60.         /** 
61.          * Returns a callable statement 
62.          * @param conn          Connection to use to create statement 
63.          * @return cs           A callable statement 
64.          */  
65.         public CallableStatement createCallableStatement(Connection conn) {  
66.             StringBuffer storedProcName = new StringBuffer("call ");  
67.             storedProcName.append(storedProc + "(");  
68.             //set output parameters  
69.             storedProcName.append("?");  
70.             storedProcName.append(", ");  
71.               
72.             //set input parameters  
73.             storedProcName.append("?");  
74.             storedProcName.append(")");  
75.   
76.             CallableStatement cs = null;  
77.             try {  
78.                 // set the first parameter is OracleTyep.CURSOR for oracel stored procedure  
79.                 cs = conn.prepareCall(storedProcName.toString());  
80.                 cs.registerOutParameter (1, OracleTypes.CURSOR);  
81.                // set the sencond paramter  
82.                 cs.setObject(2, params);  
83.             } catch (SQLException e) {  
84.                 throw new RuntimeException("createCallableStatement method Error : SQLException " + e.getMessage());  
85.             }  
86.             return cs;  
87.         }  
88.           
89.     }  
90.       
91.     /** 
92.      *  
93.      * The ProcCallableStatementCallback return a result object,  
94.      * for example a collection of domain objects. 
95.      * 
96.      */  
97.     private class ProcCallableStatementCallback implements CallableStatementCallback {  
98.           
99.         /** 
100.          * Constructs a ProcCallableStatementCallback. 
101.          */  
102.         public ProcCallableStatementCallback() {  
103.         }  
104.   
105.         /** 
106.          * Returns a List(Map) collection. 
107.          * @param cs                       object that can create a CallableStatement given a Connection 
108.          * @return resultsList             a result object returned by the action, or null 
109.          */  
110.         public Object doInCallableStatement(CallableStatement cs){  
111.             List<Map> resultsMap =  new ArrayList<Map>();  
112.             try {  
113.                 cs.execute();   
114.                 ResultSet rs = (ResultSet) cs.getObject(1);  
115.                 while (rs.next()) {  
116.                     Map<String, String> rowMap = new HashMap<String, String>();  
117.                     rowMap.put("userId", rs.getString("USER_ID"));  
118.                     rowMap.put("name", rs.getString("NAME"));  
119.                     rowMap.put("password", rs.getString("PASSWORD"));  
120.                     resultsMap.add(rowMap);  
121.                 }     
122.                 rs.close();  
123.             }catch(SQLException e) {  
124.                 throw new RuntimeException("doInCallableStatement method error : SQLException " + e.getMessage());  
125.             }  
126.             return resultsMap;  
127.        }  
128.     }  
129. }  

4.　测试代码，在这里使用了Junit4测试： 

Java代码  

1. import static org.junit.Assert.assertNotNull;   
2. import static org.junit.Assert.assertTrue;   
3.   
4. import java.util.List;   
5. import java.util.Map;   
6.   
7. import org.junit.After;   
8. import org.junit.Before;   
9. import org.junit.Test;   
10.   
11. /**  
12.  * @author Jane Jiao  
13.  *  
14.  */   
15. public class SpringStoredProceTest {   
16.       
17.    private SpringStoredProce springStoredProce;   
18.   
19.    /**  
20.     * @throws java.lang.Exception  
21.     */   
22.    @Before   
23.    public void setUp() throws Exception {   
24.       springStoredProce = new SpringStoredProce();   
25.    }   
26.   
27.    /**  
28.     * @throws java.lang.Exception  
29.     */   
30.    @After   
31.    public void tearDown() throws Exception {   
32.       springStoredProce = null;   
33.    }   
34.   
35.    /**  
36.     * Test method for {@link com.hactl.listingframework.dao.SpringStoredProce#execute(java.lang.String, java.lang.String)}.  
37.     */   
38.    @Test   
39.    public void testExecute() {   
40.       final String storedProcName = "display_users_package.display_users_proc";   
41.       final String param = "test";   
42.       List<Map> resultList = springStoredProce.execute(storedProcName, param);   
43.       assertNotNull(resultList);   
44.       assertTrue(resultList.size() > 0);   
45.       for (int i = 0; i < resultList.size(); i++) {   
46.          Map rowMap = resultList.get(i);   
47.          final String userId = rowMap.get("userId").toString();   
48.          final String name = rowMap.get("name").toString();   
49.          final String password = rowMap.get("password").toString();   
50.          System.out.println("USER_ID=" + userId + "\t name=" + name + "\t password=" + password);   
51.       }   
52.          
53.    }   
54. }  

5. 测试的输出结果：

Java代码  

1. USER_ID=test1    name=aa    password=aa   
2. USER_ID=test2    name=bb    password=bb   
3. USER_ID=test3    name=cc    password=cc