SPRING 调用ORACLE存储过程

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;  

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