JDBC04
使用JDBC调用存储过程
存储过程：
简介：
在大型数据库系统中，一组为了完成特定功能的SQL语句，它存储在数据库中，一次编译，永久有效，用户通过指定存储过程名字，并给出参数进行执行
优点：
1.重复使用，从而减少了数据库开发人员的工作量
2.提高性能，存储过程里的SQL语句一旦编译成功后，下次再使用就不需要编译，而一般的SQL语句，是使用一次，编译一次
3.减少了网络流量，存储过程存储与数据库服务器上，当我们调用的时候，只需要传递存储过程名称和参数，降低网络传输的数据量
4.安全性，参数化存储过程可以防止SQL注入攻击，而且可以将Grant，Deny以及Revoke权限应用于存储过程
无参数的存储过程的调用
创建一个无参数的存储过程
delimiter // create procedure noparam() begin select * from emp; end // delimiter在MySQL端去调用存储过程
call noparam();JDBC端：
public static void main(String[] args) throws SQLException, ClassNotFoundException { // Statement // PreparedStatement Connection con = DButil.getConnection(); String sql = "call noparam()"; CallableStatement callableStatement = con.prepareCall(sql); // ResultSet resultSet = callableStatement.executeQuery(); // 如果不知道会不会返回一个结果集 callableStatement.execute();// 执行存储过程 ResultSet resultSet = callableStatement.getResultSet(); while (resultSet.next()) { System.out.println(resultSet.getString(1) + '\t' + resultSet.getString(2) + '\t' + resultSet.getString(3) + '\t' + resultSet.getString(4) + '\t' + resultSet.getString(5) + '\t' + resultSet.getString(6)); } resultSet.close(); }有入参的存储过程
模拟业务:销售商品，有三张表，一个是订单表，一个是库存表，一个是销售记录表，当销售一件商品时，订单表增加一条数据，库存表修改库存数量，销售表增加一条数据
create table order_info ( id int primary key auto_increment, order_id varchar(20), goods_id varchar(20), num int ) create table stock( id int primary key auto_increment, goods_id varchar(20), num int ) create table sale_records( id int primary key auto_increment, goods_id varchar(20), num int, sell_time datetime )向库存表中插入一条数据
insert into stock values(null,1001,100)创建存储过程，模拟一条销售业务
delimiter // create procedure sale_prod(in good_id varchar(20) ,in order_id varchar(20) ,in n int) begin start transaction ; insert into stock values (default,goods_id,n); update stock set num = num - n where goods_id = good_id; insert into sale_records values (default,good_id,n,now()); commit ; end // delimiterMySQL端调用
call sale_prod('1001','20230101',10)订单表中新增一条数据，库存表更新了原有的库存数据，销售记录表新增了一条数据
java端jdbc调用过程：
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection con = DButil.getConnection(); String sql = "call sale_prod(?,?,?)"; CallableStatement cs = con.prepareCall(sql); cs.setString(1, "1001"); cs.setString(2, "20230102"); cs.setInt(3, 20); cs.execute(); cs.close(); }有出参的存储过程
创建一个存储过程
create procedure add_test( in a int,in b int,out c int ) begin set c = a+b; end;mysql调用
call add_test(10,20,@xx);jdbc调用：
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection con = DButil.getConnection(); String sql = "call add_test(?,?,?)"; CallableStatement cs = con.prepareCall(sql); cs.setInt(1, 10); cs.setInt(2, 20); // 对于OUT参数的处理，标注参数类型 cs.registerOutParameter(3, java.sql.Types.INTEGER); cs.execute(); System.out.println(cs.getInt(3)); cs.close(); }特殊参数存储过程调用
创建存储过程及其调用
mysql：
create PROCEDURE m(inout res int) begin set res = res*10; end; set @res = 5; call m(@res); select @res;// 50jdbc：
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection con = DButil.getConnection(); CallableStatement cstmt = con.prepareCall("{call m(?)}"); cstmt.setInt(1, 5); cstmt.registerOutParameter(1, java.sql.Types.INTEGER); cstmt.execute(); System.out.println(cstmt.getInt(1)); cstmt.close(); }存储过程返回多个结果集
创建一个存储过程返回多个结果集
create procedure selectMul(goodsID int) begin select * from order_info where goods_id=goodsID; select * from stock where goods_id=goodsID; select * from sale_records where goods_id=goodsID; end;版本1：
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection connection = DButil.getConnection(); String sql = "call selectMul(?)"; CallableStatement callableStatement = connection.prepareCall(sql); callableStatement.setString(1, "1001"); callableStatement.execute(); ResultSet resultSet = callableStatement.getResultSet(); while (resultSet.next()) { System.out.println(resultSet.getString(1)+'\t'+resultSet.getString(2)+'\t'+resultSet.getString(3)); } while (callableStatement.getMoreResults()) { System.out.println("=================================="); resultSet = callableStatement.getResultSet(); while (resultSet.next()) { System.out.println(resultSet.getString(1)+'\t'+resultSet.getString(2)+'\t'+resultSet.getString(3)); } } callableStatement.close(); connection.close(); }版本2
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection connection = DButil.getConnection(); String sql = "call selectMul(?)"; CallableStatement callableStatement = connection.prepareCall(sql); callableStatement.setString(1, "1001"); callableStatement.execute(); ResultSet resultSet = callableStatement.getResultSet(); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); while (resultSet.next()) { for (int i = 1; i <= columnCount; i++) { System.out.print(resultSet.getString(i)+'\t'); } System.out.println(); } while (callableStatement.getMoreResults()) { System.out.println("=================================="); resultSet = callableStatement.getResultSet(); ResultSetMetaData metaData1 = resultSet.getMetaData(); int columnCount1 = metaData1.getColumnCount(); while (resultSet.next()) { for (int i = 1; i <= columnCount1; i++) { System.out.print(resultSet.getString(i)+'\t'); } System.out.println(); } } callableStatement.close(); connection.close(); }execute,executeQuery,executeUpdate区别
execute:方法用来执行任意的SQL语句，如果执行的结果是ResultSet，则返回true否则就是false，则通过getResultSet()来获取ResultSet，也可以通过getMoreResults()方法，获得多个结果集，如果操作的是DML语句，可以调用getUpdateCount方法获取一些数据库的行数
executeQuery：用来执行select查询结果处理，返回Resultset,即使查询不到结果，ResultSet也不是null，会通过next()方法返回false
executeUpdate:用来执行insert，update，delete等DML语句，返回的是int类型，表示影响了多少行，也可以操作DDL，返回0
可滚动可更新结果集
可滚动结果集: 结果集的光标既可以向上滚动，也可以向下滚动，则称为是可滚动的结果集，如果只能向下滚动，则称为不可滚动结果集
默认情况下结果集不可滚动 1003
可滚动结果集，结果集不会跟随数据库的数据变化而改变 1004
ResultSet.TYPE_SCROLL_INSENSITIVE
可滚动结果集，结果集会跟随数据库的数据变化而改变 1005
ResultSet.TYPE_SCROLL_SENSITIVE
可更新结果集，可以通过结果集反向修改数据库 1007
ResultSet.CONCUR_UPDATABLE
只读结果集，不能通过结果集去反向修改数据库 1008
ResultSet.CONCUR_READ_ONLY
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection con = DButil.getConnection(); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); String sql = "select * from emp"; ResultSet rs = stmt.executeQuery(sql); rs.next(); // 报错 System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"); rs.next(); rs.previous(); System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"); rs.close(); }两次输出结果一样
创建可滚动可更新结果集
可滚动结果集API
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection con = DButil.getConnection(); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); String sql = "select * from emp"; ResultSet rs = stmt.executeQuery(sql); rs.beforeFirst();// 将结果集光标放入第一行前面，也就是默认位置 rs.afterLast();// 将光标放在最后一行的后面 while (rs.previous()) { System.out.println(rs.getString(1)+"\t"+rs.getString(2)); } boolean flag = rs.first();// 将光标放在第一行搁置，返回是否有数据 System.out.println(flag); boolean flag1 = rs.last();// 将光标放在最后一行搁置，返回是否有数据 System.out.println(flag1); boolean absolute = rs.absolute(7);// 绝对定位，绝对位移，直接把光标移动到给定行的位置上 System.out.println(absolute); // rs.next();// 将当前光标的位置向下移动一位 rs.previous();// 向上移动一位 rs.relative(2);// 表示相对位移,向下移动rows位， // 如果参数是负数，则表示向上移动 System.out.println(rs.getRow());// 检测光标当前所在行 }可更新结果集
String sql = "select * from emp"; ResultSet rs2 = stmt2.executeQuery(sql); rs2.next(); rs2.updateString(2,"白居易"); rs2.updateString(3,"女"); rs2.updateRow(); rs2.close();使用JDBC去调用自定义函数
创建自定义函数
创建函数前需要执行如下命令
SET GLOBAL log_bin_trust_function_creators = 1
mysql执行：
select getHiredate('白居易')
JDBC执行：
public static void main(String[] args) throws SQLException, ClassNotFoundException { Connection con = DButil.getConnection(); CallableStatement cstmt = con.prepareCall("{?=call getHiredate(?)}"); // 调用自定义函数的写法 cstmt.registerOutParameter(1, java.sql.Types.INTEGER); cstmt.setString(2,"白居易"); cstmt.execute(); System.out.println(cstmt.getDate(1)); cstmt.close(); con.close(); }