spring调用Oracle存储过程,并返回结果集的完整实例

最新推荐文章于 2023-10-09 11:06:09 发布

birdme007

最新推荐文章于 2023-10-09 11:06:09 发布

阅读量1.7k

点赞数

分类专栏： Struts2+Spring2+hibernate3 文章标签： spring oracle 存储 import exception null

Struts2+Spring2+hibernate3 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

这是总结以前使用spring调用Oracle存储过程，并用cursor返回结果集的一个完整实例，希望能对大家有帮助。

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;

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

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