Mybatis(五)-动态SQL

Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了
解决 拼接SQL语句字符串时的痛点问题。
实例：多个查询条件进行查询，设置了条件的话，就一定要出现在sql中， 一定要注意默认值的设置和指定。null和" "(空字符串)

if

EmpMapper.java

    List<Emp> getEmpByCondition(Emp emp);

EmpMapper.xml

    <select id="getEmpByCondition" resultType="Emp">
        select *
        from t_emp
        where
        <!-- test表示当前标签中实体类的属性是否有效，如果有效，就拼接到sql中，无效就不拼接 -->
        <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>
    </select>

测试程序TestDynamicSql,java

    @Test
    public void testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = new Emp(1, "张三", 20, "男",1);
        List<Emp> list_emp = empMapper.getEmpByCondition(emp);
        list_emp.forEach(System.out::println);
        sqlSession.close();
    }

where

这里如果把where后面的条件empName置为空，那么再用测试类查询，就是错误的。

同理，把后面age和gender都置为空，那么就剩下一个select * from t_emp where的空壳

如何解决？

方法一：加入恒成立的条件

EmpMapper.xml

    <select id="getEmpByCondition" resultType="Emp">
        select *
        from t_emp
        where 1 = 1
        <!-- test表示当前标签中实体类的属性是否有效，如果有效，就拼接到sql中，无效就不拼接 -->
        <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>

继续查询上面那个都是空字段的情况，看结果不报错，查询正确

方法二:使用where标签

EmpMapper.xml

    <select id="getEmpByCondition" resultType="Emp">
        select *
        from t_emp
        <where>
            <!-- test表示当前标签中实体类的属性是否有效，如果有效，就拼接到sql中，无效就不拼接 -->
            <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>

继续测试空值：

验证了结论：
若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字。

对其中一个赋值

测试

验证了结论：
若where标签中的if条件满足，则where标签会自动添加where关键字，并将条件最前方多余的and去掉。

注意：where标签不能去掉条件最后多余的and

即如果写成

        <where>
            <!-- test表示当前标签中实体类的属性是否有效，如果有效，就拼接到sql中，无效就不拼接 -->
            <if test="empName != null and empName != '' ">
                emp_name = #{empName}
            </if>
            <if test="age != null and age != ''">
                age = #{age} and
            </if>
            <if test="gender != null and gender != ''">
                and gender = #{gender}
            </if>
        </where>

这时测试将gender赋空值，会发现age后面的and还在

trim

trim用于去掉或添加标签中的内容
常用属性：
prefix：在trim标签中的内容的前面添加某些内容
prefixOverrides：在trim标签中的内容的前面去掉某些内容
suffix：在trim标签中的内容的后面添加某些内容
suffixOverrides：在trim标签中的内容的后面去掉某些内容

EmpMapper.xml

    <select id="getEmpByCondition" resultType="Emp">
        select *
        from t_emp
        <!-- trim的标签有4个
             prefix prefixOverrides suffix suffixOverrides
             prefix在内容前面加上指定内容
             prefixOverrides在内容前面去掉指定内容
             suffix在内容后面加上指定内容
             suffixOverrides在内容后面去掉指定内容
         -->
        <trim prefix="where" suffixOverrides="and">
            <!-- test表示当前标签中实体类的属性是否有效，如果有效，就拼接到sql中，无效就不拼接 -->
            <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} and
            </if>
        </trim>
    </select>

先测试都不为空

gender后面的and被自动去掉

再测试只有age有值的

再测试全为空，发现因为没有内容，所以where没有被添加

choose、when、otherwise

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

EmpMapper.java

    List<Emp> getEmpByChoose(Emp emp);

EmpMapper.xml

   <select id="getEmpByChoose" resultType="Emp">
        select *
        from t_emp
        <where>
            <choose>
                <!-- test表示当前标签中实体类的属性是否有效，如果有效，就拼接到sql中，无效就不拼接 -->
                <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>
                <otherwise>
                    1 = 1
                </otherwise>
            </choose>
        </where>
    </select>

测试程序

    @Test
    public void testGetEmpByChoose(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = new Emp(1, "张三", 20, "男",1);
        List<Emp> list_emp = empMapper.getEmpByChoose(emp);
        list_emp.forEach(System.out::println);
        sqlSession.close();
    }

发现choose、when、 otherwise会选择第一个符合条件的进行，只要一个符合条件的，后面都不再判断，也不再纳入条件，所以每个when不用添加and，只执行第一个成立的条件

foreach

比较重要的标签，用于批量添加和批量删除。

批量添加

EmpMapper.java

    int insertMoreEmp(@Param("emps") List<Emp> emp);

EmpMapper.xml

    <insert id="insertMoreEmp" useGeneratedKeys="true" keyProperty="empId">
        insert into t_emp
        values
        <!--foreach标签
             collection属性：放入Mapper接口Param注解中定义的变量即可
             item 可以理解为一行数据，即一个对象
             separator 表示分隔符，每一次循环后以，分割，最后一次没有
        -->
        <foreach collection="emps" item="emp" separator=",">
            <!-- 这里用emp.empName是因为返回值类型是List，而emp才是每一行数据的实体对象 -->
            (null, #{emp.empName}, #{emp.age}, #{emp.gender}, #{emp.deptId})
        </foreach>
    </insert>

测试程序

    @Test
    public void testInsertMoreEmp(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> list_emp = new ArrayList<>();
        list_emp.add(new Emp(null,"周七",23,"男",3));
        list_emp.add(new Emp(null,"朱八",25,"女",2));
        list_emp.add(new Emp(null,"李九",22,"男",1));
        int i = empMapper.insertMoreEmp(list_emp);
        System.out.println("插入：" + i + "行");
        sqlSession.close();
    }

批量删除

删除方法1

EmpMapper.java

int deleteMoreEmp(@Param("empIds") Integer[] empIds);

EmpMapper.xml

    <delete id="deleteMoreEmp">
        delete  from t_emp where emp_id in (
        <foreach collection="empIds" item="empId" separator=",">
            #{empId}
        </foreach>)
    </delete>

测试

    @Test
    public void testDeleteMoreEmp(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        Integer[] empIdArr = {8,9,10};
        int i = empMapper.deleteMoreEmp(empIdArr);
        System.out.println("删除：" + i + "行");
        sqlSession.close();
    }

删除方法2

EmpMapper.xml

    <!--方法二-->
    <delete id="deleteMoreEmp">
        delete
        from t_emp
        where emp_id in
        <!-- open以什么开始 close以什么结束 -->
        <foreach collection="empIds" item="empId" separator="," open="(" close=")">
            #{empId}
        </foreach>
    </delete>

测试：自动加了前后括号

删除方法3

EmpMapper.xml

    <delete id="deleteMoreEmp">
        delete
        from t_emp
        where
        <foreach collection="empIds" item="empId" separator="or">
            emp_id = #{empId}
        </foreach>
    </delete>

测试：用or来拼接条件

总结

foreach标签的属性
collection：设置要循环的数组或者集合
item：用一个字符串表示数组或者集合中的每一个数据
separator：设置每次循环的数据之间的分隔符
open：循环的所有内容以什么开始
close：循环的所有内容以什么结束

SQL片段

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

EmpMapper.java

List<Emp> getAllEmp();

EmpMapper.xml

    <sql id="empColumns">
        emp_id,emp_name,age,gender,dept_id
    </sql>
    <select id="getAllEmp" resultType="Emp">
        select
        <!-- 使用的时候用 include  refid属性放sql中的id -->
        <include refid="empColumns"></include>
        from t_emp
    </select>

测试

    @Test
    public void testGetAllEmp(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> list_emp = empMapper.getAllEmp();
        list_emp.forEach(System.out::println);
        sqlSession.close();
    }