JDBC之CallableStatement执行存储过程

​ 在前面的一篇文章中,我们学习使用Statement、PreparedStatement来完成对数据表的增删改查。而存储过程作为数据库的重要组成部分(痛点,当时学的时候头发都掉了好几根😭),那JDBC是如何执行存储过程呢?今天我们就来看看如何使用CallableStatement接口来执行存储过程。(本文使用的数据库为MySQL)。

​ 兴奋、紧张,迫不及待,又要学新知识了。

资源分配图

1.存储过程简介

​ 在开始讲如何执行存储过程之前,我们先来简单的看下存储过程的相关概念。

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。

​ 上面的释义为百度百科上的词条,讲的也非常清晰了。我们在开发的过程中,经常在一个功能中,会执行多条SQL语句,这时就可以将这一组SQL语句封装在一个存储过程中,在应用端就可以通过调用存储过程来获取对应的结果,而不用执行许多SQL了。

这里说明一件事,关于是否应使用存储过程的争论由来已久,我写这篇文章也没有任何诱导你的想法,是否使用存储过程还需你自行评估。

​ 这里简单的讲下存储过程的优缺点:

​ 优点:1.执行速度快:存储过程预先创建好的,并且预先编译了,省去了每条SQL编译的时间;2.安全性高:可以避免SQL注入问题,避免暴露表结构和字段。

​ 缺点:1.对数据库依赖性比较大,数据库迁移改动会非常大;2.不适用于数据库集群,存储过程依赖于具体的库来实现的,如果数据是分布存储在多个库中,存储过程就很难处理了;3.可维护性差、可读性差:程序员无法直接看到代码,业务发生变更时不利于快速开发。

2.CallableStatement接口

​我们来看一张图:

资源分配图

​ 从上图可以看到,CallableStatement接口继承(extends)了PreparedStatement接口,也就是说,CallableStatement接口不仅可以执行存储过程,还可以使用PreparedStatement接口提供方法的执行DQL、DDL和DML语句。

​ 存储过程可能会包含入参(IN),出参(OUT),出入参(INOUT,既可做入参又可做出参),执行的结果也会返回int或是ResultSet,下面我们一起来看下如何使用CallableStatement来执行存储过程。

3.执行无参的存储过程

​ 环境搭建请参考前面的文章,这里数据库中有一个users表,我们就简单的操作这张表来演示存储过程的使用。

​ 首先我们创建一个无参的存储过程,SQL如下,逻辑很简单,就是查询users表中的所有用户信息。

CREATE PROCEDURE SelectAllUsers()
BEGIN
	SELECT * FROM users;
END;

​ 下面我们来看下JDBC是怎么执行无参的存储过程的,代码如下,其中的JDBCUtil代码参考上文

/**
	 * 调用无参的存储过程,返回users表中的所有数据
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
public List<User> callNoParamProcedure() throws ClassNotFoundException, SQLException {
  Connection connection = null;
  CallableStatement statement = null;
  ResultSet resultSet = null;
  try {
    // 获取数据库连接
    connection = JDBCUtil.getConnection();
    // 构建Sql
    String sql = "{ call SelectAllUsers() }";
    // 创建CallableStatement对象
    statement = connection.prepareCall(sql);
    // 执行存储过程
    resultSet = statement.executeQuery();
    // 迭代获取所有的用户
    while (resultSet.next()) {
      // new一个User实例
      User user = new User();
      user.setId(resultSet.getInt("id"));
      user.setUserName(resultSet.getString("name"));
      user.setPassword(resultSet.getString("password"));
      user.setEmail(resultSet.getString("email"));
      user.setBirthday(resultSet.getDate("birthday"));
      // 输出用户信息
      System.out.println("获取的用户信息为:" + user);
    }
    return userList;
  } catch (ClassNotFoundException e) {
    e.printStackTrace();
    throw e;
  } catch (SQLException e) {
    e.printStackTrace();
    throw e;
  } finally {
    JDBCUtil.release(resultSet, statement, connection);
  }
}

​ 上述代码执行后的运行结果如下:

资源分配图

4.执行有入参的存储过程

​ 上面我们讲了,存储过程可能会包含IN、OUT、INOUT参数,这里我们需要注意下,当存储过程中有OUT参数事,在执行存储过程前需要通过CallableStatement中的registerOutParameter方法先注册。

​ 这里创建如下存储过程:

CREATE PROCEDURE SelectNameByIdWithOut(IN in_id int, OUT out_name CHAR(50))
BEGIN
	SELECT `name` into out_name from users where id = in_id;
	SELECT * FROM users WHERE id = in_id;
END;

​ 代码如下,:

**
  * 调用含参的存储过程, 返回users表中id对应的数据
  * 
  * @return
  * @throws ClassNotFoundException
  * @throws SQLException
  */
public void callInOutParamProcedure(int id) throws ClassNotFoundException, SQLException {
  Connection connection = null;
  CallableStatement statement = null;
  ResultSet resultSet = null;
  try {
    // 获取数据库连接
    connection = JDBCUtil.getConnection();
    // 构建Sql
    String sql = "{call SelectNameByIdWithParam(?,?)}";
    // 创建CallableStatement对象
    statement = connection.prepareCall(sql);
    // 设置IN参数的值
    statement.setInt(1, id);
    // 注册OUT参数
    statement.registerOutParameter(2, Types.VARCHAR);
    // 执行存储过程
    resultSet = statement.executeQuery();
    // 获取上面注册的OUT参数,这里的index和入参顺序一致
    System.out.println("Out参数返回的结果为:" + statement.getString(2));
    // 迭代获取用户
    while (resultSet.next()) {
      User user = new User();
      user.setId(resultSet.getInt("id"));
      user.setUserName(resultSet.getString("name"));
      user.setPassword(resultSet.getString("password"));
      user.setEmail(resultSet.getString("email"));
      user.setBirthday(resultSet.getDate("birthday"));
      // 输出用户信息
      System.out.println("获取的用户信息为:" + user);
    }
  } catch (ClassNotFoundException e) {
    e.printStackTrace();
    throw e;
  } catch (SQLException e) {
    e.printStackTrace();
    throw e;
  } finally {
    JDBCUtil.release(resultSet, statement, connection);
  }
}

​ 执行测试代码callInOutParamProcedure(2)的运行结果如下图所示:

资源分配图

5.执行返回多个结果集的存储过程

​ 在上面两个例子中,执行查询语句,查询的结果就是一个结果集,但是如果一个存储过程中有多个查询语句呢?我们在程序中要如何将所有的结果集都获取到呢?

​ 这里我们创建如下存储过程:

CREATE PROCEDURE SelectUserWithMlutiRs(IN in_id int, OUT out_name CHAR(50))
BEGIN
	SELECT `name` into out_name from users where id = in_id;
	SELECT * FROM users WHERE id = in_id;
	SELECT * FROM users;
END;

​ 我们现在mysql中执行此存储过程(可以在WorkBench或者Navicat中,执行SQL即可,或者在MySQL客户端中执行),代码如下:

set @in_out='test-2';
CALL SelectUserWithMlutiRs(2, @in_out);
SELECT @in_out

​ 上述SQL的执行结果如下,其中结果1、结果1(2)为执行存储过程返回的结果集,可以看到,SelectUserWithMlutiRs返回了两个结果集。

资源分配图

​ 那我们在应用程序中又要怎么获取呢?

​ 这里我们需要通过调Statement对象中的getMoreResults方法切换到下一个结果集,并通过getResult方法获取。我们来看下代码:

/**
	 * 调用含参的存储过程, 返回users表中id对应的数据
	 * 
	 * @param id
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
public void callMultiRsProcedure(int id) throws ClassNotFoundException, SQLException {
  Connection connection = null;
  CallableStatement statement = null;
  ResultSet resultSet = null;
  try {
    // 获取数据库连接
    connection = JDBCUtil.getConnection();
    // 构建Sql
    String sql = "{ call SelectUserWithMlutiRs(?,?) }";
    // 创建CallableStatement对象
    statement = connection.prepareCall(sql);
    // 设置IN参数的值
    statement.setInt(1, id);
    // 注册OUT参数
    statement.registerOutParameter(2, Types.VARCHAR);
    // 执行存储过程
    resultSet = statement.executeQuery();
    // 获取上面注册的OUT参数,这里的index和入参顺序一致
    System.out.println("Out参数返回的结果为:" + statement.getString(2));
    int i = 1;
    System.out.println("第" + i + "个结果集中的数据为:");
    while (resultSet.next()) {
      User user = new User();
      user.setId(resultSet.getInt("id"));
      user.setUserName(resultSet.getString("name"));
      user.setPassword(resultSet.getString("password"));
      user.setEmail(resultSet.getString("email"));
      user.setBirthday(resultSet.getDate("birthday"));
      System.out.println("获取的用户信息为:" + user);
    }
    i++;
    // 判断是否还有下一个结果集
    // 注意,此处不能直接调用,需先获取当前的ResultSet才可调用,否则上个结果集会丢失
    while (statement.getMoreResults()) {
      resultSet = statement.getResultSet();
      // 迭代获取用户
      // 迭代获取用户
      System.out.println("第" + i + "个结果集中的数据为:");
      // 判断是否还有下一个结果集
      // 注意,此处不能直接调用,需先获取当前的ResultSet才可调用,否则上个结果集会丢失
      while (resultSet.next()) {
        User user = new User();
        user.setId(resultSet.getInt("id"));
        user.setUserName(resultSet.getString("name"));
        user.setPassword(resultSet.getString("password"));
        user.setEmail(resultSet.getString("email"));
        user.setBirthday(resultSet.getDate("birthday"));
        System.out.println("获取的用户信息为:" + user);
      }
      i++;
    }
  } catch (ClassNotFoundException e) {
    e.printStackTrace();
    throw e;
  } catch (SQLException e) {
    e.printStackTrace();
    throw e;
  } finally {
    JDBCUtil.release(resultSet, statement, connection);
  }
}

​ 执行测试代码callMultiRsProcedure(3)的运行结果如下如所示:

资源分配图

6.总结

​ 本文是对如何使用CallableStatement接口调用存储过程的简单示例。存储过程因为其自身优缺点非常明显,具体是否需要使用还需自己进行评估。

参考阅读:

  1. 减少存储过程封装业务逻辑-web开发与传统软件开发的思维模式不同
  2. 支付宝数据库架构师冯大辉:谈数据库架构

​ 又到了分隔线以下,本文到此就结束了,本文内容全部都是由博主自己进行整理并结合自身的理解进行总结,如果有什么错误,还请批评指正。

​ Java web这一专栏会是一个系列博客,喜欢的话可以持续关注,如果本文对你有所帮助,还请还请点赞、评论加关注。

​ 有任何疑问,可以评论区留言。

展开阅读全文
©️2020 CSDN 皮肤主题: 像素格子 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值