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. )  

create table TEST_USERS 
( 
  USER_ID  VARCHAR2(10) not null, 
  NAME     VARCHAR2(10) not null, 
  PASSWORD VARCHAR2(20) not null 
)

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;  

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是一个游标类型，用来返回查找的结果集。

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. }  

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;

/**
 * @author Jane Jiao
 *
 */
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;
	}
	
	
	/**
	 * Create a callable statement in this connection.
	 */
	private class ProcCallableStatementCreator implements CallableStatementCreator {
		private String storedProc;
		private String params;
		
	
		/**
		 * Constructs a callable statement.
		 * @param storedProc                  The stored procedure's name.
		 * @param params                      Input parameters.
		 * @param outResultCount              count of output result set.
		 */
		public ProcCallableStatementCreator(String storedProc, String params) {
			this.params = params;
			this.storedProc = storedProc;
		}
		
		/**
		 * Returns a callable statement
		 * @param conn          Connection to use to create statement
		 * @return cs           A callable statement
		 */
		public CallableStatement createCallableStatement(Connection conn) {
			StringBuffer storedProcName = new StringBuffer("call ");
			storedProcName.append(storedProc + "(");
			//set output parameters
			storedProcName.append("?");
			storedProcName.append(", ");
			
			//set input parameters
			storedProcName.append("?");
			storedProcName.append(")");

			CallableStatement cs = null;
			try {
		        // set the first parameter is OracleTyep.CURSOR for oracel stored procedure
				cs = conn.prepareCall(storedProcName.toString());
				cs.registerOutParameter (1, OracleTypes.CURSOR);
			   // set the sencond paramter
		    	cs.setObject(2, params);
			} catch (SQLException e) {
				throw new RuntimeException("createCallableStatement method Error : SQLException " + e.getMessage());
			}
		    return cs;
		}
		
	}
	
	/**
	 * 
	 * The ProcCallableStatementCallback return a result object, 
	 * for example a collection of domain objects.
	 *
	 */
	private class ProcCallableStatementCallback implements CallableStatementCallback {
		
		/**
		 * Constructs a ProcCallableStatementCallback.
		 */
		public ProcCallableStatementCallback() {
		}

		/**
		 * Returns a List(Map) collection.
		 * @param cs                       object that can create a CallableStatement given a Connection
		 * @return resultsList             a result object returned by the action, or null
		 */
		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测试：

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. }  

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; 

/** 
 * @author Jane Jiao 
 * 
 */ 
public class SpringStoredProceTest { 
    
   private SpringStoredProce springStoredProce; 

   /** 
    * @throws java.lang.Exception 
    */ 
   @Before 
   public void setUp() throws Exception { 
      springStoredProce = new SpringStoredProce(); 
   } 

   /** 
    * @throws java.lang.Exception 
    */ 
   @After 
   public void tearDown() throws Exception { 
      springStoredProce = null; 
   } 

   /** 
    * Test method for {@link com.hactl.listingframework.dao.SpringStoredProce#execute(java.lang.String, java.lang.String)}. 
    */ 
   @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. 测试的输出结果：

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