【8】Mybatis动态sql【if、where、set、choose(when,otherwise)】

13、动态SQL

什么是动态SQL

什么是动态SQL：动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句.

官网描述：
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

  -------------------------------
  - if
  - choose (when, otherwise)
  - trim (where, set)
  - foreach
  -------------------------------

我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误。

那么怎么去解决这个问题呢？这就要使用 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。

所谓的动态SQL，本质还是SQL语句，只是我们可以在SQL层面，去执行一个逻辑代码

搭建环境

直接用之前的employee实体类就可以了。

1. 导入lombok依赖

2. 新建pojo实体类employee

3. 创建EmployeeMapper接口，并且写几个抽象方法

4. 在资源包创建EmployeeMapper.xml并编写

5. 在核心配置文件mybatis-config.xml文件绑定EmployeeMapper.xml资源

6. 编写测试类

if

    <select id="getEmployeeDynamicSQL" parameterType="map" resultType="Employee">
        select * from employee where 1 = 1
        <if test="age != null">
            and age = #{age}
        </if>
        <if test="dept_id != null">
            and dept_id = #{dept_id}
        </if>
    </select>

    @Test
    public void getEmployeeDynamicSQL(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("age",20);
//        map.put("dept_id",1);
        List<Employee> employees = mapper.getEmployeeDynamicSQL(map);
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }

这种方式比较死板，sql语句中where必须带上且激活，如果if条件都不满足，则进行全表查询，若两个都满足则为条件并集查询

choose (when, otherwise)

    <select id="getEmployeeDynamicSQL3" parameterType="map" resultType="Employee">
        select * from employee
        <where>
            <choose>
                <when test="age != null">
                    age = #{age}
                </when>
                <when test="dept_id != null">
                    dept_id = #{dept_id}
                </when>
                <otherwise>
                    id = #{id}
                </otherwise>
            </choose>
        </where>

    </select>

    @Test
    public void getEmployeeDynamicSQL3(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
//        map.put("age",20);
//        map.put("dept_id",1);
        map.put("id",7);
        List<Employee> employees = mapper.getEmployeeDynamicSQL3(map);
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }

这种按条件选择其中一个执行，要跟前面if进行比较记忆。

trim (where, set)

where配合if使用

<select id="getEmployeeDynamicSQL2" parameterType="map" resultType="Employee">
    select * from employee
    <where>
        <if test="age != null">
            and age = #{age}
        </if>
        <if test="dept_id != null">
            and dept_id = #{dept_id}
        </if>
    </where>
</select>

    @Test
    public void getEmployeeDynamicSQL2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("age",20);
        map.put("dept_id",1);
        List<Employee> employees = mapper.getEmployeeDynamicSQL2(map);
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }

set修改执行修改sql

<update id="updateEmployeeDynamicSQL" parameterType="map">
    update employee
    <set>
        <if test="name != null">name = #{name},</if>
        <if test="age != null">age = #{age},</if>
        <if test="dept_id != null">dept_id = #{depid}</if>
    </set>
    where id = #{id}
</update>

    @Test
    public void updateEmployeeDynamicSQL(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
//        map.put("age",20);
//        map.put("dept_id",1);
        map.put("id",7);
        map.put("name","王炸炸");
        map.put("age",18);
        mapper.updateEmployeeDynamicSQL(map);

    }

SQL片段

有时候，我们可能会将一些功能的部分抽取出来，方便复用！

1. 使用sql标签抽取公共部分

       <sql id="age-dept_id">
           <if test="age != null">
               and age = #{age}
           </if>
           <if test="dept_id != null">
               and dept_id = #{dept_id}
           </if>
       </sql>
   

2. 在需要使用的地方使用include标签引用

       <select id="getEmployeeDynamicSQL2" parameterType="map" resultType="Employee">
           select * from employee
           <where>
               <include refid="age-dept_id"/>
           </where>
       </select>
   

foreach