关注: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_time
        FROM 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 != ''">
            , CASE
            WHEN du.username = #{dto.usernameFuzzyQuery} THEN 1
            WHEN du.username LIKE CONCAT(#{dto.usernameFuzzyQuery}, '%') THEN 2
            WHEN du.username LIKE CONCAT('%', #{dto.usernameFuzzyQuery}) THEN 3
            ELSE 4
            END 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;                    // 用户ID
    private 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 {

    @Autowired
    private 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 {

    @Autowired
    private 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_time
    FROM 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.beginTimedto.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 != ''">
    , CASE
    WHEN du.username = #{dto.usernameFuzzyQuery} THEN 1
    WHEN du.username LIKE CONCAT(#{dto.usernameFuzzyQuery}, '%') THEN 2
    WHEN du.username LIKE CONCAT('%', #{dto.usernameFuzzyQuery}) THEN 3
    ELSE 4
    END 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 语句实现了对模糊查询结果的排序优化。