MyBatis之动态SQL

动态SQL是MyBatis的强大特性之一，基于OGNJ表达式来完成动态SQL。其主要元素如下：

元素	说明
if	判断语句，单条件分支判断
choose，when，otherwise	相当于java中的switch…case..default语句，用于多条件分支判断
where，trim，set	辅助元素，用于处理一些SQL拼接、特殊字符问题
foreach	循环语句，常用于in语句等列举条件中
bind	从OGNL表达式中创建一个变量，并将其绑定到上下文，常用于模糊查询的sql中

if元素

在映射文件中的配置

    <select id="findCustomerByNameAndJobs"
    parameterType="com.itheima.po.Customer"
    resultType="com.itheima.po.Customer">
        SELECT * FROM t_customer WHERE 1=1
        <if test="username != null and username != ''">
            AND username LIKE concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs != ''">
            AND jobs = #{jobs}
        </if>
    </select>

测试方法

    @Test
    public void findCustomerByNameAndJobsTest() {
        SqlSession session = MybatisUtils.getSession();
        Customer customer = new Customer();
        customer.setUsername("jack");
        customer.setJobs("teacher");
        List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobs", customer);
        for(Customer customer2 : customers) {
            System.out.println(customer2);
        }
        session.close();
    }

choose,when,otherwise元素

映射文件中的配置

    <select id="findCustomerByNameOrJobs"
    parameterType="com.itheima.po.Customer"
    resultType="com.itheima.po.Customer">
        SELECT * FROM t_customer WHERE 1=1
        <choose>
            <when test="username != null and jobs != ''">
                and username LIKE concat('%',#{username},'%')
            </when>
            <when test="jobs != null and jobs != ''">
                AND jobs=#{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
    </select>

测试方法

 @Test
    public void findCustomerByNameOrJobsTest() {
        SqlSession session = MybatisUtils.getSession();
        Customer customer = new Customer();
        customer.setUsername("jack");
        customer.setJobs("teacher");
        List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameOrJobs", customer);
        for (Customer customer2 : customers) {
            System.out.println(customer2);
        }
        session.close();
    }

where、trim元素

where元素主要是用来去除where后面的1=1。在where元素内的条件成立时候，，才会在拼接SQL中加如where关键字，也会自动去除多余的“AND”和“OR”。
以下功能相同：

    <select id="findCustomerByNameAndJobs"
    parameterType="com.itheima.po.Customer"
    resultType="com.itheima.po.Customer">
        SELECT * FROM t_customer WHERE 1=1
        <if test="username != null and username != ''">
            AND username LIKE concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs != ''">
            AND jobs = #{jobs}
        </if>
    </select>

<select id="findCustomerByNameAndJobs"
    parameterType="com.itheima.po.Customer"
    resultType="com.itheima.po.Customer">
        SELECT * FROM t_customer
        <where>
            <if test="username != null and username != ''">
                AND username LIKE concat('%',#{username},'%')
            </if>
            <if test="jobs != null and jobs != ''">
                AND jobs = #{jobs}
            </if>
        </where>
    </select>

用trim也可以达到相同的效果，代码如下：

    <select id="findCustomerByNameAndJobs"
    parameterType="com.itheima.po.Customer"
    resultType="com.itheima.po.Customer">
        SELECT * FROM t_customer
        <trim prefix="where" prefixOverrides="and">
            <if test="username != null and username != ''">
                AND username LIKE concat('%',#{username},'%')
            </if>
            <if test="jobs != null and jobs != ''">
                AND jobs = #{jobs}
            </if>
        </trim>
    </select>

set元素

set元素用于更新操作，主要作用是在动态包含的SQL语句前输出一个SET关键字，并将SQL语句中的最后一个多余的逗号去除。

    <update id="updateCustomer" parameterType="com.itheima.po.Customer">
        UPDATE t_customer
        <set>
            <if test="username!=null and username!=''">
                username = #{username},
            </if>
            <if test="jobs!=null and jobs!=''">
                jobs=#{jobs},
            </if>
            <if test="phone!=null and jobs!=''">
                phone=#{phone},
            </if>
        </set>
        WHERE id=#{id}
    </update>

foreach元素

主要作用是用来循环遍历数组和集合。

    <select id="findCustomerByIds" parameterType="List" resultType="com.itheima.po.Customer">
        SELECT * FROM t_customer WHERE id IN
        <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

其中index是配置循环中当前的元素，index是配置当前元素在集合的位置下标，collection是配置list是传递过来的参数类型（首字母小写），它可以是一个array，list或collection，Map集合的键，POJO包装类中数组或集合类型的属性名。open和close是配置以什么符号将这些元素包装起来，separator是配置各个元素的间隔符。
测试用例如下：

    @Test
    public void findCustomerByIdsTest() {
        SqlSession session = MybatisUtils.getSession();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByIds", ids);
        for (Customer customer : customers) {
            System.out.println(customer);
        }
        session.close();
    }

bind元素

在进行模糊查询时，如果使用“￥{ }”进行字符连接，则无法防止SQL注入问题，如果使用concat函数进行拼接，则只针对数据库。而bind元素可以解决这一问题，只要使用MyBaits的语言即可与所需参数连接。

    <select id="findCustomerByName" parameterType="com.itheima.po.Customer" resultType="com.itheima.po.Customer">
        <bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'"/>
        SELECT * FROM t_customer WHERE username LIKE #{pattern_username}
    </select>

    @Test
    public void findCustomerByNameTest() {
        SqlSession session = MybatisUtils.getSession();
        Customer customer = new Customer();
        customer.setUsername("j");
        List<Customer> customers = session.selectList("com.itheima.mapper.CustomerMapper.findCustomerByName", customer);
        for(Customer customer2 : customers) {
            System.out.println(customer2);
        }
        session.close();
    }