MyBatis：调用存储过程、游标使用

MyBatis 对存储过程提供了调用功能，并且支持对游标数据的转化功能。

存储过程

存储过程（Stored Procedure）：是一组为了完成特定功能的 SQL 语句集合，经创建编译后保存在数据库中给用户调用（call）。

存储过程是对数据库 SQL 语言层面的代码封装与重用，通常会包含入参 in 和 出参out 操作。

mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `count_actor`(IN q_first_name CHAR(20),OUT total int,OUT exec_date date)
BEGIN
	SELECT COUNT(*) INTO total FROM actor WHERE first_name = q_first_name;
	SELECT SYSDATE() INTO exec_date;
END;
1304 - PROCEDURE count_actor already exists
mysql> call count_actor('NICK', @total, @exec_date);
Query OK, 1 row affected (0.00 sec)

mysql> select @total, @exec_date;
+--------+------------+
| @total | @exec_date |
+--------+------------+
|      3 | 2023-07-23 |
+--------+------------+
1 row in set (0.03 sec)

IN和OUT参数使用

• IN参数是一个输入的参数。

• OUT参数是一个输出参数。通常 SELECT 得到的结果字段使用 INTO 到 OUT 指定的参数。

上面示例，是输入一个 q_first_name，将查询到的结果保存到 total这个 OUT参数中，并且将当前日期保存在 exec_date这个输入参数中。

调用存储过程

MyBatis 调用存储过程，只需通过一个参数类（parameterType）就可以读取输入参数和接收输出参数，Mapper 是 void无返回类型的，在 Mapper 的statementType需指定为 CALLABLE 。如statementType="CALLABLE"。

示例如下：

1. 创建存储过程，以上面 count_actor 存储过程为示例。

2. 定义一个ActorCountDO来反映存储过程的参数，包括输入参数和输出参数。

   @Data
   public class ActorCountDO {
   
       private String firstName;
       private Integer count;
       private Date execDate;
       
   }

3. 定义业务层

   ActorController

   @RestController
   @RequestMapping(value = "/actor")
   public class ActorController {
   
       @Autowired
       private ActorService actorService;
   
       @RequestMapping("/count")
       public ActorCountDO count(String firstName){
           ActorCountDO actorCountDO = actorService.count(firstName);
           return actorCountDO;
       }
   }

   ActorService

   public interface ActorService {
       ActorCountDO count(String firstName);
   }

   ActorServiceImpl

   @Service
   public class ActorServiceImpl implements ActorService {
   
       @Autowired
       private ActorMapper actorMapper;
   
       @Override
       public ActorCountDO count(String firstName) {
           ActorCountDO countDO = new ActorCountDO();
           countDO.setFirstName(firstName);
           // 调用存储过程无返回值的
           actorMapper.count(countDO);
           // 返回值会封装进参数对象中
           return countDO;
       }
   }

4. 定义DAO的Mapper
   ActorMapper.java

   @Repository
   public interface ActorMapper {
      	// 调用存储过程无返回值
       void count(ActorCountDO actorCountDO);
   }

   ActorMapper.xml

   配置存储过程，可以看到参数的输入输出参数的对应关系。指定statementType="CALLABLE"。

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   
   <mapper namespace="com.gxitsky.mapper.ActorMapper">
   
       <select id="count" statementType="CALLABLE"  parameterType="com.gxitsky.entity.ActorCountDO">
           {
               CALL count_actor(
                   #{firstName, mode=IN, jdbcType=VARCHAR},
                   #{count, mode=OUT,  jdbcType=INTEGER},
                   #{execDate, mode=OUT,  jdbcType=DATE}
               )
           }
       </select>
    </mapper>

   statementType="CALLABLE"告诉 MyBatis 用存储过程的方式执行它。如果不声明，程序将会抛出异常。

   • mode=IN 为输入参数。
   • mode=OUT为输出参数。
   • jdbcType定义为数据库的类型。

   MyBatis 回自动回填 count 和 execDate 到参数类型。

   存储过程游标