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

一：先看几个概念：

首先，自己参考了几篇文章，写的很不错，自己也借鉴一下，然后会在最后贴出自己在项目中使用到的存储过程，已经实现过程，大家可以做个对比，实现方法不同。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、建立基类

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、执行不带参但是有返回值的存储过程

下面建立存储过程

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

调用

//执行不带参但是有返回值的存储过程获取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、执行带参带返回值的存储过程

--根据部门编号和姓名查询人数 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;

//执行带参带返回值的存储过程 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、执行返回值为游标的存储过程

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

//执行返回值为游标的存储过程 游标名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 返回姓名

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;

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:更新订单状态，返回状态值

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调用存储过程代码

/* * 订单支付完成调用存储过程接口，修改订单支付状态 */ @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调用储存过程的

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

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

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