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;
}
@Override
public 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
)
BEGIN
DECLARE start_pos INT;
SET start_pos = (page_num - 1) * page_size;
-- compute total count
SELECT COUNT(1) INTO total_count
FROM user;
-- compute total page
SET 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()
BEGIN
DECLARE var1 INT DEFAULT 0; -- 声明一个整型变量,默认值为 0
DECLARE 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()
BEGIN
SELECT @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;
@BeforeAll
public 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);
}
@AfterAll
public static void tearDown() {
userMapperExt = null;
sqlSession.close();
}
@Test
public 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调用存储过程结束。