PageBean 以及动态SQL

SpringBoot学习第四天：PageBean 以及动态SQL

1）PageBean

分页查询是绝大多数 Web 页面都需要实现的功能，目的是限制每页显示的数据数量。通过分页可以有效地提高查询效率，避免一次性加载大量数据。在后端开发中，如果直接通过 SQL 查询语句实现分页，通常会使用类似下面的 SQL 语句：

SELECT * FROM dept LIMIT 1, 4;

这条 SQL 语句的作用是从 dept 表中查询数据，返回 从第 2 行开始的 4 条记录。

• LIMIT 子句：用于限制查询结果的数量。
  • 第一个参数 1：表示偏移量，即从第 2 行开始（行号从 0 开始）。
  • 第二个参数 4：表示返回的最大行数。

Java实现

public PageBean page(Integer page, Integer pageSize){  
    // 获取总记录数
    Long count = empMapper.count();  
  
    // 计算偏移量，获取结果列表
    Integer start = (page - 1) * pageSize;  
    List<Emp> empList = empMapper.page(start, pageSize);  
  
    // 创建并返回 PageBean 对象
    PageBean pageBean = new PageBean(count, empList);  
    return pageBean;  
}

上述代码中，我们通过 empMapper.count() 获取总记录数，计算分页偏移量后，再通过 empMapper.page(start, pageSize) 获取当前页的数据。最终，我们将总记录数和结果列表封装成 PageBean 对象返回。

---

使用 PageHelper 插件简化分页

PageHelper 是 MyBatis 提供的分页插件，它简化了分页查询的操作，不需要手动计算偏移量。以下是基于 PageHelper 实现分页的例子：

public PageBean page(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end) {   
    // 启动分页插件，自动添加 LIMIT 子句
    PageHelper.startPage(page, pageSize);  
  
    // 查询数据
    List<Emp> empList = empMapper.list(name, gender, begin, end);  
   
    // 获取分页结果并封装为 PageBean
    Page<Emp> p = (Page<Emp>) empList;
    PageBean pageBean = new PageBean(p.getTotal(), p.getResult());  
    return pageBean;  
}

empMapper.list 对应的 SQL 查询如下：

<select id="list" resultType="com.itheima.practice.pojo.Emp">  
    SELECT *  
    FROM emp    
    <where>  
        <if test="name != null">  
            name LIKE concat('%', #{name}, '%')  
        </if>  
  
        <if test="gender != null">  
            AND gender = #{gender}  
        </if>  
  
        <if test="begin != null AND end != null">  
            AND entrydate BETWEEN #{begin} AND #{end}  
        </if>  
    </where>  
    ORDER BY update_time DESC  
</select>

通过 PageHelper.startPage(page, pageSize)，PageHelper 会自动拦截查询，并为其添加分页功能，相当于在 SQL 查询中自动插入 LIMIT 子句。这样，查询的结果将只包含指定页的数据。

---

2）动态SQL

在后端开发中，SQL 是不可或缺的重要组成部分，尤其是当需要根据不同条件查询数据时。假设数据库中有这样一张表：

username	password	name	gender	job	entrydate	dept_id	create_time	update_time
john	5f4dcc3b5	John	1	1	2021-06-15	101	2021-06-15 10:30:00	2023-10-01 15:00:00
alice	1a79a4d60	Alice	0	2	2020-09-01	102	2020-09-01 09:00:00	2023-08-20 18:45:00
bob	e99a18c42	Bob	1	3	2019-03-25	103	2019-03-25 12:15:00	2023-11-15 09:20:00
jane	098f6bcd	Jane	0	4	2022-01-10	104	2022-01-10 11:40:00	2023-09-25 16:10:00
tom	d41d8cd9	Tom	1	1	2023-02-05	101	2023-02-05 08:30:00	2023-10-10 14:00:00

有时我们需要通过 gender 和 job 查询数据，SQL 可以写成：

SELECT * 
FROM your_table_name
WHERE gender = ? AND job = ?;

显然有时候我们又会通过其他的参数来查询数据，例如只用username和job查询数据

SELECT * 
FROM your_table_name
WHERE username = ? AND job = ?;

又或是只通过gender查询等等，查询的方式有千万种，我们不可能在后端中定义所有的查询方式，这样是在太臃肿、繁琐了，一个很好的解决方案是通过动态SQL，代码如下(XML配置文件中)

<update id="update">
    UPDATE emp
    <set>
        <if test="emp.username != null">username = #{emp.username},</if>
        <if test="emp.password != null">password = #{emp.password},</if>
        <if test="emp.name != null">name = #{emp.name},</if>
        <if test="emp.gender != null">gender = #{emp.gender},</if>
        <if test="emp.job != null">job = #{emp.job},</if>
        <if test="emp.entrydate != null">entrydate = #{emp.entrydate},</if>
        <if test="emp.deptId != null">dept_id = #{emp.deptId},</if>
        <if test="emp.updateTime != null">update_time = #{emp.updateTime},</if>
    </set>
</update>

解释：

• <if test="emp.xxx != null">：判断 emp 对象中的某个属性是否为空（null），如果不为空，则将该字段拼接到 SQL 中。

  • 例如，如果 emp.username 不为 null，则生成 username = #{emp.username},。
  • 如果 emp.password 不为 null，则生成 password = #{emp.password},。

• <set>：用于动态构建 SQL 的 SET 子句。MyBatis 会自动添加逗号，并跳过空字段。

示例：假设我们只输入 gender

如果只输入了 gender 参数，真实的 SQL 会是：

UPDATE emp
SET gender = ?

• <if test="emp.gender != null">：判断 gender 是否为 null，若不为空，则生成 gender = #{emp.gender}。

其他字段由于值为空（null），不会被包含在 SET 子句中。

动态 SQL 的优势

• 简洁：不需要为每种查询条件编写独立的 SQL。
• 灵活：可以根据传入的条件动态生成不同的 SQL。
• 可维护：在增加或修改查询条件时，只需修改 XML 配置，而不需要修改 Java 代码。