动态Sql

我们都可以从一些网站上看到这样的进行查询的方式：

在进行查询的时候，可以对每一个字段进行单选或者多选，作为程序员的我们可以知道，每一次进行查询的时候都需要一个SQL语句对应，但是对于我们上面的这种大规模的查询，条件众多，如果对每一个查询都对应一个SQL语句的话，代码量是很庞大的。而且如果上面的条件进行变更，项目的维护难度是十分大的。

所以Mybatis有一种动态SQL技术，可以把SQL语句通过条件选择，一条语句能够对应很多种的查询，极大地提高了效率。

---

动态Sql

动态SQL有以上四种标签供我们使用。

If

添加mapper接口中方法：

List<Emp> query(Emp emp);

mapper配置文件SQL语句：

使用方法看注释：

<select id="query" parameterType="Emp" resultMap="useResultMap" >
            <!-- 下面这里是介绍if的使用方式
                if的作用就是判断这个字段是否为空，来进行多条件动态查询，如果有这个条件就加上，如果这个条件为空就不带
                这里设计的方法就是 利用 1 = 1 来保证这次查询不用考虑最后那个where的问题，如果后面一个条件都不满足
                那么多出一个where,查询语句就会报错，然后后面让每一个条件成为一个单独模块
                就可以进行多个条件用一个语句就可以查询出来。
                这里不仅可以用and,对or同样适用-->
            select e_id,e_name,e_age,e_birthday,e_salary from emp where 1 = 1
            <if test="name != null">
                and e_name = #{name}
            </if>
            <if test="age != null">
                and e_age = #{age}
            </if>
        </select>

service实现类

    @Override
    public List<Emp> query(Emp emp) {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> list = mapper.query(emp);
        return list;
    }

 测试1：

    @Test
    public void testQuery(){
        EmpService service = new EmpServiceImpl();

        Emp emp = new Emp();
        emp.setName("佐助");
        emp.setAge(13);
        List<Emp> list = service.query(emp);
        for(Emp e : list){
            System.out.println(e);
        }
    }

测试2：

@Test
    public void testQuery(){
        EmpService service = new EmpServiceImpl();

        Emp emp = new Emp();
        emp.setName("佐助");
        //emp.setAge(13);
        List<Emp> list = service.query(emp);
        for(Emp e : list){
            System.out.println(e);
        }
    }

测试3

@Test
    public void testQuery(){
        EmpService service = new EmpServiceImpl();

        Emp emp = new Emp();
        //emp.setName("佐助");
        //emp.setAge(13);
        List<Emp> list = service.query(emp);
        for(Emp e : list){
            System.out.println(e);
        }
    }

---

choose, when, otherwise

我们一定碰到过这样的需求，比如像文章顶部的房子信息，如果什么信息都不传入，那么就会查询所有，那么数据库中所有的数据都会查询出来，这样会造成很大的数据传输，对服务器有着很大的压力，不如如果什么信息都不传入的话，那么就展示一个特定的条件下的内容。

这个标签类似于JSP中的C标签，原理类似于连续的if else 语句。进行连续判断。

只需修改上面例子中的查询语句：

<select id="query" parameterType="Emp" resultMap="useResultMap" >
            <!-- where后面的 1=1 还是除去没有条件的时候消除where的影响
             这里没有条件的时候会默认使用后面的条件，也消除了后面and的影响-->
            select e_id,e_name,e_age,e_birthday,e_salary from emp where 1 = 1
            <!-- 这里三个标签配置使用，choose在外层，包裹住所有条件
                里面的when标签可以设置多个.
                就像是一个接一个if{}
                            else if{}
                            else if{}
                            else{}
              第一个when标签是我们的第一个if，后面的每一个when都相当于是else if
              最后的otherwise就相当于我们最后一个else{}
              -->
            <choose>

                <when test=" age != null and name !=null">
                    and e_age = #{age} and e_name = #{name}
                </when>
                <when test=" name != null">
                    and e_name = #{name}
                </when>
                <when test=" age != null">
                    and e_age = #{age}
                </when>
                <otherwise>
                    and e_age = 13 and e_name = '小樱'
                </otherwise>
            </choose>
        </select>

测试1：

@Test
    public void testQuery(){
        EmpService service = new EmpServiceImpl();
        Emp emp = new Emp();
        emp.setName("佐助");
        emp.setAge(13);
        List<Emp> list = service.query(emp);
        for(Emp e : list){
            System.out.println(e);
        }
    }

把年龄输入注释掉，进行测试：

两个都注释掉：

 

---

trim, where, set

这三个标签作用类似，where在SQL语句需要使用where时使用，set标签是针对update操作时使用，trim是一个公共的方法，既可以实现where的功能，也可以实现set的功能。

我们首先从where进行介绍：

我们在前面使用where进行查询的时候，为了避免and和where关键字的影响，一直使用一个1=1来规避影响，但是这种做法是不太好的。

我们的数据库进行存储的时候，每一列的相同的值会进行整合，只存储一个相同的值，然后这个相同的值后面后存储所有等于这个值的数据的地址，进行查询的时候，会根据查询的条件，针对第一个条件做一个二分查找，然后找到后面跟的所有满足这个条件的数据的地址，然后再在所有满足第一个条件的数据里进行第二个数据的查找，如果有第三个条件以此类推，如果到最后没有条件了，会把后面跟的所有数据进行返回，这样会提高查询的效率，但是我们前面有一个1=1，就会从数据库里进行一个一个的查找，降低了我们查找的效率，所以需要把这个给规避掉。这里就需要使用where标签。

修改上面的查询的SQL语句：

<select id="query" parameterType="Emp" resultMap="useResultMap" >
            select e_id,e_name,e_age,e_birthday,e_salary from emp
            <where>
                <if test="name != null">
                    and e_name = #{name}
                </if>
                <if test="age != null">
                    and e_age = #{age}
                </if>
            </where>
        </select>

where的作用是：

1.当where后面跟的条件都不为空的时候，会在语句拼接上where关键字

2.加入where后面跟的条件不为空的时候，后面第一个不为空的语句是以and或者or开头的，会把这个and或者or省略掉。

set

Sql语句：

<update id="updateEmp">
            update emp
            <set>
                <if test="name != null">
                    e_name = #{name},
                </if>
                <if test="age != null">
                    e_age = #{age},
                </if>
                <if test="birthday != null">
                    e_birthday = #{birthday},
                </if>
                <if test="salary != null">
                    e_salary = #{salary},
                </if>
            </set>
             where e_id = #{id}
        </update>

测试1：

    @Test
    public void testUpdateEmp(){
        Emp emp = new Emp(15,"佐助",13,new Date(),new BigDecimal(1000));
        EmpService service = new EmpServiceImpl();
        service.updateEmp(emp);
    }

测试2：

    @Test
    public void testUpdateEmp(){
        Emp emp = new Emp(17,"佐助",13,null,new BigDecimal(1000));
        EmpService service = new EmpServiceImpl();
        service.updateEmp(emp);
    }

set的作用是：

1.加入set标签中的语句不为空的时候，那么会拼接set关键字

2.如果里面的标签最后一个修改条件是逗号结尾的话，那么这个会被删除

trim

在trim标签有四个属性，作用分别是：

prefix：前缀，在返回的字符串之前添加什么内容

suffix：后缀，在返回的字符串之后添加什么内容

prefixOverrides：当字符串以其中什么内容开头的时候，该内容会被覆盖

suffixOberirdes：当字符串以其中什么内容结尾的时候，该内容会被覆盖

利用这四个特性，就可以完成where和set标签的功能：

替换where标签：

<select id="query" parameterType="Emp" resultMap="useResultMap" >
            select e_id,e_name,e_age,e_birthday,e_salary from emp
            <trim prefix="where" prefixOverrides="and||or">
                <if test="name != null">
                    and e_name = #{name}
                </if>
                <if test="age != null">
                    and e_age = #{age}
                </if>
            </trim>
        </select>

替换set标签：

<update id="updateEmp">
            update emp
            <trim prefix="set" suffixOverrides=",">
                <if test="name != null">
                    e_name = #{name},
                </if>
                <if test="age != null">
                    e_age = #{age},
                </if>
                <if test="birthday != null">
                    e_birthday = #{birthday},
                </if>
                <if test="salary != null">
                    e_salary = #{salary},
                </if>
            </trim>
             where e_id = #{id}
        </update>

---

foreach

我们之前的查询或者其他使用where关键字进行条件判断的时候，都是使用的单个条件，如果我们想要查询id值为1,3,4,5,19这些数据的时候，我们还是需要在SQL语句中使用in关键字，但是使用in关键字的时候，后面跟的一个括号，里面是各个条件值，我们该如果实现这个动态的查询呢，而动态SQL里面的foreach标签，就可以完成这个功能。

foreach的功能大部分都是配合in关键字来实现的，而in可以跟在任何使用where的环境下，为了更好的演示，这里使用一个批量删除的功能。

在数据中括号内的信息都是要单独写出来的，那么我们传入的一系列数据该怎么去传入呢，程序员一般都会用一个数据或者一个list来存储这些数据。

在mapper接口和service接口中写一个方法：

void deleteBatch(Integer[] ids);

编写SQL语句：

当接口中方法的参数是数组或者集合的时候，Mybatis会自动把参数封装到一个map集合中，这个元素的key值为参数类型，首字母小写

比如数组为array,集合为list,map的为map。

foreach一共有五个标签。功能分别为：

collection：取出封装到map中的key值，就是我们上面介绍的参数类型名

open：表示拼接的字符串是以什么开始

close：表示拼接的字符串是以什么结束

item:给集合中的每一个元素起的别名，和下面#{}中的参数名对应，比如这里是用id值，所以就起名为id,见名知义

separator；集合中元素的分隔符，比如这里是用逗号分隔，所以就用逗号

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

service实现类实现方法：

    @Override
    public void deleteBatch(Integer[] ids) {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        mapper.deleteBatch(ids);
        sqlSession.commit();
        sqlSession.close();
    }

测试：

    @Test
    public void testdeleteBatch(){
        Integer[] ids = {15,17,18,19};
        EmpService service = new EmpServiceImpl();
        service.deleteBatch(ids);
    }