使用Mybatis动态SQL构建通用自定义高级查询

前言

动态SQL是Mybatis中最强大的特性之一。实际开发中，数据的查询条件是动态多变的，因此动态SQL可以很方便的构建多条件的 where 语句的拼装。但是where条件里的查询条件跟表中的字段还是息息相关，我们在编写*Mapper.xml 文件的时候，还是需要在 <where> 节点里写编写很多的 <if test="condition"> 条件。程序猿都是一种爱偷懒的物种，于是乎，便有了此文的主题『使用Mybatis动态SQL构建通用自定义高级查询』。话不多说，进入正题。

1. 思路

我们需要构建通用的自定义高级查询，那么首先有以下几个必须满足的点：

满足通用的要求，那么查询字段就不能写死在 <where> 节点中。
满足高级查询的要求，那么查询条件的个数就是不确定的。
满足自定义的要求，那么查询操作符肯定不可以限死是 = 或者 like 等。

根据上面提出要求，也就是说我们需要定义一个查询参数去接收前端传到后台的查询条件。查询条件需要满足3个要素：①查询字段②查询操作符③查询数据。当我们接收到查询条件的时候，根据动态SQL的语法构建出动态查询条件。

2. 实现

步骤一：定义高级查询条件模型

AdcanceQuery.java

package com.xkcoding.demo.model.query;

import lombok.Data;

import java.util.List;

/**
 * <p>
 * 高级搜索条件
 * </p>
 *
 * @package: com.xkcoding.demo.model.query
 * @description: 高级搜索条件
 * @author: yangkai.shen
 * @date: Created in 2019-02-14 15:33
 * @copyright: Copyright (c) 2019
 * @version: V1.0
 * @modified: yangkai.shen
 */
@Data
public class AdvanceQuery {
    /**
     * 查询条件
     */
    private String key;
    /**
     * 查询操作符
     */
    private String op;
    /**
     * 查询内容
     */
    private List<Object> value;
}

步骤二：修改对应的Mapper Java文件

UserMapper.java

package com.xkcoding.demo.mapper;

import java.util.List;

import com.xkcoding.demo.common.MyMapper;
import com.xkcoding.demo.model.User;
import com.xkcoding.demo.model.query.AdvanceQuery;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

/**
 * <p>
 * UserMapper
 * </p>
 *
 * @package: com.xkcoding.demo.mapper
 * @description: UserMapper
 * @author: yangkai.shen
 * @date: Created in 2019-02-14 15:33
 * @copyright: Copyright (c) 2019
 * @version: V1.0
 * @modified: yangkai.shen
 */
@Component
public interface UserMapper extends MyMapper<User> {
    /**
     * 查询用户列表
     *
     * @param advanceQueryList 高级搜索条件
     * @return 用户列表
     */
    List<User> selectList(@Param("advanceQuery") List<AdvanceQuery> advanceQueryList);
}

步骤三：修改对应的Mapper XML文件，添加高级查询SQL片段

UserMapper.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.xkcoding.demo.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.xkcoding.demo.model.User">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <result column="dept_id" jdbcType="INTEGER" property="deptId"/>
        <result column="gender" jdbcType="INTEGER" property="gender"/>
        <result column="nickname" jdbcType="VARCHAR" property="nickname"/>
        <result column="open_id" jdbcType="VARCHAR" property="openId"/>
        <result column="birthday" jdbcType="Date" property="birthday"/>
        <result column="last_login_time" jdbcType="Date" property="lastLoginTime"/>
        <result column="last_ip" jdbcType="VARCHAR" property="lastIp"/>
        <result column="lock" jdbcType="INTEGER" property="lock"/>
    </resultMap>

    <select id="selectList" resultType="com.xkcoding.demo.model.User">
        SELECT temp.* FROM (
            SELECT
            t.*
            FROM t_user t
        )temp
        <where>
            <include refid="advance_query_sql"/>
        </where>
    </select>

    <sql id="advance_query_sql">
        <foreach item="item" collection="advanceQuery" open=" AND " separator=" AND " close=" ">
            <choose>
                <when test="item.op != null and item.op == 'eq'">
                    temp.${item.key} = #{item.value[0]}
                </when>
                <when test="item.op != null and item.op == 'gte'">
                    temp.${item.key} &gt;= #{item.value[0]}
                </when>
                <when test="item.op != null and item.op == 'lte'">
                    temp.${item.key} &lt;= #{item.value[0]}
                </when>
                <when test="item.op != null and item.op == 'between'">
                    temp.${item.key} BETWEEN #{item.value[0]} AND #{item.value[1]}
                </when>
                <when test="item.op != null and item.op == 'in'">
                    temp.${item.key} IN
                    <foreach item="value" collection="item.value" open="(" separator="," close=")">
                        #{value}
                    </foreach>
                </when>
                <when test="item.op != null and item.op == 'no'">
                    (temp.${item.key} IS NULL OR trim(temp.${item.key}) = '')
                </when>
                <when test="item.op != null and item.op == 'yes'">
                    (temp.${item.key} IS NOT NULL AND trim(temp.${item.key}) != '')
                </when>
                <when test="item.op != null and item.op == 'notin'">
                    temp.${item.key} NOT IN
                    <foreach item="value" collection="item.value" open="(" separator="," close=")">
                        #{value}
                    </foreach>
                </when>
                <when test="item.op != null and item.op == 'like'">
                    <if test="item.value.size == 1">
                        temp.${item.key} LIKE CONCAT("%", #{item.value[0]}, "%")
                    </if>
                    <if test="item.value.size > 1">
                        <foreach item="value" collection="item.value" open="(" separator=" OR " close=")">
                            temp.${item.key} LIKE CONCAT("%", #{value}, "%")
                        </foreach>
                    </if>
                </when>
                <otherwise>
                    1 = 1
                </otherwise>
            </choose>
        </foreach>
    </sql>
</mapper>

这里改造之前的查询语句，只需要在外层再包一层，并且别名改成temp即可。

如下：

<select>
	SELECT * FROM table where xxx;
</select>

改为：

<select>
 SELECT temp.* FROM
 (
     SELECT * FROM table where xxx;
 )temp
 <where>
     <include refid="advance_query_sql"/>
 </where>
</select>

步骤四：Controller层

查询方法由 @GetMapping 改为 @PostMapping，参数 @RequestBody List<AdvanceQuery> advanceQueryList

3. 测试

使用 PostMan 测试，参数使用 Body 发送 json 数据类似如下格式：

[
  {
    "key": "username",
    "op": "like",
    "value": ["xk", "coding"]
  },
  {
    "key": "gender",
    "op": "eq",
    "value": [1]
  },
  {
    "key": "birthday",
    "op": "between",
    "value": ["1994-11-01 00:00:00", "1994-12-01 00:00:00"]
  }
]

4. 操作符说明

内容	含义	value值备注
eq	等于	有且只有 1 个
gte	大于等于	有且只有 1 个
lte	小于等于	有且只有 1 个
between	介于	有且只有 2 个
in	包含	大于等于 1 个
notin	不包含	大于等于 1 个
no	不存在	空数组
yes	存在	空数组
like	模糊	大于等于 1 个，多个 value 时，会使用 OR 构建查询条件

5. 思考

目前支持的操作符就是以上几种，但是基本够用，其余语法还需要自己扩展。
目前仅支持多条件 AND 拼接。
xml模板目前没有类似继承的特性，所以这个sql片段需要在每个使用到的地方都写一遍，造成代码冗余。后期可以参考Mybatis拦截器的特性，对查询SQL进行封装，解决冗余。

6. 参考

MyBatis 3 | 动态SQL - MyBatis.org