相关文章推荐


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

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


JDBC之CallableStatement执行存储过程_结果集



文章目录


1.存储过程简介

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

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

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

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

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

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

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

2.CallableStatement接口

我们来看一张图:


JDBC之CallableStatement执行存储过程_sql_02


从上图可以看到, 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);
}
}

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


JDBC之CallableStatement执行存储过程_结果集_03


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)​ ​的运行结果如下图所示:


JDBC之CallableStatement执行存储过程_结果集_04


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​ ​返回了两个结果集。


JDBC之CallableStatement执行存储过程_存储过程_05


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

​ 这里我们需要通过调​ ​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)​ ​的运行结果如下如所示:


JDBC之CallableStatement执行存储过程_存储过程_06


6.总结

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

参考阅读:


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



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

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



 
推荐文章