Spring Boot - Spring Boot 集成 MyBatis 分页实现 手写 SQL 分页

一、准备阶段

1、依赖引入

• pom.xml

<properties>

    ...

    <postgresql.verison>42.5.6</postgresql.verison>
    <mybatis.version>3.0.1</mybatis.version>
</properties>

<dependencies>

    ...

    <!-- postgresql 驱动 -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>${postgresql.verison}</version>
    </dependency>

    <!-- mybatis 和 springboot 整合的起步依赖 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>${mybatis.version}</version>
    </dependency>
</dependencies>

2、配置文件

1. application.yml

mybatis:
  mapper-locations: classpath:/mapper/*.xml # 映射文件路径
  config-location: classpath:/mybatis-config.xml # 核心配置文件路径

2. mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC
        "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>

        <!-- 设置驼峰标识 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>

        <!-- 打印 SQL 语句 -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
</configuration>

3、准备数据

1. 创建数据表

CREATE TABLE staff (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(255),
    salary DECIMAL(10, 2)
);

2. 插入数据

INSERT INTO staff (name, role, salary) VALUES
('张三', '项目经理', 25000.00),
('李四', '高级开发工程师', 18000.00),
('王五', '开发工程师', 15000.00),
('赵六', '测试工程师', 12000.00),
('钱七', 'UI设计师', 13000.00),
('孙八', '产品经理', 20000.00),
('周九', '运维工程师', 14000.00),
('吴十', '初级开发工程师', 10000.00),
('郑十一', '数据库管理员', 16000.00),
('王十二', '技术总监', 30000.00);

二、手写 SQL 分页实现

1、XML 方式

（1）Mapper

1. StaffMapper.java

@Mapper
public interface StaffMapper {
    List<Staff> queryStaffs(int offset, int pageSize);

    Integer countStaffs();
}

2. StaffMapper.xml

<?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.my.mapper.StaffMapper">

    <resultMap id="staffResultMap" type="com.my.model.db.Staff">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="role" property="role"/>
        <result column="salary" property="salary"/>
    </resultMap>

    <select id="queryStaffs" resultMap="staffResultMap">
        SELECT *
        FROM staff LIMIT #{pageSize}
        OFFSET #{offset}
    </select>
    
    <select id="countStaffs" resultType="int">
        SELECT COUNT(*)
        FROM staff
    </select>
</mapper>

（2）Test

// 分页参数计算
int pageNum = 2; // 当前页码
int pageSize = 2; // 每页条数
int offset = (pageNum - 1) * pageSize;

List<Staff> staffs = staffMapper.queryStaffs(offset, pageSize);
int total = staffMapper.countStaffs();
int pageTotal = (int) Math.ceil((double) total / pageSize);

for (Staff staff : staffs) {
    System.out.println(staff);
}

System.out.println("pageNum: " + pageNum);
System.out.println("pageSize: " + pageSize);
System.out.println("pageTotal: " + pageTotal);
System.out.println("total: " + total);

# 输出结果

Staff(id=5, name=王五, role=开发工程师, salary=15000.0)
Staff(id=6, name=赵六, role=测试工程师, salary=12000.0)
pageNum: 2
pageSize: 2
pageTotal: 5
total: 10

2、注解方式

（1）Mapper

• StaffMapper.java

@Select("SELECT * FROM staff ORDER BY id LIMIT #{pageSize} OFFSET #{offset}")
List<Staff> queryStaffs(int offset, int pageSize);

@Select("SELECT COUNT(*) FROM staff")
Integer countStaffs();

（2）Test

// 分页参数计算
int pageNum = 2; // 当前页码
int pageSize = 2; // 每页条数
int offset = (pageNum - 1) * pageSize;

List<Staff> staffs = staffMapper.queryStaffs(offset, pageSize);
int total = staffMapper.countStaffs();
int pageTotal = (int) Math.ceil((double) total / pageSize);

for (Staff staff : staffs) {
    System.out.println(staff);
}

System.out.println("pageNum: " + pageNum);
System.out.println("pageSize: " + pageSize);
System.out.println("pageTotal: " + pageTotal);
System.out.println("total: " + total);

# 输出结果

Staff(id=5, name=王五, role=开发工程师, salary=15000.0)
Staff(id=6, name=赵六, role=测试工程师, salary=12000.0)
pageNum: 2
pageSize: 2
pageTotal: 5
total: 10

三、手写 SQL 分页实现封装

1、Entity

• PageResult.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> {
    private List<T> data;
    private Integer pageNum;
    private Integer pageSize;
    private Integer pageTotal;
    private Integer total;
}

2、Mapper

• StaffMapper.java

@Select("SELECT * FROM staff ORDER BY id LIMIT #{pageSize} OFFSET #{offset}")
List<Staff> queryStaffs(int offset, int pageSize);

@Select("SELECT COUNT(*) FROM staff")
Integer countStaffs();

3、Service

1. StaffService.java

public interface StaffService {
    PageResult<Staff> queryStaffs(int pageNum, int pageSize);
}

2. StaffServiceImpl.java

@Service
public class StaffServiceImpl implements StaffService {

    @Autowired
    private StaffMapper staffMapper;

    @Override
    public PageResult<Staff> queryStaffs(int pageNum, int pageSize) {

        int offset = (pageNum - 1) * pageSize;

        List<Staff> staffs = staffMapper.queryStaffs(offset, pageSize);
        int total = staffMapper.countStaffs();
        int pageTotal = (int) Math.ceil((double) total / pageSize);

        return new PageResult<>(staffs, pageNum, pageSize, pageTotal, total);
    }
}

4、Test

PageResult<Staff> staffPageResult = staffService.queryStaffs(1, 5);

List<Staff> staffs = staffPageResult.getData();
int pageNum = staffPageResult.getPageNum();
int pageSize = staffPageResult.getPageSize();
int pageTotal = staffPageResult.getPageTotal();
int total = staffPageResult.getTotal();

for (Staff staff : staffs) {
    System.out.println(staff);
}

System.out.println("pageNum: " + pageNum);
System.out.println("pageSize: " + pageSize);
System.out.println("pageTotal: " + pageTotal);
System.out.println("total: " + total);

# 输出结果

Staff(id=3, name=张三, role=项目经理, salary=25000.0)
Staff(id=4, name=李四, role=高级开发工程师, salary=18000.0)
Staff(id=5, name=王五, role=开发工程师, salary=15000.0)
Staff(id=6, name=赵六, role=测试工程师, salary=12000.0)
Staff(id=7, name=钱七, role=UI设计师, salary=13000.0)
pageNum: 1
pageSize: 5
pageTotal: 2
total: 10