SPRING 調用ORACLE預存程序

 1.建立表

create table TEST_USERS<br />(<br /> USER_ID VARCHAR2(10) not null,<br /> NAME VARCHAR2(10) not null,<br /> PASSWORD VARCHAR2(20) not null<br />)

2.建立預存程序

create or replace package display_users_package is<br /> type search_results is ref cursor;<br /> procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type);<br />end display_users_package; </p><p>create or replace package body display_users_package is<br /> procedure display_users_proc(results_out out search_results, userId in test_users.user_id%type)<br /> is<br /> begin<br /> if userId is not null then<br /> open results_out for select * from test_users where user_id like userId || '%';<br /> else<br /> open results_out for select * from test_users;<br /> end if;<br /> end display_users_proc;<br />end display_users_package;<br />

3.　完整實現代碼：

import java.sql.CallableStatement;<br />import java.sql.Connection;<br />import java.sql.ResultSet;<br />import java.sql.SQLException;<br />import java.util.ArrayList;<br />import java.util.HashMap;<br />import java.util.List;<br />import java.util.Map; </p><p>import javax.sql.DataSource; </p><p>import oracle.jdbc.OracleTypes; </p><p>import org.springframework.dao.DataAccessException;<br />import org.springframework.jdbc.core.CallableStatementCallback;<br />import org.springframework.jdbc.core.CallableStatementCreator;<br />import org.springframework.jdbc.core.JdbcTemplate; </p><p>import com.spring.stored.procedure.util.DataContextUtil; </p><p>/**<br /> * @author Jane Jiao<br /> *<br /> */<br />public class SpringStoredProce { </p><p> public List<Map> execute(String storedProc, String params){<br /> List<Map> resultList = null;<br /> try{<br /> final DataSource ds = DataContextUtil.getInstance().getDataSource();<br /> final JdbcTemplate template = new JdbcTemplate(ds);<br /> resultList = (List<Map>)template.execute(new ProcCallableStatementCreator(storedProc, params),<br /> new ProcCallableStatementCallback());<br /> }catch(DataAccessException e){<br /> throw new RuntimeException("execute method error : DataAccessException " + e.getMessage());<br /> }<br /> return resultList;<br /> } </p><p> /**<br /> * Create a callable statement in this connection.<br /> */<br /> private class ProcCallableStatementCreator implements CallableStatementCreator {<br /> private String storedProc;<br /> private String params; </p><p> /**<br /> * Constructs a callable statement.<br /> * @param storedProc The stored procedure's name.<br /> * @param params Input parameters.<br /> * @param outResultCount count of output result set.<br /> */<br /> public ProcCallableStatementCreator(String storedProc, String params) {<br /> this.params = params;<br /> this.storedProc = storedProc;<br /> } </p><p> /**<br /> * Returns a callable statement<br /> * @param conn Connection to use to create statement<br /> * @return cs A callable statement<br /> */<br /> public CallableStatement createCallableStatement(Connection conn) {<br /> StringBuffer storedProcName = new StringBuffer("call ");<br /> storedProcName.append(storedProc + "(");<br /> //set output parameters<br /> storedProcName.append("?");<br /> storedProcName.append(", "); </p><p> //set input parameters<br /> storedProcName.append("?");<br /> storedProcName.append(")"); </p><p> CallableStatement cs = null;<br /> try {<br /> // set the first parameter is OracleTyep.CURSOR for oracel stored procedure<br /> cs = conn.prepareCall(storedProcName.toString());<br /> cs.registerOutParameter (1, OracleTypes.CURSOR);<br /> // set the sencond paramter<br /> cs.setObject(2, params);<br /> } catch (SQLException e) {<br /> throw new RuntimeException("createCallableStatement method Error : SQLException " + e.getMessage());<br /> }<br /> return cs;<br /> } </p><p> } </p><p> /**<br /> *<br /> * The ProcCallableStatementCallback return a result object,<br /> * for example a collection of domain objects.<br /> *<br /> */<br /> private class ProcCallableStatementCallback implements CallableStatementCallback { </p><p> /**<br /> * Constructs a ProcCallableStatementCallback.<br /> */<br /> public ProcCallableStatementCallback() {<br /> } </p><p> /**<br /> * Returns a List(Map) collection.<br /> * @param cs object that can create a CallableStatement given a Connection<br /> * @return resultsList a result object returned by the action, or null<br /> */<br /> public Object doInCallableStatement(CallableStatement cs){<br /> List<Map> resultsMap = new ArrayList<Map>();<br /> try {<br /> cs.execute();<br /> ResultSet rs = (ResultSet) cs.getObject(1);<br /> while (rs.next()) {<br /> Map<String, String> rowMap = new HashMap<String, String>();<br /> rowMap.put("userId", rs.getString("USER_ID"));<br /> rowMap.put("name", rs.getString("NAME"));<br /> rowMap.put("password", rs.getString("PASSWORD"));<br /> resultsMap.add(rowMap);<br /> }<br /> rs.close();<br /> }catch(SQLException e) {<br /> throw new RuntimeException("doInCallableStatement method error : SQLException " + e.getMessage());<br /> }<br /> return resultsMap;<br /> }<br /> }<br />}<br />

4.　測試代碼，在這裡使用了Junit4測試：
import static org.junit.Assert.assertNotNull;<br />import static org.junit.Assert.assertTrue; </p><p>import java.util.List;<br />import java.util.Map; </p><p>import org.junit.After;<br />import org.junit.Before;<br />import org.junit.Test; </p><p>/**<br /> * @author Jane Jiao<br /> *<br /> */<br />public class SpringStoredProceTest { </p><p> private SpringStoredProce springStoredProce; </p><p> /**<br /> * @throws java.lang.Exception<br /> */<br /> @Before<br /> public void setUp() throws Exception {<br /> springStoredProce = new SpringStoredProce();<br /> } </p><p> /**<br /> * @throws java.lang.Exception<br /> */<br /> @After<br /> public void tearDown() throws Exception {<br /> springStoredProce = null;<br /> } </p><p> /**<br /> * Test method for {@link com.hactl.listingframework.dao.SpringStoredProce#execute(java.lang.String, java.lang.String)}.<br /> */<br /> @Test<br /> public void testExecute() {<br /> final String storedProcName = "display_users_package.display_users_proc";<br /> final String param = "test";<br /> List<Map> resultList = springStoredProce.execute(storedProcName, param);<br /> assertNotNull(resultList);<br /> assertTrue(resultList.size() > 0);<br /> for (int i = 0; i < resultList.size(); i++) {<br /> Map rowMap = resultList.get(i);<br /> final String userId = rowMap.get("userId").toString();<br /> final String name = rowMap.get("name").toString();<br /> final String password = rowMap.get("password").toString();<br /> System.out.println("USER_ID=" + userId + "/t name=" + name + "/t password=" + password);<br /> } </p><p> }<br />}<br />

5. 測試的輸出結果：

USER_ID=test1 name=aa password=aa<br />USER_ID=test2 name=bb password=bb<br />USER_ID=test3 name=cc password=cc