JDBC核心技术七（CallableStatement）

系列笔记目录

JDBC技术的操作SQL存储过程的CallableStatement，今天来了解一下；

一、 MySQL存储过程

1、什么是存储过程？

存储过程（Store Procedure）：是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象；
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过储存过程名+参数进行调用（可简单类比于Java中的函数）；
存储过程是SQL语言层面的代码封装与重用；
存储过程与普通的SQL执行流程对比如下图所示：

2、存储过程的优点

效率高：编译一次，保存在数据库，每次直接调用执行，普通SQL需要进行词法分析、语法分析、编译之后才会执行；
复用性高：存储过程一般为实现一个特定功能，在需要这个特定功能时，可直接复用；
安全性高：可设定只有授权用户可以使用；
降低网络通信量：如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载；

3. MySQL中创建数据库作为本文中的实例

创建数据库属于，采用SQLyog导出的SQL语句

CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `password` varchar(15) NOT NULL DEFAULT '123456',
  `address` varchar(25) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gb2312;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`password`,`address`,`phone`) values 

(1,'章子怡','qwerty','Beijing','13788658672'),

(2,'郭富城','abc123','HongKong','15678909898'),

(3,'林志颖','654321','Taiwan','18612124565'),

(4,'梁静茹','987654367','malaixiya','18912340998'),

(5,'LadyGaGa','123456','America','13012386565');

数据表user表信息如下图所示：

user表信息

4. 创建存储过程

存储过程就是具有名字的一段代码，用来完成一个特定的功能；
创建的存储过程保存在数据库的数据字段中；

4.1 创建存储过程

MySQL数据库创建存储存储过程的基本语法：

CREATE PROCEDURE 存错过程名 (参数类型1 参数1 数据类型1, [...])
BEGIN
具体的procedure的SQL语句
END 结束符

【例子：创建按照ID查询user表中name的存储过程】

DELIMITER //
CREATE PROCEDURE selectName(IN u_id INTEGER)
BEGIN
SELECT NAME FROM USER WHERE id = u_id;
END //

mysql> DELIMITER //
mysql> CREATE PROCEDURE selectName(IN u_id INTEGER)
    -> BEGIN
    -> SELECT NAME FROM USER WHERE id = u_id;
    -> END //
Query OK, 0 rows affected (0.01 sec)

注意事项：若想创建某个指定的数据库下的存储过程，需要存储过程名前+数据库名做前缀；

【例子：指定数据库的存储过程】

mysql> CREATE PROCEDURE test.selectPhone(IN u_name VARCHAR(255))
    -> BEGIN
    -> SELECT phone FROM USER WHERE NAME = u_name;
    -> END //
Query OK, 0 rows affected (0.01 sec)
mysql> call test.selectPhone('章子怡');
+-------------+
| phone       |
+-------------+
| 13788658672 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

4.2 调用存储过程

基本语法：

call 存储过程名(传参);

【例子：调用selectName存储过程】

mysql> call selectName(1);
+-----------+
| NAME      |
+-----------+
| 章子怡    |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

4.3 声明语句结束符

声明语句结束符，可以自定义

DELIMITER $$ # "$$"为自定义的结束符
或
DELIMITER //

声明结束符的作用：BEGIN与END之间，具体的SQL语句需要以**“;”**，若不替换语句结束符，没到END就已经结束，存储过程创建失败，如下所示：

错误实例

4.3 存储过程开始和结束符

BEGIN .... END

BEGIN … END之间定义具体的存储过程SQL语句

例如上面的存储过程selectName中的：

BEGIN
SELECT NAME FROM USER WHERE id = u_id;
END

4.4 定义变量

定义变量的语法：

SET @p_in=1

4.5 删除和查询存储过程

删除：

DROP PROCEDURE 过程名;

查看：

# 查看数据库的所有存储过程
SHOW PROCEDURE STATUS WHERE db = '数据库名';

#正则化查找
SHOW PROCEDURE STATUS like '存储过程名%';

mysql> show procedure status where db = 'test';
+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name        | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | getNameById | PROCEDURE | root@localhost | 2021-05-26 19:06:33 | 2021-05-26 19:06:33 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| test | GreetWorld  | PROCEDURE | root@localhost | 2021-05-26 10:51:49 | 2021-05-26 10:51:49 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| test | inout_param | PROCEDURE | root@localhost | 2021-05-26 10:44:48 | 2021-05-26 10:44:48 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| test | in_param    | PROCEDURE | root@localhost | 2021-05-26 18:43:55 | 2021-05-26 18:43:55 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| test | out_param   | PROCEDURE | root@localhost | 2021-05-26 10:41:57 | 2021-05-26 10:41:57 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| test | p1          | PROCEDURE | root@localhost | 2021-05-26 10:52:43 | 2021-05-26 10:52:43 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| test | p2          | PROCEDURE | root@localhost | 2021-05-26 10:52:59 | 2021-05-26 10:52:59 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
| test | selectcName | PROCEDURE | root@localhost | 2021-05-26 17:23:09 | 2021-05-26 17:23:09 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| test | select_user | PROCEDURE | root@localhost | 2021-05-26 10:29:24 | 2021-05-26 10:29:24 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
9 rows in set (0.00 sec)


mysql> show procedure status like 'get%';
+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name        | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | getNameById | PROCEDURE | root@localhost | 2021-05-26 19:06:33 | 2021-05-26 19:06:33 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8_general_ci    |
+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

5. 存储过程的参数解析

MySQL存储过程的参数用在存储过程的定义，共有三种类型参数IN，OUT，INOUT，形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

IN输入参数：表示调用者向存储过程传入值（传入值可以是字面量或变量）；
OUT输出参数：表示过程向调用者传出值（可以返回多个值）（传出值只能是变量）；
INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值；（值只能是变量）；

5.1 IN输入参数

【例子：IN输入参数】

mysql> delitimer //
mysql> create procedure in_param(in p_in int)
    -> begin
    -> select p_in;
    -> set p_in = 2;
    -> select p_in;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @p_in = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| p_in |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

解析：p_in在存储过程中被修改，但并不影响@p_in的值，前者是局部变量，后者是全局变量；

5.2 OUT输出参数

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
 
mysql> set @p_out=1;
 
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+
 
mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

5.3 INOUT输入输出参数

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

5.4 注意事项

若存储过程无参数，也需在过程名后加上小括号；
须确保参数名不会数据表的列名；
INOUT尽量少用；

二、JDBC使用CallableStatement

JDBC可以使用CallableStatement调用数据库的存储过程；

1. 使用步骤

使用Connection对象的prepareCall()方法创建CallableStatement对象；
IN参数使用 CallableStatement.setXxx()设置；
OUT参数使用CallableStatement.registerOutParameter()设置，使用CallableStatement.getXxx()获取输出参数；
执行使用execute()，或者executeUpdate()、executeQuery();

2. JDBC测试CallableStatement参数

2.1 创建Procedure存储过程

【例子：根据id查询用户的名字】

mysql> delimiter //
mysql> create procedure getNameById(IN u_id int, OUT u_name varchar(255))
    -> begin
    -> select name into u_name
    -> from user
    -> where id = u_id;
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
# 定义变量U_name为null
mysql> set @u_name = null;
Query OK, 0 rows affected (0.00 sec)

mysql> select @u_name;
+------------------+
| @u_name          |
+------------------+
| 0x               |
+------------------+
1 row in set (0.00 sec)

#调用getNameById存储过程，传入参数：IN参数id = 1，OUT参数@p_name; 
mysql> call getNameById(1, @p_name);
Query OK, 1 row affected, 1 warning (0.00 sec)

#执行完后变量p_name变为章子怡，传出参数；
mysql> select @p_name;
+-----------+
| @p_name   |
+-----------+
| 章子怡    |
+-----------+
1 row in set (0.00 sec)

2.2 JDBC Java实现调用CallableStatement

    @Test
    public void testInOutParam() throws Exception {
        Connection conn = null;
        CallableStatement cs = null;
        try {
            //1. 获取数据库连接
            conn = JDBCUtils.getConnection();

            //2. 调用存储过程的SQL语句
            String sql = "call getNameById(?, ?)";

            //3. 创建CallableStatement对象
            cs = conn.prepareCall(sql);

            //4. 填充参数
            cs.setInt(1, 1);
            //4.1 第二个参数为传出参数，采用registerOutParameter
            cs.registerOutParameter(2, Types.VARCHAR);

            //5. 执行SQL语句
            cs.executeUpdate();

            //6. 获取传出参数
            String name = cs.getString(2);
            System.out.println("传出参数name = " + name);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, cs, null);
        }
    }

执行结果：

传出参数name = 章子怡

原文链接：https://blog.csdn.net/wegofun/article/details/117302890