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