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

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

1. 创建表：

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

2. 创建存储过程：

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.　完整实现代码：

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测试：

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. 测试的输出结果：

USER_ID=test1    name=aa    password=aa 
USER_ID=test2    name=bb    password=bb 
USER_ID=test3    name=cc    password=cc

為什麼不用JdbcTemplate.call()呢? 也會回傳Map，有什麼特殊原因嗎?

请参考：
http://developer.spikesource.com/errorbuddy/source/spring-framework-1.0.2-with-dependencies/docs/api/org/springframework/jdbc/core/class-use/CallableStatementCreator.html

java.lang.Object 	JdbcTemplate.execute(CallableStatementCreator csc, CallableStatementCallback action)

java.util.Map 	JdbcTemplate.call(CallableStatementCreator csc, java.util.List declaredParameters) 

它们返回的值是不同的，这就要根据你想要返回的结果来决定了!

对不起，刚刚才看到你发的贴!

因为在上面的示例中我要返回一个List<Map>，也就是说是包含了多行多列的一个结果集，所以我用了JdbcTemplate.execute()中的ProcCallableStatementCallback封装这个结果集，而JdbcTemplate.call()仅返回一个Map，也就是说它只能是包含一行数据。

要想对List里面的map进行排序,大家会怎么做呢?

List里面的数据已经是有序，　而List里面的Map仅仅代表查寻到的一行数据，为什么要对一行数据进行排序呢？

如果你想得到的List是一个经过排序的结果集的话，你只需要修改你的存储过程就可以了，加上order by 条件就可以了.