SpringBoot | 基于 MyBatis 的分页与模糊查询的开发模板

关注：CodingTechWork

引言

  在开发 Web 应用时，常常需要处理复杂的查询需求，尤其是在涉及到用户管理功能时，分页查询和模糊查询是常见的需求之一。
  本文将通过一个具体的示例，展示如何使用 MyBatis实现分页和模糊查询，特别是如何使用CASE...WHEN...THEN语句进行用户名的模糊查询，并且支持其他精确查询条件。

需求背景

  我们需要实现一个分页查询功能，查询系统中的用户信息。每个用户包括用户名、用户组、邮箱、状态、注册时间等字段。此外，查询功能支持以下条件：

1. 用户名模糊查询（其中精准匹配排序优先，左右匹配也有优先级）
2. 用户组、状态的精确查询
3. 注册时间范围查询
4. 支持分页
5. 时间排序
   最终，我们的目标是使用MyBatis提供的动态 SQL 功能，构建灵活的查询条件，并实现分页查询的功能。

实践模板

数据库设计

假设我们有一个 demo_user 表，存储了用户信息。表结构大致如下：

CREATE TABLE demo_user (id BIGINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(255) NOT NULL,user_group VARCHAR(255),email VARCHAR(255),status INT,registration_time DATETIME,is_deleted INT DEFAULT 0
);

MyBatis 配置Mapper XML 配置

  在 MyBatis 中，我们需要通过 XML 映射文件来定义 SQL 查询语句。我们将编写一个分页查询的 SQL，其中包含模糊查询和精确查询的条件。

<?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.selfcode.demo.mapper.UserMapper"><!-- 公共SQL，用于查询用户信息 --><sql id="list_user">SELECT du.id,du.username,du.user_group,du.email,du.status,du.registration_timeFROM demo_user du</sql><!-- 分页查询用户信息 --><select id="pageListUser" resultType="com.selfcode.demo.vo.UserVO"><include refid="list_user"></include><where><!-- 用户名模糊查询 --><if test="dto.usernameFuzzyQuery != null and dto.usernameFuzzyQuery != ''">AND du.username LIKE CONCAT('%', #{dto.usernameFuzzyQuery}, '%')</if><!-- 用户组精确查询 --><if test="dto.userGroup != null and dto.userGroup != ''">AND du.user_group = #{dto.userGroup}</if><!-- 用户状态精确查询 --><if test="dto.status != null">AND du.status = #{dto.status}</if><!-- 注册时间范围查询 --><if test="dto.beginTime != null and dto.endTime != null">AND du.registration_time BETWEEN #{dto.beginTime} AND #{dto.endTime}</if><!-- 未删除的用户 -->AND du.is_deleted = 0</where><!-- 默认按照用户状态（开启）和注册时间倒序排列 -->ORDER BY du.status DESC, du.registration_time DESC<!-- 用户名模糊查询的优先级排序 --><if test="dto.usernameFuzzyQuery != null and dto.usernameFuzzyQuery != ''">, CASEWHEN du.username = #{dto.usernameFuzzyQuery} THEN 1WHEN du.username LIKE CONCAT(#{dto.usernameFuzzyQuery}, '%') THEN 2WHEN du.username LIKE CONCAT('%', #{dto.usernameFuzzyQuery}) THEN 3ELSE 4END ASC</if></select></mapper>

Mapper 接口

  接下来，我们需要定义一个 Java 接口来与Mapper XML配置文件进行映射。我们在接口中声明 pageListUser 方法来执行分页查询。

package com.selfcode.demo.mapper;import com.selfcode.demo.dto.UserDTO;
import com.selfcode.demo.vo.UserVO;
import org.apache.ibatis.annotations.Param;import java.util.List;public interface UserMapper {/*** 分页查询用户信息** @param dto 查询条件* @return 用户信息列表*/List<UserVO> pageListUser(@Param("dto") UserDTO dto);
}

查询条件 DTO

  在 Java 中，我们定义一个 UserDTO 类来封装查询条件，包括分页参数、用户名模糊查询、用户组、状态和注册时间范围等。

package com.selfcode.demo.dto;import java.util.Date;public class UserDTO {private String usernameFuzzyQuery;  // 模糊查询的用户名private String userGroup;           // 精确查询用户组private Integer status;             // 精确查询用户状态private Date beginTime;             // 查询起始时间private Date endTime;               // 查询结束时间private Integer pageNo = 1;         // 页码，默认从1开始private Integer pageSize = 10;      // 每页显示的记录数，默认每页10条// Getters 和 Setters
}

返回值 VO

  UserVO类用于存储查询返回的用户信息数据。它包括用户的 ID、用户名、用户组、邮箱、状态和注册时间等信息。

package com.selfcode.demo.vo;import java.util.Date;public class UserVO {private Long id;                    // 用户IDprivate String username;            // 用户名private String userGroup;           // 用户组private String email;               // 邮箱private Integer status;             // 用户状态（例如：启用、禁用）private Date registrationTime;      // 注册时间// Getters 和 Setters
}

服务层

  在服务层，我们定义 UserService 类来调用 Mapper 进行数据库查询。UserService 负责接收前端的请求，处理查询逻辑并返回结果。

package com.selfcode.demo.service;import com.selfcode.demo.dto.UserDTO;
import com.selfcode.demo.mapper.UserMapper;
import com.selfcode.demo.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class UserService {@Autowiredprivate UserMapper userMapper;/*** 获取用户分页列表** @param dto 查询条件* @return 用户列表*/public List<UserVO> getUserList(UserDTO dto) {return userMapper.pageListUser(dto);}
}

控制器层

  最后，在控制器层，我们接收来自前端的请求，调用UserService方法来获取分页查询的结果，并返回给客户端。

package com.selfcode.demo.controller;import com.selfcode.demo.dto.UserDTO;
import com.selfcode.demo.service.UserService;
import com.selfcode.demo.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestController
@RequestMapping("/api/user")
public class UserController {@Autowiredprivate UserService userService;/*** 获取分页查询用户列表** @param username 模糊查询的用户名* @param userGroup 用户组* @param pageNo 当前页* @param pageSize 每页显示条数* @return 用户列表*/@GetMapping("/list")public List<UserVO> listUsers(@RequestParam(defaultValue = "") String username,@RequestParam(defaultValue = "") String userGroup,@RequestParam(defaultValue = "1") Integer pageNo,@RequestParam(defaultValue = "10") Integer pageSize) {UserDTO dto = new UserDTO();dto.setUsernameFuzzyQuery(username);dto.setUserGroup(userGroup);dto.setPageNo(pageNo);dto.setPageSize(pageSize);return userService.getUserList(dto);}
}

MyBatis Mapper XML 配置详细解析

  在上述示例中，XML 配置文件主要用于定义 SQL 查询语句，并通过 MyBatis 的映射机制将 SQL 与 Java 方法进行绑定。接下来，我们将详细解析 Mapper XML 文件中的各个部分，包括 SQL 语句、动态 SQL 标签、条件判断、排序等内容。

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.selfcode.demo.mapper.UserMapper">

1. <?xml version="1.0" encoding="UTF-8"?>：声明这是一个 XML 文件，并且使用 UTF-8 编码。
2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">：引入 MyBatis 3.0 的 DTD 定义文件，这个声明确保 XML 文件遵循 MyBatis 的规范。
3. <mapper namespace="com.selfcode.demo.mapper.UserMapper">：定义了 Mapper 的命名空间，与 Java 中的 UserMapper接口类进行关联。MyBatis 会根据这个 namespace来找到对应的 SQL 配置，进行映射。

定义公共 SQL 语句 ()

<sql id="list_user">SELECT du.id,du.username,du.user_group,du.email,du.status,du.registration_timeFROM demo_user du
</sql>

1. <sql>：用于定义一个公共的 SQL 片段。通过id="list_user"来给这个 SQL 片段命名，其他地方可以通过 <include> 标签来引用它。
2. SELECT：这是查询 demo_user 表的 SQL 语句，选择了用户的 id、username、user_group、email、status 和 registration_time 等字段。

分页查询的 SQL

(<select id="pageListUser" resultType="com.selfcode.demo.vo.UserVO">)

<select id="pageListUser" resultType="com.selfcode.demo.vo.UserVO"><include refid="list_user"></include><where><!-- 条件部分 --></where><order by><!-- 排序部分 --></order by>
</select>

1. <select>：定义一个 SELECT 查询的 SQL 语句。id="pageListUser" 表示该 SQL 语句映射到 UserMapper 接口中的 pageListUser 方法。
2. resultType="com.selfcode.demo.vo.UserVO"：指定该查询返回的数据类型为UserVO（一个 VO 对象，用于存储返回的用户信息）。

引用公共 SQL

<include refid="list_user"></include>

1. <include refid="list_user">：通过 <include> 标签引用上面定义的公共 SQL 片段。这样可以避免重复编写相同的查询字段，提升代码的复用性。

动态条件查询

<where>

<where><!-- 用户名模糊查询 --><if test="dto.usernameFuzzyQuery != null and dto.usernameFuzzyQuery != ''">AND du.username LIKE CONCAT('%', #{dto.usernameFuzzyQuery}, '%')</if><!-- 用户组精确查询 --><if test="dto.userGroup != null and dto.userGroup != ''">AND du.user_group = #{dto.userGroup}</if><!-- 用户状态精确查询 --><if test="dto.status != null">AND du.status = #{dto.status}</if><!-- 注册时间范围查询 --><if test="dto.beginTime != null and dto.endTime != null">AND du.registration_time BETWEEN #{dto.beginTime} AND #{dto.endTime}</if><!-- 未删除的用户 -->AND du.is_deleted = 0
</where>

1. <where>：<where> 标签用于处理 SQL 中的 WHERE 子句。MyBatis 会自动处理在动态 SQL 中添加 AND 或 OR 的位置，避免出现多余的 AND（例如，如果没有任何条件时，WHERE 会自动去掉）。
2. <if test="...">：动态 SQL 条件判断。如果 test 表达式的条件成立，才会执行对应的 SQL 片段。比如，只有当 dto.usernameFuzzyQuery 不为空时，才会添加用户名的模糊查询条件。

用户名模糊查询：

<if test="dto.usernameFuzzyQuery != null and dto.usernameFuzzyQuery != ''">AND du.username LIKE CONCAT('%', #{dto.usernameFuzzyQuery}, '%')
</if>

• 如果 dto.usernameFuzzyQuery 不为空，则生成 AND du.username LIKE '%...%' 条件进行模糊匹配。

用户组精确查询：

<if test="dto.userGroup != null and dto.userGroup != ''">AND du.user_group = #{dto.userGroup}
</if>

• 如果 dto.userGroup 不为空，则会生成 AND du.user_group = '...' 条件。

用户状态精确查询：

<if test="dto.status != null">AND du.status = #{dto.status}
</if>

• 如果dto.status 不为 null，则添加AND du.status = ...条件进行精确查询。

注册时间范围查询：

<if test="dto.beginTime != null and dto.endTime != null">AND du.registration_time BETWEEN #{dto.beginTime} AND #{dto.endTime}
</if>

• 如果dto.beginTime和 dto.endTime 都不为空，则生成一个时间范围查询 BETWEEN 子句。

软删除条件：

AND du.is_deleted = 0

• 这是一个硬编码的条件，用来过滤掉已删除的用户，假设 is_deleted = 1 表示用户已删除。

排序

<order by>

ORDER BY du.status DESC, du.registration_time DESC
<if test="dto.usernameFuzzyQuery != null and dto.usernameFuzzyQuery != ''">, CASEWHEN du.username = #{dto.usernameFuzzyQuery} THEN 1WHEN du.username LIKE CONCAT(#{dto.usernameFuzzyQuery}, '%') THEN 2WHEN du.username LIKE CONCAT('%', #{dto.usernameFuzzyQuery}) THEN 3ELSE 4END ASC
</if>

1. ORDER BY：默认按 status字段降序排列，如果status相同，再按 registration_time 降序排列。
2. CASE语句：如果用户提供了usernameFuzzyQuery 进行模糊查询，我们根据匹配程度来排序。具体逻辑如下：
   • 如果用户名完全匹配 dto.usernameFuzzyQuery，则给这个记录排序为优先（1）。
   • 如果用户名是以 dto.usernameFuzzyQuery 开头匹配，排序为第二（2）。
   • 如果用户名是以 dto.usernameFuzzyQuery 结尾匹配，排序为第三（3）。
   • 其他情况，排序为最后（4）。
     通过这种方式，我们将更精确的匹配记录排在前面，提升用户体验。

总结

  本文展示了如何使用 MyBatis 实现一个分页和模糊查询的用户列表查询功能。通过灵活的动态 SQL 配置，我们可以根据用户的输入条件动态生成查询语句，从而实现高效的数据库查询。
  这个 XML 配置通过 MyBatis 动态 SQL 功能，结合 标签、 标签和 CASE 语句，实现了以下功能：

1. 分页查询：可以根据传入的条件动态生成 WHERE 子句，支持多种查询条件（模糊查询、精确查询、时间范围查询）。
2. 动态条件：根据用户输入的条件，动态构建 SQL 查询，避免了不必要的条件拼接。
3. 排序优化：通过 CASE 语句实现了对模糊查询结果的排序优化。