日拱一卒MyBatis 动态 SQL

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>

解析后：

==> Preparing: select id , name, age, status, create_time, update_time from user WHERE name in ( ? , ? ) AND status not in (-1, -4) order by id desc limit ?, ?
==> Parameters: tinyspot(String), xing(String), 20(Integer), 10(Integer)
<==      Total: 0

5.2 更新

请求地址 http://localhost:8080/update?id=3&status=2
请求地址 http://localhost:8080/updateUser?id=3

@RequestMapping("update")
public Integer update(UserDO userDO) {
    return userDao.update(userDO);
}

@RequestMapping("updateUser")
public Integer updateUser(UserDO userDO) {
    userDO.setExtend("{\"time\":\"2022-10-01\"}");
    UpdateOption option = new UpdateOption();
    option.setUpdateExtend(true);
    return userDao.updateUser(userDO, option);
}

更新模型

public interface UserDao {
    Integer update(UserDO userDO);

    Integer updateUser(@Param("userDO") UserDO userDO, @Param("option") UpdateOption option);
}

@Data
public class UpdateOption implements Serializable {
    private static final long serialVersionUID = 4974510783148384827L;
    private boolean updateExtend = false;
}

<update id="update">
    update user set
    <if test="status != null">
        status = #{status}
    </if>
    where id = #{id}
</update>

<update id="updateUser">
    update user set
    <if test="userDO.status != null">
        status = #{userDO.status}
    </if>
    <if test="option.updateExtend == true">
        <if test="userDO.extend != null and userDO.extend != ''">
            extend = #{userDO.extend}
        </if>
    </if>
    where id = #{userDO.id}
</update>

5.3 插入

<insert id="insert">
    insert into user (name, age, status, extend)
    values (#{name}, #{age}, #{status}, #{extend})
</insert>

<insert id="insertUser">
    insert into user
    <trim prefix='(' suffix=')' suffixOverrides=','>
        create_time, update_time,
        <if test="name != null and name != ''">
            name,
        </if>
        <if test="status != null">
            status,
        </if>
    </trim>
    <trim prefix='values (' suffix=')' suffixOverrides=','>
        now(),
        now(),
        <if test="name != null and name != ''">
            #{name},
        </if>
        <if test="status != null">
            #{status},
        </if>
    </trim>
</insert>

这篇好文章是转载于：学新通技术网