日拱一卒：MyBatis 动态 SQL

Tinyspot 2023-09-30 原文

1. OGNL表达式

if
choose (when, otherwise)
trim (where, set)
foreach

1.1 <where> 标签

<where>元素只在子元素有内容的情况下才插入 WHERE子句；而且，若子句的开头为 AND 或OR, <where>元素也会将它们去除

<where>
    <if test="name != null and name != '' ">
        and name = #{name}
    </if>
</where>

1.2 <foreach> 标签

<where>
    <if test="names != null and names.size() > 0">
        and name in
        <foreach item="item" collection="names" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
</where>

1.3 <choose> 标签

类比 Java 中的 switch 语句，只会进入其中一个

<sql id="queryCondition">
    <where>
        <choose>
            <when test="name != null and name != ''">
                and name = #{name}
            </when>
            <when test="status != null">
                and status = #{status}
            </when>
            <otherwise>
                and age = 20
            </otherwise>
        </choose>
    </where>
</sql>

1.4 <trim> 标签

四个属性：
prefix，suffix 表示拼接
prefixOverrides，suffixOverrides 表示删除

<trim prefix="and" prefixOverrides="and | or" suffix="," suffixOverrides=","></trim>

2. ${} VS #{}

${}拼接符
- 对传入的参数不会做任何的处理，传递什么就是什么
- 应用场景：设置动态表名或列名
- 缺点：${} 可能导致 SQL 注入
#{}占位符
- 对传入的参数会预编译处理，被当做字符串使用
- 比如解析后的参数值会有引号 select * from user where name = 'tinyspot'
两者之间的关系可类比：JDBC 使用 PreparedStatement 代替 Statement

2.1 示例

<select id="propertyCount" resultType="UserCount">
    select ${property} as property, count(1) as total
    from user
    group by ${property}
</select>

解析后：

==> Preparing: select name as property, count(1) as total from user group by name
==> Parameters: 
<==    Columns: property, total

若改为 group by #{property}
会报错：

### SQL: select name as property, count(1) as total from user  group by ?
### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

3. 标签 <sql> <include> <bind>

3.1 <include> 标签 - SQL复用

<select id="findUsersByCondition" resultType="User">
    select
    <include refid="columns"/>
    from user
    <include refid="queryByPage"/>
</select>

4. 小问题

查询报错：Expected one result (or null) to be returned by selectOne(), but found: 3
解决方式一：用 List<User> 接收
解决方式二：加 limit 1 限制

<select id="findUser" resultType="User">
    select * from user
    <include refid="queryCondition" />
    limit 1
</select>

5. 实战

5.1 分页查询

@RequestMapping("/findUsersByCondition")
public String findUsersByCondition(UserDO userDO) {
    userDO.resetOffset();
    return JSON.toJSONString(userDao.findUsersByCondition(userDO));
}

请求地址：http://localhost:8080/findUsersByCondition?names=tinyspot,xing&page=3&pageSize=10

@Data
public class UserDO extends BaseQuery {
    private static final long serialVersionUID = 6521438352578419238L;

    private Integer id;
    private String name;
    private Integer age;
    private Integer status;
    private List<String> names;

    private boolean forbiddenStatus = false;
    private boolean filterForbiddenStatus = true;
}

@Data
public class BaseQuery implements Serializable {
    private static final long serialVersionUID = 1604477692747202533L;

    public static final int DEFAULT_PAGE_SIZE = 20;
    public static final int MAX_PAGE_SIZE = 100;

    private int pageSize = DEFAULT_PAGE_SIZE;
    private int page = 1;
    private int offset = 0;

    public void resetOffset() {
        this.offset = pageSize * (page - 1);
    }
}

<sql id="columns">
    id, name, age, status, extend, create_time, update_time
</sql>

<sql id="queryByPage">
    <include refid="queryCondition"/>
    <include refid="orderBy"/>
    limit #{offset}, #{pageSize}
</sql>

<sql id="orderBy">
    order by id desc
</sql>

<sql id="queryCondition">
    <where>
        <if test="name != null and name != '' ">
            and name = #{name}
        </if>
        <if test="status != null ">
            and status = #{status}
        </if>
        <if test="names != null and names.size() > 0">
            and name in
            <foreach item="item" collection="names" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <choose>
            <when test="forbiddenStatus==true">
                AND status in (-1)
            </when>
            <otherwise>
                <choose>
                    <when test="filterForbiddenStatus==true">
                        AND status not in (-1, -4)
                    </when>
                    <otherwise>
                        AND status != -4
                    </otherwise>
                </choose>
            </otherwise>
        </choose>
    </where>
</sql>