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
)

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;

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

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);
      }

   }
}

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