MyBatis的动态sql

使用MyBatis框架操作数据库时，有些条件操作是需要动态指定的，可以使用MyBatis的标签来动态指定条件参数，执行sql操作。

数据准备

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Achievement {
    private String id;
    private String name;
    private Integer age;
    private String gender;
    private Integer score;
    private String hobby;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class AchievementStatistics {
    private String id;
    private String username;
    private String grade;
}

CREATE TABLE `achievement` (
  `id` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `gender` varchar(32) DEFAULT NULL,
  `score` int DEFAULT NULL,
  `hobby` varchar(32) DEFAULT NULL
) ENGINE=InnoDB

 

一 if标签

1.1 if标签的语法

<if test=" ">
        and 字段名 操作 字段值
</if>
if标签有一个且必需要有的属性test，test属性的属性值是表达式：
        若表达式的结果为true，则if标签中的内容会执行；
        若表达式的结果为false，则if标签中的内容不会执行；
后台代码操作数据库时，有些条件参数的指定是不确定的，这时就需要使用到if标签

1.2 代码示例

List<Achievement> queryAchievementList(Integer age, String gender, String hobby);

<select id="queryAchievementList" resultType="com.mango.domain.Achievement">
    select * from achievement
    where 1=1
    <if test="age != null">
        and age = #{age}
    </if>
    <if test="gender != null and gender != ''">
        and gender = #{gender}
    </if>
    <if test="hobby != null and hobby != ''">
        and hobby = #{hobby}
    </if>
</select>

1.3 测试代码

@Test
public void testIf() {
    int age = 18;
    String gender = "male";
    String hobby = "read";
    List<Achievement> achievementList = 
            achievementMapper.queryAchievementList(age, gender, hobby);
    System.out.println(achievementList);
}

==>  Preparing: select * from achievement where 1=1 and age = ? and gender = ? and hobby = ?
==> Parameters: 18(Integer), male(String), read(String)
<==    Columns: id, name, age, gender, score, hobby
<==        Row: 1001, jack1, 18, male, 99, read
<==      Total: 1

@Test
public void testIf() {
    int age = 18;
    String gender = "male";
    String hobby = null;
    List<Achievement> achievementList =
            achievementMapper.queryAchievementList(age, gender, hobby);
    System.out.println(achievementList);
}

==>  Preparing: select * from achievement where 1=1 and age = ? and gender = ?
==> Parameters: 18(Integer), male(String)
<==    Columns: id, name, age, gender, score, hobby
<==        Row: 1001, jack1, 18, male, 99, read
<==        Row: 1002, jack2, 18, male, 27, Sanda
<==      Total: 2

二 choose&when&otherwise标签

<choose>
    <when test=" ">
            and 字段名 操作 字段值
    </when>
    <otherwise>
            and 字段名 操作 字段值
    </otherwise>
</choose>

2.1 choose标签的语法

choose标签没有属性，有两个的子标签when标签和otherwise标签，when标签有test属性，值为true则when标签条件成立，值为false则when标签条件不成立，otherwise标签没有属性；
choose标签和when标签、otherwise标签配合使用，类似编程语言的switch... case... default...组合，有一个满足条件就不再往下匹配条件；

2.2 代码示例

List<Achievement> queryAchievementList2(Integer age, String gender, String hobby);

<select id="queryAchievementList2" resultType="com.mango.domain.Achievement">
    select * from achievement
    where 1=1
    <choose>
        <when test="age != null">
            and age = #{age}
        </when>
        <when test="gender != null and gender != ''">
            and gender = #{gender}
        </when>
        <when test="hobby != null and hobby != ''">
            and hobby = #{hobby}
        </when>
        <otherwise>
            and hobby in ('read', 'tourism')
        </otherwise>
    </choose>
</select>

2.3 测试代码

@Test
public void testChoose() {
    Integer age = null;
    String gender = "female";
    String hobby = null;
    List<Achievement> achievementList =
            achievementMapper.queryAchievementList2(age, gender, hobby);
    System.out.println(achievementList);
}

==>  Preparing: select * from achievement where 1=1 and gender = ?
==> Parameters: female(String)
<==    Columns: id, name, age, gender, score, hobby
<==        Row: 1003, jack3, 19, female, 25, tourism
<==        Row: 1004, jack4, 17, female, 83, tourism
<==        Row: 1005, jack5, 21, female, 77, Sanda
<==      Total: 3

@Test
public void testChoose() {
    Integer age = null;
    String gender = "";
    String hobby = null;
    List<Achievement> achievementList =
            achievementMapper.queryAchievementList2(age, gender, hobby);
    System.out.println(achievementList);
}

==>  Preparing: select * from achievement where 1=1 and hobby in ('read', 'tourism')
==> Parameters: 
<==    Columns: id, name, age, gender, score, hobby
<==        Row: 1001, jack1, 18, male, 99, read
<==        Row: 1003, jack3, 19, female, 25, tourism
<==        Row: 1004, jack4, 17, female, 83, tourism
<==        Row: 1006, jack6, 15, male, 67, read
<==      Total: 4

if标签和choose标签的区别：if标签如果有多个条件满足，那么多个条件会进行拼接执行，choose标签有多个条件传入时会选择第一满足的条件执行。

2.4 case函数

choose标签及其子标签when和otherwise标签用在where条件后面，筛选出数据；
case函数用在查询结果后的判断，配合when、then、else和end等关键字使用；

List<AchievementStatistics> queryAchievementStatisticsList();

<select id="queryAchievementStatisticsList"
        resultType="com.mango.domain.AchievementStatistics">
    select
        id as old_id,
        name as username,
        case
            when score > 90 then '优秀'
            when score > 80 and score <![CDATA[<=]]> 90 then '良好'
            when score > 60 and score <![CDATA[<=]]> 80 then '合格'
            else '不合格'
        end
        as grade
    from achievement
</select>

三 where标签

3.1 where标签的语法

where标签嵌套着其他子标签配合着使用，譬如if标签和choose标签：
如果where标签里面的if子标签有条件成立，那么在sql中动态生成where关键字，而且第一个if标签条件成立的标签，里面的and关键字会被去掉；
        第一个if标签条件成立的标签，里面的and关键字会被去掉，如果没写and关键字也不会出错，但是第二个往后if标签条件成立的标签，里面的and关键字必须都要写，建议全都写上；
        if标签体里面除了可以写and关键字，而且还可以写or关键字，但是都最好写在sql短语的前面，才能去掉and或or关键字；
        choose标签和where标签配合使用时，where关键字的取舍和if标签一样；
如果where标签里面的子标签没有条件成立，那么不会在sql中生成where关键字；

3.2 代码示例

List<Achievement> queryAchievementList(Integer age, String gender, String hobby);
List<Achievement> queryAchievementList2(Integer age, String gender, String hobby);

<select id="queryAchievementList" resultType="com.mango.domain.Achievement">
    select * from achievement
    <where>
        <if test="age != null">
            and age = #{age}
        </if>
        <if test="gender != null and gender != ''">
            and gender = #{gender}
        </if>
        <if test="hobby != null and hobby != ''">
            and hobby = #{hobby}
        </if>
    </where>
</select>

<select id="queryAchievementList2" resultType="com.mango.domain.Achievement">
    select * from achievement
    <where>
        <choose>
            <when test="age != null">
                and age = #{age}
            </when>
            <when test="gender != null and gender != ''">
                and gender = #{gender}
            </when>
            <when test="hobby != null and hobby != ''">
                and hobby = #{hobby}
            </when>
            <otherwise>
                and hobby in ('read', 'tourism')
            </otherwise>
        </choose>
    </where>
</select>

四 foreach标签

4.1 foreach标签的语法

foreach标签多用于批量操作，foreach标签的常用属性：
        collection属性：设置要循环的数组或集合，业务层调用mapper接口传来的数组或集合数据；
        item属性：遍历的集合或数组中的每一项数据；
        separator属性：设置集合或数组中的每一项数据之间的分隔符；
        open属性：设置在foreach标签中循环的所有内容的开始符号；
        close属性：设置foreach标签中循环的所有内容的结束符号；

4.2 foreach批量增加

int insertBatch(@Param("achievementList") List<Achievement> achievementList);

<insert id="insertBatch">
    insert into achievement(id, name, age, gender, score, hobby) values
    <foreach collection="achievementList" item="achievement" separator=",">
        (#{achievement.id}, #{achievement.name}, #{achievement.age},
         #{achievement.gender}, #{achievement.score}, #{achievement.hobby})
    </foreach>
</insert>

4.3 foreach批量修改

int updateBatch(@Param("idList") List<String> idList);

<update id="updateBatch">
    update achievement set score = 100 where id in
    <foreach collection="idList" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</update>

4.4 foreach批量查询

List<Achievement> selectBatch(@Param("idList") List<String> idList);

<select id="selectBatch" resultType="com.mango.domain.Achievement">
    select id, name, age, gender, score, hobby from achievement where id in
    <foreach collection="idList" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</select>
<select id="selectBatch" resultType="com.mango.domain.Achievement">
    select id, name, age, gender, score, hobby from achievement where
    <foreach collection="idList" item="id" separator="or">
        id=#{id}
    </foreach>
</select>

4.5 foreach批量删除

int deleteBatch(@Param("idList") List<String> idList);

<delete id="deleteBatch">
    delete from achievement where id in
    <foreach collection="idList" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>
</delete>

五 sql标签

sql标签记录一段公共sql片段，在其他标签中使用include标签进行引入。

<sql id="achievement_fi">
    select * from achievement
</sql>
<select id="queryAchievementList" resultType="com.mango.domain.Achievement">
    <include refid="achievement_fi" />
    <where>
        <if test="age != null">
            and age = #{age}
        </if>
        <if test="gender != null and gender != ''">
            and gender = #{gender}
        </if>
        <if test="hobby != null and hobby != ''">
            and hobby = #{hobby}
        </if>
    </where>
</select>

<select id="queryAchievementList2" resultType="com.mango.domain.Achievement">
    <include refid="achievement_fi" />
    <where>
        <choose>
            <when test="age != null">
                and age = #{age}
            </when>
            <when test="gender != null and gender != ''">
                and gender = #{gender}
            </when>
            <when test="hobby != null and hobby != ''">
                and hobby = #{hobby}
            </when>
            <otherwise>
                and hobby in ('read', 'tourism')
            </otherwise>
        </choose>
    </where>
</select>