Java代码中执行存储过程和函数

最新推荐文章于 2021-09-18 14:08:32 发布

狂奔的蜗牛已被占用

最新推荐文章于 2021-09-18 14:08:32 发布

阅读量322

点赞数

一：先看几个概念：

首先，自己参考了几篇文章，写的很不错，自己也借鉴一下，然后会在最后贴出自己在项目中使用到的存储过程，已经实现过程，大家可以做个对比，实现方法不同。http://www.cnblogs.com/liunanjava/p/4261242.html

过程和函数，它们被编译后保存在数据库中，称为持久性存储模块（Persistent Stored Module，PSM），可以反复调用，运行速度快。不同之处是函数必须指定返回类型。

Java调用结构：

存储过程：{call <procedure-name>[(<arg1>,<arg2>, …)]}
函数：{?= call <procedure-name>[(<arg1>,<arg2>, …)]}。

如果要调用存储过程，则使用第一种语法，就是开头不带问号的语法，call 后面是过程名，
如果没有参数，可以省略小括号。
如果要调用函数，则使用第二种语法，开头带有一个问号加等号，实际上这个问号就是一个占位符，这个问号总是调用函数的第一个占位符。其它部分与过程的语法相同。

二、CallableStatement 执行存储过程：

2.1、建立基类

[java] view plain copy

print ?

package com.pb.emp.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.pb.emp.untily.ConfigManager;
public class BaseDao {
protected Connection conn;
protected PreparedStatement ps;
protected ResultSet rs;
//建立连接
public boolean getConnection(){
String driver=ConfigManager.getInstance().getString(”jdbc.driver_class”);
String url=ConfigManager.getInstance().getString(”jdbc.connection.url”);
String username=ConfigManager.getInstance().getString(”jdbc.connection.username”);
String password=ConfigManager.getInstance().getString(”jdbc.connection.password”);
try {
Class.forName(driver);
conn=DriverManager.getConnection(url,username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
return true;
}
//增加，修改，删除
public int executeUpdate(String sql, Object[] params){
getConnection();
int updateRow=0;
try {
ps=conn.prepareStatement(sql);
//填充占位符
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
updateRow = ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return updateRow;
}
//
//查询
public ResultSet executeSQL(String sql, Object[] params){
getConnection();
try {
ps=conn.prepareStatement(sql);
//填充占位符
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
rs = ps.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
// 关闭资源
public boolean closeResource() {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
return true;
}
}

package com.pb.emp.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.pb.emp.untily.ConfigManager; public class BaseDao { protected Connection conn; protected PreparedStatement ps; protected ResultSet rs; //建立连接 public boolean getConnection(){ String driver=ConfigManager.getInstance().getString(“jdbc.driver_class”); String url=ConfigManager.getInstance().getString(“jdbc.connection.url”); String username=ConfigManager.getInstance().getString(“jdbc.connection.username”); String password=ConfigManager.getInstance().getString(“jdbc.connection.password”); try { Class.forName(driver); conn=DriverManager.getConnection(url,username, password); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } return true; } //增加，修改，删除 public int executeUpdate(String sql, Object[] params){ getConnection(); int updateRow=0; try { ps=conn.prepareStatement(sql); //填充占位符 for(int i=0;i<params.length;i++){ ps.setObject(i+1, params[i]); } updateRow = ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return updateRow; } // //查询 public ResultSet executeSQL(String sql, Object[] params){ getConnection(); try { ps=conn.prepareStatement(sql); //填充占位符 for(int i=0;i<params.length;i++){ ps.setObject(i+1, params[i]); } rs = ps.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rs; } // 关闭资源 public boolean closeResource() { if(rs!=null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } } if(ps!=null){ try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } } return true; } }

2.2、执行不带参但是有返回值的存储过程

下面建立存储过程

[sql] view plain copy

print ?

–查询emp表记录数
CREATE OR REPLACE PROCEDURE getEmpCount(v_count OUT NUMBER)
AS
BEGIN
SELECT COUNT(*) INTO v_count FROM emp;
END;

–查询emp表记录数 CREATE OR REPLACE PROCEDURE getEmpCount(v_count OUT NUMBER) AS BEGIN SELECT COUNT(*) INTO v_count FROM emp; END;

调用

[java] view plain copy

print ?

//执行不带参但是有返回值的存储过程获取emp表总记录数
public int getTotalCountProc(){
//定义一个变量来接收结果
int totalCount=0;
//声明CallableStatement对象
CallableStatement proc=null;
String sql=”{call getEmpCount(?)}”;
try {
//建立连接
getConnection();
//CallableStatement对象
proc=conn.prepareCall(sql);
//将数据库对象数据类型注册为java中的类型
proc.registerOutParameter(1, Types.INTEGER);
//执行
proc.execute();
//接收返回值
totalCount=proc.getInt(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return totalCount;
}

//执行不带参但是有返回值的存储过程获取emp表总记录数 public int getTotalCountProc(){ //定义一个变量来接收结果 int totalCount=0; //声明CallableStatement对象 CallableStatement proc=null; String sql=”{call getEmpCount(?)}”; try { //建立连接 getConnection(); //CallableStatement对象 proc=conn.prepareCall(sql); //将数据库对象数据类型注册为java中的类型 proc.registerOutParameter(1, Types.INTEGER); //执行 proc.execute(); //接收返回值 totalCount=proc.getInt(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return totalCount; }

2.3、执行带参带返回值的存储过程

[sql] view plain copy

print ?

–根据部门编号和姓名查询人数
CREATE OR REPLACE PROCEDURE getEmpCount(v_deptno NUMBER, v_ename VARCHAR2,v_count OUT NUMBER)
AS
BEGIN
SELECT COUNT(*) INTO v_count FROM emp
WHERE ename LIKE ‘%’||v_ename||‘%’ AND deptno=v_deptno;
END;

–根据部门编号和姓名查询人数 CREATE OR REPLACE PROCEDURE getEmpCount(v_deptno NUMBER, v_ename VARCHAR2,v_count OUT NUMBER) AS BEGIN SELECT COUNT(*) INTO v_count FROM emp WHERE ename LIKE ‘%’||v_ename||’%’ AND deptno=v_deptno; END;

[java] view plain copy

print ?

//执行带参带返回值的存储过程
public int getTotalCountProc1(int deptno,String ename){
//定义一个变量来接收结果
int totalCount=0;
//声明CallableStatement对象
CallableStatement proc=null;
String sql=”{call getEmpCount(?,?,?)}”;
//建立连接
getConnection();
//CallableStatement对象
try {
proc=conn.prepareCall(sql);
//设置占位符
//Object [] params={deptno,ename};
//只设置输入参数即可
proc.setInt(1, deptno);
proc.setString(2, ename);
//proc.setInt(3, totalCount);
将数据库对象数据类型注册为java中的类型，将输出参数转换
proc.registerOutParameter(3, Types.INTEGER);
//执行
proc.execute();
//获取结果
totalCount=proc.getInt(3);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.closeResource();
if(proc!=null){
try {
proc.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return totalCount;
}

//执行带参带返回值的存储过程 public int getTotalCountProc1(int deptno,String ename){ //定义一个变量来接收结果 int totalCount=0; //声明CallableStatement对象 CallableStatement proc=null; String sql=”{call getEmpCount(?,?,?)}”; //建立连接 getConnection(); //CallableStatement对象 try { proc=conn.prepareCall(sql); //设置占位符 //Object [] params={deptno,ename}; //只设置输入参数即可 proc.setInt(1, deptno); proc.setString(2, ename); //proc.setInt(3, totalCount); 将数据库对象数据类型注册为java中的类型，将输出参数转换 proc.registerOutParameter(3, Types.INTEGER); //执行 proc.execute(); //获取结果 totalCount=proc.getInt(3); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.closeResource(); if(proc!=null){ try { proc.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return totalCount; }

2.4、执行返回值为游标的存储过程

[sql] view plain copy

print ?

–查询员工所有信息
CREATE OR REPLACE PROCEDURE emp_cur(emp_cur OUT SYS_REFCURSOR)
AS
BEGIN
OPEN emp_cur FOR SELECT * FROM emp;
END;

–查询员工所有信息 CREATE OR REPLACE PROCEDURE emp_cur(emp_cur OUT SYS_REFCURSOR) AS BEGIN OPEN emp_cur FOR SELECT * FROM emp; END;

[java] view plain copy

print ?

//执行返回值为游标的存储过程游标名emp_cur
public List<emp> getempProc1(){
List<emp> emplist=new ArrayList<emp>();
String sql=”{call emp_cur(?) }”;
//声明CallableStatement对象
CallableStatement proc=null;
//建立连接
getConnection();
try {
//执行
proc=conn.prepareCall(sql);
//注册类型为数据库游标类型
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
//接收结果集
proc.execute();
//获取结果第一个对象
rs=(ResultSet) proc.getObject(1);
while(rs.next()){
int empno=rs.getInt(“empno”);
String ename=rs.getString(”ename”);
String job=rs.getString(”job”);
int mgr=rs.getInt(“mgr”);
Date hiredate=rs.getDate(”hiredate”);
double sal=rs.getDouble(“sal”);
double comm=rs.getDouble(“comm”);
int deptno=rs.getInt(“deptno”);
//声明Emp对象
Emp emp=new Emp();
//将得到的值添加到对象中
emp.setEmpno(empno);
emp.setEname(ename);
emp.setJob(job);
emp.setMgr(mgr);
emp.setHiredate(hiredate);
emp.setSal(sal);
emp.setComm(comm);
emp.setDeptno(deptno);
//将对象添加到集合
emplist.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
this.closeResource();
if(proc!=null){
try {
proc.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return emplist;
}</emp></emp></emp>

//执行返回值为游标的存储过程游标名emp_cur public List<emp> getempProc1(){ List<emp> emplist=new ArrayList<emp>(); String sql=”{call emp_cur(?) }”; //声明CallableStatement对象 CallableStatement proc=null; //建立连接 getConnection(); try { //执行 proc=conn.prepareCall(sql); //注册类型为数据库游标类型 proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); //接收结果集 proc.execute(); //获取结果第一个对象 rs=(ResultSet) proc.getObject(1); while(rs.next()){ int empno=rs.getInt(“empno”); String ename=rs.getString(“ename”); String job=rs.getString(“job”); int mgr=rs.getInt(“mgr”); Date hiredate=rs.getDate(“hiredate”); double sal=rs.getDouble(“sal”); double comm=rs.getDouble(“comm”); int deptno=rs.getInt(“deptno”); //声明Emp对象 Emp emp=new Emp(); //将得到的值添加到对象中 emp.setEmpno(empno); emp.setEname(ename); emp.setJob(job); emp.setMgr(mgr); emp.setHiredate(hiredate); emp.setSal(sal); emp.setComm(comm); emp.setDeptno(deptno); //将对象添加到集合 emplist.add(emp); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ this.closeResource(); if(proc!=null){ try { proc.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return emplist; }</emp></emp></emp>

以上看出，需要将输出的参数，和结果注册，输入的参数不要注册,

但输入参数需要设置占位符。

三、执行函数。

3.1 、函数功能为根据雇员id 返回姓名

[sql] view plain copy

print ?

CREATE OR REPLACE FUNCTION getename(v_empno NUMBER)
RETURN VARCHAR2
AS
v_ename VARCHAR2(20);
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;
RETURN v_ename;
END;

CREATE OR REPLACE FUNCTION getename(v_empno NUMBER) RETURN VARCHAR2 AS v_ename VARCHAR2(20); BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=v_empno; RETURN v_ename; END;

[java] view plain copy

print ?

public void getenamefun(int empno){
//sql
String ename=”“;
String sql=”{?=call getename(?)}”;//注意和存储过程的区别
CallableStatement fun=null;
getConnection();
try {
fun=conn.prepareCall(sql);
fun.setInt(2, empno);
fun.registerOutParameter(1, Types.VARCHAR);
fun.execute();
ename=fun.getString(1);
System.out.println(ename);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

public void getenamefun(int empno){ //sql String ename=”“; String sql=”{?=call getename(?)}”;//注意和存储过程的区别 CallableStatement fun=null; getConnection(); try { fun=conn.prepareCall(sql); fun.setInt(2, empno); fun.registerOutParameter(1, Types.VARCHAR); fun.execute(); ename=fun.getString(1); System.out.println(ename); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
其它的方法与过程一样，只是多了个返回值类型。

——————————————————————

四：接下来说下自己项目中的，直接看代码。

创建储存过程的代码基本一样，只是Java调用存储过程的时候，跟上边调用的方法不同，大家可以自行理解：

4.1:更新订单状态，返回状态值

[sql] view plain copy

print ?

DELIMITER $ -------------先定义语句的结束符号  </li><li class="">  </li><li class="alt">USE `zhangzi_tester`$ $ -------------先定义语句的结束符号  </li><li class="">  </li><li class="alt">USE `zhangzi_tester`$
IF EXISTS `pay_orderState` $  </li><li class="">  </li><li class="alt">CREATE  PROCEDURE `pay_orderState`(  </li><li class="">    IN serNumbner VARCHAR (32),  </li><li class="alt">    IN itemId VARCHAR (32),  </li><li class="">    IN captions INT,  </li><li class="alt">    IN cfcaPayTime VARCHAR(32),  </li><li class="">    IN payDate VARCHAR(30),  </li><li class="alt">    OUT state VARCHAR (2))  </li><li class="">BEGIN  </li><li class="alt">    DECLARE     maxMoney INT DEFAULT 0;  </li><li class="">  DECLARE     countMoney INT DEFAULT 0;  </li><li class="alt">  DECLARE     caps INT DEFAULT 0;  </li><li class="">START TRANSACTION;-- 配置事务  </li><li class="alt">SELECT  IFNULL(max_finance_money,0) INTO maxMoney FROM  t_item_info WHERE   id = itemId;  </li><li class="">SELECT  IFNULL(SUM(capital),0) INTO countMoney FROM     t_order_info WHERE fk_item_id = itemId AND pay_status = '01';  </li><li class="alt">   IF maxMoney >= captions+countMoney  THEN  -- 判断有没有超募  </li><li class="">            IF  maxMoney = countMoney+captions THEN  </li><li class="alt">                    UPDATE  t_item_info SET full_flag='01' WHERE id= itemId;  </li><li class="">            END IF;  </li><li class="alt">            UPDATE t_order_info SET pay_status = '01',order_complete_date = cfcaPayTime, pay_date= payDate  WHERE   serial_number = serNumbner; -- 没有超募  </li><li class="">        SET state ='1'; -- 未超募 返回1   </li><li class="alt">        ELSE  </li><li class="">            UPDATE t_order_info SET pay_status = '03',order_complete_date = cfcaPayTime, pay_date=  payDate  WHERE  serial_number = serNumbner; -- 没有超募  </li><li class="alt">            SET state ='3'; -- 已超募 返回3  </li><li class="">    END IF;  </li><li class="alt">COMMIT;   -- 事务结束提交  </li><li class="">END$
DELIMITER ;—改成默认的分号;

DELIMITER

— — — — - 先 定 义 语 句 的 结 束 符 号 U S E ‘ z h a n g z i t e s t e r ‘

$————-先定义语句的结束符号 USE `zhangzi_tester`$ DROP PROCEDURE IF EXISTS `pay_orderState`

C R E A T E P R O C E D U R E ‘ p a y o r d e r S t a t e ‘ (I N s e r N u m b n e r V A R C H A R (32), I N i t e m I d V A R C H A R (32), I N c a p t i o n s I N T, I N c f c a P a y T i m e V A R C H A R (32), I N p a y D a t e V A R C H A R (30), O U T s t a t e V A R C H A R (2)) B E G I N D E C L A R E m a x M o n e y I N T D E F A U L T 0; D E C L A R E c o u n t M o n e y I N T D E F A U L T 0; D E C L A R E c a p s I N T D E F A U L T 0; S T A R T T R A N S A C T I O N; - 配 置 事 务 S E L E C T I F N U L L (m a x f i n a n c e m o n e y, 0) I N T O m a x M o n e y F R O M t i t e m i n f o W H E R E i d = i t e m I d; S E L E C T I F N U L L (S U M (c a p i t a l), 0) I N T O c o u n t M o n e y F R O M t o r d e r i n f o W H E R E f k i t e m i d = i t e m I d A N D p a y s t a t u s = ‘ 01'; I F m a x M o n e y > = c a p t i o n s + c o u n t M o n e y T H E N - 判 断 有 没 有 超 募 I F m a x M o n e y = c o u n t M o n e y + c a p t i o n s T H E N U P D A T E t i t e m i n f o S E T f u l l f l a g =' 01' W H E R E i d = i t e m I d; E N D I F; U P D A T E t o r d e r i n f o S E T p a y s t a t u s = ‘ 01', o r d e r c o m p l e t e d a t e = c f c a P a y T i m e, p a y d a t e = p a y D a t e W H E R E s e r i a l n u m b e r = s e r N u m b n e r; - 没 有 超 募 S E T s t a t e =' 1'; - 未 超 募 返 回 1 E L S E U P D A T E t o r d e r i n f o S E T p a y s t a t u s = ‘ 03', o r d e r c o m p l e t e d a t e = c f c a P a y T i m e, p a y d a t e = p a y D a t e W H E R E s e r i a l n u m b e r = s e r N u m b n e r; - 没 有 超 募 S E T s t a t e =' 3'; - 已 超 募 返 回 3 E N D I F; C O M M I T; - 事 务 结 束 提 交 E N D

$CREATE PROCEDURE `pay_orderState`( IN serNumbner VARCHAR (32), IN itemId VARCHAR (32), IN captions INT, IN cfcaPayTime VARCHAR(32), IN payDate VARCHAR(30), OUT state VARCHAR (2)) BEGIN DECLARE maxMoney INT DEFAULT 0; DECLARE countMoney INT DEFAULT 0; DECLARE caps INT DEFAULT 0; START TRANSACTION;– 配置事务 SELECT IFNULL(max_finance_money,0) INTO maxMoney FROM t_item_info WHERE id = itemId; SELECT IFNULL(SUM(capital),0) INTO countMoney FROM t_order_info WHERE fk_item_id = itemId AND pay_status = ‘01’; IF maxMoney >= captions+countMoney THEN – 判断有没有超募 IF maxMoney = countMoney+captions THEN UPDATE t_item_info SET full_flag=’01’ WHERE id= itemId; END IF; UPDATE t_order_info SET pay_status = ‘01’,order_complete_date = cfcaPayTime, pay_date= payDate WHERE serial_number = serNumbner; – 没有超募 SET state =’1’; – 未超募返回1 ELSE UPDATE t_order_info SET pay_status = ‘03’,order_complete_date = cfcaPayTime, pay_date= payDate WHERE serial_number = serNumbner; – 没有超募 SET state =’3’; – 已超募返回3 END IF; COMMIT; – 事务结束提交 END$ DELIMITER ;—改成默认的分号;

4.2：Java调用存储过程代码

[java] view plain copy

print ?

/*
* 订单支付完成调用存储过程接口，修改订单支付状态
*/
@SuppressWarnings(“unchecked”)
public String orderState(final String serNumbner,final String itemId,final int captions,final String bankNotificationTime){
String param2Value=null;
try{
param2Value = (String)jdbc.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = ”{call pay_orderState(?,?,?,?,?,?)}”;// 调用的sql
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1, serNumbner);
cs.setString(2, itemId);
cs.setInt(3, captions);
cs.setString(4, bankNotificationTime);
cs.setString(5,DateTimeUtils.getNowTime());
cs.registerOutParameter(6, Types.VARCHAR);
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException{
cs.execute();
return cs.getString(6);// 获取输出参数的值
}
});
}catch(Exception ex){
throw new RuntimeException(ex);
}
return param2Value;
}

/* * 订单支付完成调用存储过程接口，修改订单支付状态 */ @SuppressWarnings(“unchecked”) public String orderState(final String serNumbner,final String itemId,final int captions,final String bankNotificationTime){ String param2Value=null; try{ param2Value = (String)jdbc.execute( new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { String storedProc = “{call pay_orderState(?,?,?,?,?,?)}”;// 调用的sql CallableStatement cs = con.prepareCall(storedProc); cs.setString(1, serNumbner); cs.setString(2, itemId); cs.setInt(3, captions); cs.setString(4, bankNotificationTime); cs.setString(5,DateTimeUtils.getNowTime()); cs.registerOutParameter(6, Types.VARCHAR); return cs; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException{ cs.execute(); return cs.getString(6);// 获取输出参数的值 } }); }catch(Exception ex){ throw new RuntimeException(ex); } return param2Value; }

4.3：再附上两段代码，有关Java调用储存过程的

[java] view plain copy

print ?