动态SQL

在实际开发中，在执行查询语句时，通常会携带多个条件或者无条件查询，而这些条件由用户指定，例如：

# 查询name包含丽丽，且age大于18的用户
select * from tb_user where name like '%丽丽%' and age > 18;

# 查询name包含丽丽
select * from tb_user where name like '%丽丽%';

# 查询age大于18的用户
select * from tb_user where age > 18;

# 查询所有用户
select * from tb_user;

上述的四条查询语句中的查询条件都是由用户来指定的，可任意进行组合，查询条件可有可无。此时我们就可以通过动态SQL来实现查询语句的条件动态拼接。

if

用来判断用户是否输入某条件，如果输入了再将该条件拼接到sql语句。例如：

<select id="selectUser" resultType="User">
    select * from tb_user where 1=1
    <if test="name != null and name != ''">
        and name like concat('%',#{name},'%')
    </if>
    <if test="age != null and age != ''">
        and age > #{age}
    </if>
</select>

where1=1是为了防止用户在没有传递查询条件时导致 SQL 语句错误。

where 标签

上述的where1=1并没有什么实际意义，仅仅是为了防止 SQL 语句错误。因此我们可以通过where标签来解决这个问题。

<select id="selectUser" resultType="User">
    select * from tb_user
    <where>
        <if test="name != null and name != ''">
            name like concat('%',#{name},'%')
        </if>
        <if test="age != null and age != ''">
            and age > #{age}
        </if>
    </where>
</select>

在使用where标签后，就不需要再写where 1=1了，同时我们可以看到if标签中的第一个 SQL 语句也不需要添加and。

trim

上面使用where标签来完成查询操作，如果where的元素与我们期望的不一样时，可以使用trim来定制where元素的功能。

<select id="selectUser" resultType="User">
    select * from tb_user
    <!-- prefix: 设置前缀, prefixOverrides: 去除多余的前缀， suffix: 设置后缀, suffixOverrides: 去除多余的后缀 -->
    <trim prefix="where" prefixOverrides="and | or">
        <if test="name != null and name != ''">
            name like concat('%',#{name},'%') and
        </if>
        <if test="age != null and age != ''">
            age > #{age}
        </if>
    </trim>
</select>

其中 MyBatis 会根据实际元素是否满足条件来判断添加或删除AND，OR

choose、when、otherwise

这三个标签分别对应 Java 中的switch、case、default。

<select id="selectUser" resultType="User">
    select * from tb_user
    <where>
        <choose>
            <when test="name != null and name != ''">
                and name like concat('%',#{name},'%')
            </when>
            <when test="username != null and username != ''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="age != null">
                and age > #{age}
            </when>
            <otherwise>
                and id = 1
            </otherwise>
        </choose>
    </where>
</select>

foreach

select * from tb_user where id in (5,6,7,8,9,10);

<select id="findUserByIds" resultType="User">
    select * from tb_user where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

<insert id="insertUserList" useGeneratedKeys="true" keyProperty="id">
    insert into tb_user (name,username, age) values
    <foreach collection="list" item="user" separator=",">
        (#{user.name},#{user.username}, #{user.age})
    </foreach>
</insert>

sql

sql标签用于定义可重用的sql片段。

<sql id="userColumns">
    id, name, username, age, email, balance, info, id_card_number
</sql>
<select id="findUserByIds" resultType="User">
    select
    <include refid="userColumns"/>
    from tb_user where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>