之动态SQL

小程序开发定制开发人员在使用JDBC小程序开发定制或者其他类似的进行数小程序开发定制据库开发时，小程序开发定制通常都要根据需求去手动拼装SQL，小程序开发定制这是一个非常麻烦且痛苦的工作，而MyBatis提供的对SQL小程序开发定制语句动态组装的功能，小程序开发定制恰能很好的解决这一麻烦工作。

动态SQL是MyBatis小程序开发定制的强大特性之一，小程序开发定制其主要元素如下：

实体类

public class Emp {    private Integer empId;    private String empName;    private Integer age;    private String gender;      public Emp() {    }     public Emp(Integer empId, String empName, Integer age, String gender) {        this.empId = empId;        this.empName = empName;        this.age = age;        this.gender = gender;    }     public Integer getEmpId() {        return empId;    }     public void setEmpId(Integer empId) {        this.empId = empId;    }     public String getEmpName() {        return empName;    }     public void setEmpName(String empName) {        this.empName = empName;    }     public Integer getAge() {        return age;    }     public void setAge(Integer age) {        this.age = age;    }     public String getGender() {        return gender;    }     public void setGender(String gender) {        this.gender = gender;    }     @Override    public String toString() {        return "Emp{" +                "empId=" + empId +                ", empName='" + empName + '\'' +                ", age=" + age +                ", gender='" + gender + '\'' +                '}';    }}
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

1. < if > 元素

在MyBatis中，< if > 是最常用的判断语句，它类似于Java中的if语句，主要用于实现某些简单的条件选择

    <select id="getEmpByyCondition" resultType="com.atguigu.mybatis.pojo.Emp">        select * from t_emp where 1=1        <if test="empName!=null and empName!=''">            and emp_name=#{empName}        </if>        <if test="age!=null and age!=''">            and age=#{age}        </if>        <if test="gender!=null and gender!=''">            and gender=#{gender}        </if>    </select>
1
2
3
4
5
6
7
8
9
10
11
12

测试方法：

    public void test(){        SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();        SqlSession sqlSession = sqlSessionUtils.getSqlSession();        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);        Emp emp = new Emp(null,"张三",null,null);        List<Emp> list = mapper.getEmpByyCondition(emp);        System.out.println(list);    }
1
2
3
4
5
6
7
8

测试结果如下：

同时在为传递任何参数时，程序会将数据表中的所有数据查出

2. < where >

如上述使用if语句查询我们注意到select * from t_emp where 1=1在语句中要添加where 1=1这是为了保证至少有条件成立，不至于程序报错，但是MyBatis的开发者设计了更好的方法，就是把< where >也作为元素，动态添加where，使用如下

 <select id="getEmpByConditionTwo" resultType="Emp">        select * from t_emp        <where>            <if test="empName != null and empName != ''">                emp_name = #{empName}            </if>            <if test="age != null and age != ''">                and age = #{age}            </if>            <if test="gender != null and gender != ''">                and gender = #{gender}            </if>        </where>    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

这样就避免了那个设定

3. < choose >,< when >,< otherwise >元素

在使用< if >元素时，只要test属性中的表达式为true，就会执行元素中的条件语句，但是在实际应用中，有时只需要从多个选项中选择一个去执行。在这种场景下，使用< if > 元素进行处理是非常不合理的。如果使用的是Java语言，这种情况显然更适合switch语句来处理，那么MyBatis中有没有类似的语句呢？当然是有的。针对上面的情况，MyBatsi可以用< choose >,< when >,< otherwise >元素组合去实现上面的情况。

<select id="getEmpByChoose" resultType="Emp">        select * from t_emp        <where>            <choose>                <when test="empName != null and empName != ''">                    emp_name = #{empName}                </when>                <when test="age != null and age != ''">                    age = #{age}                </when>                <when test="gender != null and gender != ''">                    gender = #{gender}                </when>            </choose>        </where>    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

测试语句：

public void testGetEmpByChoose(){        SqlSession sqlSession = SqlSessionUtil.getSqlSession();        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);        Emp emp = new Emp(null, "张三", 21, "");        List<Emp> list = mapper.getEmpByChoose(emp);        list.forEach(System.out::println);    }
1
2
3
4
5
6
7

结果如下：

这是因为当条件满足其中一项，在这个案例中就是满足了名字，那么后面的就不会再去匹配啦，所以这里的查询是按照名字也就是张三来进行的。

4. < trim >元素

trim元素用于自定义拼接SQL语句，与where类似，具体我们可以对比来看

< where >版

 <select id="getEmpByConditionTwo" resultType="Emp">        select * from t_emp        <where>            <if test="empName != null and empName != ''">                emp_name = #{empName}            </if>            <if test="age != null and age != ''">                and age = #{age}            </if>            <if test="gender != null and gender != ''">                and gender = #{gender}            </if>        </where>    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

< trim >版

<select id="getEmpByCondition" resultType="Emp">        select <include refid="empColumns"></include> from t_emp        <trim prefix="where" suffixOverrides="and">            <if test="empName != null and empName != ''">                emp_name = #{empName} and            </if>            <if test="age != null and age != ''">                age = #{age} and            </if>            <if test="gender != null and gender != ''">                gender = #{gender}            </if>        </trim>    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

trim用于去掉或添加标签中的内容

常用属性：

• prefix：在trim标签中的内容的前面添加某些内容
• prefixOverrides：在trim标签中的内容的前面去掉某些内容
• suffix：在trim标签中的内容的后面添加某些内容
• suffixOverrides：在trim标签中的内容的后面去掉某些内容

5. < set >元素

在更新表中字段时，根据条件进行判断，从而实现部分更改，而不是更新所有字段，提高开发效率

  <update id="updateColumns" parameterType="Emp">        update t_emp         <set>            <if test="empName != null and empName != ''">                emp_name = #{empName} and            </if>            <if test="age != null and age != ''">                age = #{age} and            </if>            <if test="gender != null and gender != ''">                gender = #{gender}            </if>        </set>    </update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

其中的< if >元素用于判断相应的字段是否传入值，如果传入的字段非空，就将此字段进行动态SQL组转；并更新此字段，否则此字段不更新。

注意：在映射文件中使用 < set > 和 < if >元素组合纪念性update语句动态SQL组转时，如果< set >元素内包含的内容都为空，则会出现SQL语法错误。所以在使用< set > 元素进行字段信息更新时，要确保传入的更新字段都不能为空。

6. < foreach >元素

用于SQL语句执行批量操作

• collection: 需做foreach(遍历)的对象，作为入参时，list、array对象时，collection属性值分别默认用"list"、"array"代替，Map对象没有默认的属性值。但是，在作为入参时可以使用@Param(“keyName”)注解来设置自定义collection属性值，设置keyName后，list、array会失效；
• item： 集合元素迭代时的别名称，该参数为必选项；
• index： 在list、array中，index为元素的序号索引。但是在Map中，index为遍历元素的key值，该参数为可选项；
• open： 遍历集合时的开始符号，通常与close=")"搭配使用。使用场景IN(),values()时，该参数为可选项；
• separator： 元素之间的分隔符，类比在IN()的时候，separator=“,”,最终所有遍历的元素将会以设定的（,）逗号符号隔开，该参数为可选项；
• close： 遍历集合时的结束符号，通常与open="("搭配使用，该参数为可选项；

6.1 添加批量数据

    <insert id="insertEmps">        insert into t_emp value        <foreach collection="emps" item="emp" separator=",">          (null,#{emp.empName},#{emp.age},#{emp.gender},null)        </foreach>    </insert>
1
2
3
4
5
6

测试方法：

    public void test(){        SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();        SqlSession sqlSession = sqlSessionUtils.getSqlSession();        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);        Emp emp1 = new Emp(null,"数据1",21,"女");        Emp emp2 = new Emp(null,"数据2",24,"男");        Emp emp3 = new Emp(null,"数据3",31,"女");        List<Emp> emps = Arrays.asList(emp1, emp2, emp3);        mapper.insertEmps(emps);    }
1
2
3
4
5
6
7
8
9
10

执行结果：

6.2 批量删除数据

    <delete id="deleteByEmpIds">        delete from t_emp where emp_id in        <foreach collection="empIds" item="empId" separator="," open="(" close=")">        #{empId}    	</foreach>    </delete>
1
2
3
4
5
6

测试方法：

    public void test(){        SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();        SqlSession sqlSession = sqlSessionUtils.getSqlSession();        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);        Integer empIds[]={6,7};        mapper.deleteByEmpIds(empIds);     }
1
2
3
4
5
6
7
8

执行结果：

也可以这么拼接SQL语句

    <delete id="deleteByEmpIds">        delete from t_emp where        <foreach collection="empIds" item="empId" separator="or" >            emp_id=#{empId}        </foreach>    </delete>
1
2
3
4
5
6

7. < SQL >元素

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

    <sql id="empColumns"> emp_id,emp_name,age,gender</sql>    <select id="selectAll" resultType="com.atguigu.mybatis.pojo.Emp">        select <include refid="empColumns"></include> from t_emp    </select>
1
2
3
4

测试方法：

    public void test(){        SqlSessionUtils sqlSessionUtils = new SqlSessionUtils();        SqlSession sqlSession = sqlSessionUtils.getSqlSession();        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);        List<Emp> emps = mapper.selectAll();        System.out.println(emps);     }
1
2
3
4
5
6
7
8

执行结果：

8. 小结

动态SQL可以帮开发者灵巧的实现很多特殊条件的SQL语句，比如if元素是最常用的，同时要灵活使用sql语句来完成嵌套查询，要根据项目要求选取合适的元素来实现开发。