Spring的SimpleJdbcTemplate将存储过程的调用进行了良好的封装,下面列出使用JdbcTemplate调用Oracle存储过程的三种情况:
一、无返回值的存储过程调用
1、存储过程代码:
create or replace procedure sp_insert_table(param1 in varchar2,param2 in varchar2) as
begin
insert into table MyTable (id,name) values ('param1 ','param2');
end sp_insert_table;
2、JdbcTemplate调用该存储过程代码:
package com.dragon.test;
import org.springframework.jdbc.core.JdbcTemplate;
public class JdbcTemplateTest {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void test(){
this.jdbcTemplate.execute("call sp_insert_table('100001')");
}
}
二、有返回值的存储过程(非结果集)
1、存储过程代码:
create or replace procedure sp_select_table (param1 in varchar2,param2 out varchar2) as
begin select into param2 from MyTable where ID = param1 ;
end sp_insert_table ;
2、JdbcTemplate调用该存储过程代码:
public void test() {
String param2Value = (String) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call sp_select_table (?,?)}";// 调用的sql
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1, "p1");// 设置输入参数的值
cs.registerOutParameter(2,OracleTypes.Varchar);// 注册输出参数的类型
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.execute();
return cs.getString(2);// 获取输出参数的值
}
});
}
三、有返回值的存储过程(结果集)
1、存储过程代码:先创建程序包,因为Oracle存储过程所有返回值都是通过out参数返回的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用package:
CREATE OR REPLACE PACKAGE Pkg_Type_Gj IS
type Gj_Ref_Cursor is ref CURSOR;
type GjWX_Ref_Cursor is ref CURSOR;
END Pkg_Type_Gj;
2、存储过程代码:可以看到,列表是通过把游标作为一个out参数来返回的。
create or replace procedure Sp_Gj_Table(param1 in varchar2,param2 out Pkg_Type_Gj.Gj_Ref_Cursor) is
Vs_Tabsql VARCHAR2(2000);
begin
Vs_Tabsql :='SELECT ' || param1 || ' as datetime, ROWNUM,GG.ORGANAMEPR,GG.ORGAID,GG.ORGANAME,GG.METID,GG.METNAME ' ||
'FROM (SELECT G.*, T.METID, T.METNAME ' ||
'FROM AMET T ' ||
'RIGHT JOIN (SELECT (SELECT B.ORGANAME ' ||
'FROM AORGA B ' ||
'WHERE B.ORGAID = A.PREID) ORGANAMEPR, ' ||
'A.ORGAID, ' ||
'A.ORGANAME ' ||
'FROM AORGA A ' ||
'WHERE LEVEL = 2 ' ||
'START WITH A.ORGAID IN (10005,10002,10003,10004, ' ||
'10292,10317,10318,10319, ' ||
'10320,10321,10322,10323) ' ||
'CONNECT BY PRIOR A.ORGAID = A.PREID) G ' ||
'ON T.ORGAID = G.ORGAID) GG ' ||
'LEFT JOIN ' ||
'(SELECT TT.METID, TT.VALTYPE, TT.REPDATA ' ||
'FROM DRAW'||param1|| ' TT ' ||
'WHERE TT.VALTYPE <= 2 ' ||
'AND TO_CHAR(TT.DATATIME, ''HH24'') = ''00'' ' ||
'AND TO_CHAR(TT.DATATIME, ''MI'') = ''00'') TTT ' ||
'ON GG.METID = TTT.METID ' ||
'WHERE TTT.METID IS NULL ';
open param2 for Vs_Tabsql;
end Sp_Gj_Table;
3、JdbcTemplate调用该存储过程代码:
public List getcallwxgj(String datatimewx){
if(datatimewx!=null&&datatimewx!=""){
datatimewx = DateUtils.getString(DateUtils.add(DateUtils.getDate(datatimewx), Calendar.DAY_OF_MONTH,0),"yyyyMMdd");
System.out.println(datatimewx);
}else{
datatimewx = "2014-05-12";
datatimewx= DateUtils.getString(DateUtils.add(DateUtils.getDate(datatimewx), Calendar.DAY_OF_MONTH,0),"yyyyMMdd");
}
System.out.println(datatimewx);
final String nihao = datatimewx;
List resultList = (List) jdbcTemplate.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call Sp_Gjwx_Table(?,?)}";// 调用的sql
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1,nihao);// 设置输入参数的值
cs.registerOutParameter(2,OracleTypes.CURSOR);// 注册输出参数的类型
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
List resultsMap = new ArrayList();
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值
while (rs.next()) {// 转换每行的返回值到Map中
Map rowMap = new HashMap();
rowMap.put("unitname", rs.getString("unitname"));
rowMap.put("organame", rs.getString("organame"));
rowMap.put("metid", rs.getString("metid"));
rowMap.put("metname", rs.getString("metname"));
rowMap.put("valtype", rs.getString("valtype"));
rowMap.put("starttime", rs.getString("starttime"));
rowMap.put("startread", rs.getString("startread"));
rowMap.put("rawtype1", rs.getString("rawtype1"));
rowMap.put("valid1", rs.getString("valid1"));
rowMap.put("endtime", rs.getString("endtime"));
rowMap.put("endread", rs.getString("endread"));
rowMap.put("rawtype2", rs.getString("rawtype2"));
rowMap.put("valid2", rs.getString("valid2"));
resultsMap.add(rowMap);
}
rs.close();
return resultsMap;
}
});
System.out.println(resultList);
return resultList;
}