Mybatis3 调用存储过程

1. 数据库MySQL，user表

CREATE TABLE `user` (`USER_ID` int NOT NULL AUTO_INCREMENT,`USER_NAME` varchar(100) NOT NULL COMMENT '用户姓名',`AGE` int NOT NULL COMMENT '年龄',`CREATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`CREATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN',`UPDATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,`UPDATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN',PRIMARY KEY (`USER_ID`)) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3

 

2.使用Mybatis-generator生成POJO等对象

具体使用方法参考文章：Mybatis Generator 使用手册-CSDN博客

User.java

package com.derek.model;import java.util.Date;public class User {/**** This field was generated by MyBatis Generator.* This field corresponds to the database column user.USER_ID** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/private Integer userId;/**** This field was generated by MyBatis Generator.* This field corresponds to the database column user.USER_NAME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/private String userName;/**** This field was generated by MyBatis Generator.* This field corresponds to the database column user.AGE** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/private Integer age;/**** This field was generated by MyBatis Generator.* This field corresponds to the database column user.CREATED_TIME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/private Date createdTime;/**** This field was generated by MyBatis Generator.* This field corresponds to the database column user.CREATED_BY** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/private String createdBy;/**** This field was generated by MyBatis Generator.* This field corresponds to the database column user.UPDATED_TIME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/private Date updatedTime;/**** This field was generated by MyBatis Generator.* This field corresponds to the database column user.UPDATED_BY** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/private String updatedBy;/*** This method was generated by MyBatis Generator.* This method returns the value of the database column user.USER_ID** @return the value of user.USER_ID** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public Integer getUserId() {return userId;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column user.USER_ID** @param userId the value for user.USER_ID** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public void setUserId(Integer userId) {this.userId = userId;}/*** This method was generated by MyBatis Generator.* This method returns the value of the database column user.USER_NAME** @return the value of user.USER_NAME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public String getUserName() {return userName;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column user.USER_NAME** @param userName the value for user.USER_NAME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public void setUserName(String userName) {this.userName = userName;}/*** This method was generated by MyBatis Generator.* This method returns the value of the database column user.AGE** @return the value of user.AGE** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public Integer getAge() {return age;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column user.AGE** @param age the value for user.AGE** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public void setAge(Integer age) {this.age = age;}/*** This method was generated by MyBatis Generator.* This method returns the value of the database column user.CREATED_TIME** @return the value of user.CREATED_TIME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public Date getCreatedTime() {return createdTime;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column user.CREATED_TIME** @param createdTime the value for user.CREATED_TIME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public void setCreatedTime(Date createdTime) {this.createdTime = createdTime;}/*** This method was generated by MyBatis Generator.* This method returns the value of the database column user.CREATED_BY** @return the value of user.CREATED_BY** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public String getCreatedBy() {return createdBy;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column user.CREATED_BY** @param createdBy the value for user.CREATED_BY** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public void setCreatedBy(String createdBy) {this.createdBy = createdBy;}/*** This method was generated by MyBatis Generator.* This method returns the value of the database column user.UPDATED_TIME** @return the value of user.UPDATED_TIME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public Date getUpdatedTime() {return updatedTime;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column user.UPDATED_TIME** @param updatedTime the value for user.UPDATED_TIME** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public void setUpdatedTime(Date updatedTime) {this.updatedTime = updatedTime;}/*** This method was generated by MyBatis Generator.* This method returns the value of the database column user.UPDATED_BY** @return the value of user.UPDATED_BY** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public String getUpdatedBy() {return updatedBy;}/*** This method was generated by MyBatis Generator.* This method sets the value of the database column user.UPDATED_BY** @param updatedBy the value for user.UPDATED_BY** @mbg.generated Sat Mar 08 07:25:49 CST 2025*/public void setUpdatedBy(String updatedBy) {this.updatedBy = updatedBy;}@Overridepublic String toString() {return "User{" +"userId=" + userId +", userName='" + userName + '\'' +", age=" + age +", createdTime=" + createdTime +", createdBy='" + createdBy ;}
}

其他的类就不逐一介绍了，这里只列出User对象，后面会使用。

 

3.定义存储过程

这里的存储过程为了展示功能，使用了IN和OUT类型两种参数。

具体的功能为：根据输入的页面编号和页面数据量参数，返回总的页数和总数据量。同时返回查询的页面数据集合。

DELIMITER $$
CREATE PROCEDURE query_user_by_page(IN page_num INTEGER,IN page_size INTEGER,OUT total_count INTEGER,OUT total_page INTEGER
)
BEGINDECLARE start_pos INT;SET start_pos = (page_num - 1) * page_size;-- compute total count SELECT COUNT(1) INTO total_countFROM user;-- compute total pageSET total_page = CEILING(total_count/page_size);SELECT * FROM user LIMIT start_pos, page_size;
END
$$ DELIMITER ;

MySQL workbench console调用，检查procedure正确性

call query_user_by_page(2, 2, @total_count, @total_page);
select @total_count, @total_page;

 

小提示：MySQL 存储过程局部变量和全局变量区别和使用

1. 局部变量

局部变量是在存储过程或函数中声明的变量，其作用域仅限于该存储过程或函数内部。

声明局部变量

局部变量必须在存储过程的开头声明，且在 BEGIN 语句之后。使用 DECLARE 语句声明局部变量。

DELIMITER //CREATE PROCEDURE ExampleProcedure()
BEGINDECLARE var1 INT DEFAULT 0;  -- 声明一个整型变量，默认值为 0DECLARE var2 VARCHAR(50);    -- 声明一个字符串变量DECLARE var3 DATE;           -- 声明一个日期变量-- 变量赋值SET var2 = 'Hello, World!';SET var3 = '2025-04-14';-- 使用变量SELECT var1, var2, var3;
END //DELIMITER ;

2. 全局变量

全局变量的作用范围是整个数据库会话，可以在多个存储过程或会话中使用。MySQL 提供了一些预定义的全局变量，也可以通过 SET 语句设置自定义全局变量。

预定义的全局变量

MySQL 提供了一些系统全局变量，可以通过 SHOW VARIABLES 或 SELECT @@variable_name 查看其值。

sql复制

SHOW VARIABLES LIKE 'max_connections';  -- 查看系统变量
SELECT @@max_connections;              -- 查看系统变量

自定义全局变量

可以通过 SET 语句设置自定义全局变量。自定义全局变量的作用范围是整个会话。

SET @global_var = 'Hello, Global!';  -- 设置全局变量DELIMITER //CREATE PROCEDURE UseGlobalVariable()
BEGINSELECT @global_var;  -- 在存储过程中使用全局变量
END //DELIMITER ;CALL UseGlobalVariable();  -- 调用存储过程

 

4.编写Mapper.xml中存储过程调用

<select id="queryUserByPage" statementType="CALLABLE" parameterType="map" resultType="com.derek.model.User">{call query_user_by_page(#{pageNum, mode=IN, javaType=INTEGER},#{pageSize, mode=IN, javaType=INTEGER},#{totalCount, mode=OUT, jdbcType=INTEGER},#{totalPage, mode=OUT, jdbcType=INTEGER})}
</select>

这里注意statementType选择“CALLABLE”，表示调用存储过程。一般使用map来传递IN, OUT 参数。

特别注意: 存储过程的名字要写对，不然查错误❌特别费劲。我曾经名字写错了，写成了调用方法的名字queryByPage。报错Parameter number 3 is not OUT parameter。被整得懵懵的，找了很久才发现错误。

5. Mapper.java中接口定义

public interface UserMapperExt extends UserMapper {List<User> queryUserByPage(Map<String,Object> map);}

这里使用Map<String,Object>来接收和存储OUT对象。

 

6.编写单元测试

package com.derek.mapper;import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.Map;public class UserMapperExtTest {private static SqlSession sqlSession;private static UserMapperExt userMapperExt;@BeforeAllpublic static void setUp() throws IOException {Reader reader = Resources.getResourceAsReader("mybatis-config.xml");sqlSession = new org.apache.ibatis.session.SqlSessionFactoryBuilder().build(reader).openSession();userMapperExt = sqlSession.getMapper(UserMapperExt.class);}@AfterAllpublic static void tearDown() {userMapperExt = null;sqlSession.close();}@Testpublic void testQueryByPage() {Map<String,Object> map = new HashMap<>();map.put("pageNum", 1);map.put("pageSize", 2);userMapperExt.queryUserByPage(map).forEach(System.out::println);sqlSession.commit();System.out.println("totalCount:" + map.get("totalCount"));System.out.println("totalPage:" + map.get("totalPage"));}}

我的数据库中User表有11条数据，查询接入如下： 

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@76012793]
==>  Preparing: { call query_user_by_page( ?, ?, ?, ? ) }
==> Parameters: 1(Integer), 2(Integer)
<==    Columns: USER_ID, USER_NAME, AGE, CREATED_TIME, CREATED_BY, UPDATED_TIME, UPDATED_BY
<==        Row: 1, derek, 20, 2025-02-14 13:34:01, UNKNOWN, 2025-03-01 21:47:07, derek
<==        Row: 3, adore, 34, 2025-03-08 03:07:39, jack.zhang, 2025-03-08 03:07:39, jack.zhang
<==      Total: 2
<==    Updates: 0
User{userId=1, userName='derek', age=20, createdTime=Fri Feb 14 21:34:01 CST 2025, createdBy='UNKNOWN
User{userId=3, userName='adore', age=34, createdTime=Sat Mar 08 11:07:39 CST 2025, createdBy='jack.zhang
totalCount:11
totalPage:6
 

 到此为止，Mybatis3调用存储过程结束。