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

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

我的异常网推荐解决方案：oracle存储过程,http://www..net/oracle-develop/177537.html