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

最新推荐文章于 2022-08-19 10:39:27 发布

零下忆度

最新推荐文章于 2022-08-19 10:39:27 发布

阅读量1.7k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

一：先看几个概念：

首先，自己参考了几篇文章，写的很不错，自己也借鉴一下，然后会在最后贴出自己在项目中使用到的存储过程，已经实现过程，大家可以做个对比，实现方法不同。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 $$ -------------先定义语句的结束符号
USE `zhangzi_tester`$$
DROP PROCEDURE IF EXISTS `pay_orderState`$$
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 ;---改成默认的分号;

DELIMITER $$ -------------先定义语句的结束符号 USE `zhangzi_tester`$$ DROP PROCEDURE IF EXISTS `pay_orderState`$$ 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 ?

@Resource(name = "jdbcTemplate")
private JdbcTemplate jdbc;

@Resource(name = "jdbcTemplate") private JdbcTemplate jdbc;

[xml] view plain copy print ?

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource">
</property></bean>

4.4:最后一张图片--mysql执行储存过程

下面这个和我的很相似，不用自己写了，就用他的吧！

最近做了一个需求，需要批量更新数据库表，但是因为涉及到的数据较多(千万条)，如果直接用sql更新，估计会把pl/sql弄垮

sql如下：update online_product set online_flag = '0' where status = 'ON'

所以，写了一个存储过程，以备忘：

[sql] view plain copy print ?

declare
cursor product_id_list is
select product_id from online_product where status = 'ON';
commit_count number := 0;
total_count number := 0;
begin
for pid in product_id_list loop
total_count := total_count + 1;
commit_count := commit_count + 1;
update online_product setonline_flag = '0'
where status = 'ON'
and product_id = pid.product_id;
if commit_count > 1000 then
commit;
commit_count := 0;
end if;
end loop;
commit;
dbms_output.put_line('Total Count:' || total_count);
end;

零下忆度

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Java代码中执行存储过程和函数

一：先看几个概念：首先，自己参考了几篇文章，写的很不错，自己也借鉴一下，然后会在最后贴出自己在项目中使用到的存储过程，已经实现过程，大家可以做个对比，实现方法不同。http://www.cnblogs.com/liunanjava/p/4261242.html过程和函数，它们被编译后保存在数据库中，称为持久性存储模块（Persistent Stored Module，PSM），可以反复调用，运...
复制链接

扫一扫