spring事务oracle存储过程,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 execute(String storedProc, String params){

List resultList = null;

try{

final DataSource ds = DataContextUtil.getInstance().getDataSource();

final JdbcTemplate template = new JdbcTemplate(ds);

resultList = (List)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 resultsMap = new ArrayList();

try {

cs.execute();

ResultSet rs = (ResultSet) cs.getObject(1);

while (rs.next()) {

Map rowMap = new HashMap();

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