MyBatis ---- 动态SQL

MyBatis 框架的动态 SQL 技术是一种根据特定条件动态拼接 SQL 语句的功能，它存在的意义是为了解决拼接 SQL 语句字符串时的痛点问题。

1. if

    /**
     * 根据条件查询员工信息if
     * @param emp
     * @return
     */
    List<Emp> getEmpListByMoreTJ(Emp emp);
1
2
3
4
5
6

    
    <select id="getEmpListByMoreTJ" resultType="emp">
        select * from t_emp where
        <if test="empName != '' and empName != null">
            emp_name = #{empName}
        if>
        <if test="age != '' and age != null">
            and age = #{age}
        if>
        <if test="sex != '' and sex != null">
            and sex = #{sex}
        if>
        <if test="email != '' and email != null">
            and email = #{email}
        if>
    select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

当 age、sex…等条件为空时，不会影响查询结果，但是若 empName 为空的话，查询语句就会变成 where 后面直接跟 and，这是不符合语法规则的，避免措施：
在查询语句中添加一个 1=1，恒成立条件，且不会影响查询效果

    
    <select id="getEmpListByMoreTJ" resultType="emp">
        select * from t_emp where 1=1
        <if test="empName != '' and empName != null">
            and emp_name = #{empName}
        if>
        <if test="age != '' and age != null">
            and age = #{age}
        if>
        <if test="sex != '' and sex != null">
            and sex = #{sex}
        if>
        <if test="email != '' and email != null">
            and email = #{email}
        if>
    select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2. where

    /**
     * 根据条件查询员工信息where
     * @param emp
     * @return
     */
    List<Emp> getEmpListByMoreTJ2(Emp emp);
1
2
3
4
5
6

    
    <select id="getEmpListByMoreTJ2" resultType="emp">
        select * from t_emp
        <where>
            <if test="empName != '' and empName != null">
                emp_name = #{empName}
            if>
            <if test="age != '' and age != null">
                and age = #{age}
            if>
            <if test="sex != '' and sex != null">
                and sex = #{sex}
            if>
            <if test="email != '' and email != null">
                and email = #{email}
            if>
        where>
    select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

where 和 if 一般结合使用：
a> 若 where 标签找那个的 if 条件都不满足，则 where 标签没有任何功能，即不会添加 where 关键字
b> 若 where 标签中的 if 条件满足，则 where 标签会自动添加 where 关键字，并将条件最前方多余的 and 去掉
注意：where 标签不能去掉条件最后多余的 and

3. trim

    /**
     * 根据条件查询员工信息trim
     * @param emp
     * @return
     */
    List<Emp> getEmpListByMoreTJ3(Emp emp);
1
2
3
4
5
6

    
    <select id="getEmpListByMoreTJ3" resultType="emp">
        select * from t_emp
        <trim prefix="where" suffixOverrides="and">
            <if test="empName != '' and empName != null">
                emp_name = #{empName} and
            if>
            <if test="age != '' and age != null">
                age = #{age} and
            if>
            <if test="sex != '' and sex != null">
                sex = #{sex} and
            if>
            <if test="email != '' and email != null">
                email = #{email} and
            if>
        trim>
    select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4. choose、when、otherwise

choose、when、otherwise相当于if…else if…else

    /**
     * choose、when、otherwise
     * @param emp
     * @return
     */
    List<Emp> getEmpListByMoreTJ4(Emp emp);
1
2
3
4
5
6

    
    <select id="getEmpListByMoreTJ4" resultType="emp">
        select * from t_emp
        <where>
            <choose>
                <when test="empName != '' and empName != null">
                    emp_name = #{empName}
                when>
                <when test="age != '' and age != null">
                    age = #{age}
                when>
                <when test="sex != '' and sex != null">
                    sex = #{sex}
                when>
                <when test="email != '' and email != null">
                    email = #{email}
                when>
                <otherwise>
                    did = 1
                otherwise>
            choose>
        where>
    select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

    @Test
    public void testGetEmpListByMoreTJ4(){

        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper dynamicSQLMapper = sqlSession.getMapper(DynamicSQLMapper.class);
        List<Emp> empListByMoreTJ = dynamicSQLMapper.getEmpListByMoreTJ4(new Emp(null, null, null, null, null));
        empListByMoreTJ.forEach(emp -> System.out.println(emp));

    }
1
2
3
4
5
6
7
8
9

5. foreach

    /**
     * 通过数组批量删除
     * @param eids
     * @return
     */
    int deleteMoreByArray(@Param("eids") Integer[] eids);
1
2
3
4
5
6

    
    <delete id="deleteMoreByArray">
        delete from t_emp where
        <foreach collection="eids" item="eid" separator="or">
            eid = #{eid}
        foreach>
    delete>
1
2
3
4
5
6
7

    @Test
    public void testDeleteMoreByArray(){

        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper dynamicSQLMapper = sqlSession.getMapper(DynamicSQLMapper.class);
        int i = dynamicSQLMapper.deleteMoreByArray(new Integer[]{6, 7, 8});
        System.out.println(i);

    }
1
2
3
4
5
6
7
8
9

collection：设置要循环的数组或集合
item：表示集合或数组中的每一个数据
separator：设置循环体之间的分隔符
open：设置 foreach 标签中的内容的开始符
close：设置 foreach 标签中的内容的结束符

6. SQL片段

sql 片段，可以记录一段公共 sql 片段，在使用的地方通过 include 标签进行引入

    <sql id="empColumns">eid, emp_name, age, sexsql>

    
    <select id="getEmpListByMoreTJ4" resultType="emp">
        select <include refid="empColumns">include> from t_emp
        <where>
            <choose>
                <when test="empName != '' and empName != null">
                    emp_name = #{empName}
                when>
                <when test="age != '' and age != null">
                    age = #{age}
                when>
                <when test="sex != '' and sex != null">
                    sex = #{sex}
                when>
                <when test="email != '' and email != null">
                    email = #{email}
                when>
                <otherwise>
                    did = 1
                otherwise>
            choose>
        where>
    select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25