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;
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;
大哥,我用的mysql数据库啊,而且我的存储过程不带参数的,存储过程的代码如下DELIMITER $$DROP PROCEDURE IF EXISTS `course`.`member` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `member`() BEGINselect t.tid,t.mnumber,t.snumber,t.tleader,t.tname,t.title, group_concat(concat(concat(s.name,' ')) separator ", ") as members,t.torder from team t left join students s on t.tid = s.tid group by t.tid;END $$DELIMITER ;
代码
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);
}
}
}
5. 测试的输出结果: 代码
USER_ID=test1 name=aa password=aa
USER_ID=test2 name=bb password=bb
USER_ID=test3 name=cc password=cc
CREATE DEFINER=`root`@`localhost` PROCEDURE `member`()
BEGINselect t.tid,t.mnumber,t.snumber,t.tleader,t.tname,t.title, group_concat(concat(concat(s.name,' ')) separator ", ") as members,t.torder
from team t left join students s on t.tid = s.tid
group by t.tid;END $$DELIMITER ;
java.sql.PreparedStatement ps = null;
java.sql.ResultSet rs = null;
List list=new ArrayList();
try{
con = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
String sql="{call member()}";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData meta=rs.getMetaData();
int columnCount=meta.getColumnCount();
Map rowData=new HashMap();
while(rs.next()){
rowData=new HashMap(columnCount);
for(int i=1;i<=columnCount;i++){
rowData.put(meta.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
return list;
}catch(Exception e){
return null;
}