Mybatis——动态SQL

写在前面的话

为什么有动态SQL,因为在我们平常编写的SQL简单，有时候业务逻辑复杂起来，就需要SQL是动态的。也是为了解决SQL拼接字符串的问题。

参考官方文档

Dynamic SQL

One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.

While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.

The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:

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

一、if标签

根据标签中的test属性所对应的表达式决定标签中的内容是否需要拼接到SQL中

<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="emp">
    select * from emp
    <where>
        <if test="empName != null and empName !=''">
            emp_name = #{empName}
        </if>
        <if test="age != null and age != ''">
            and age = #{age}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
        <if test="email != null and email != ''">
            and email = #{email}
        </if>
    </where>
</select>

二、where标签

当where有内容时 会自动生成 where关键字 并且将内容前多余的and或or去掉
当where标签中 没有内容时 where标签就没有任何效果
where标签不能将其中内容后面多余的and或or去掉

<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="emp">
    select * from emp
    <where>
        <if test="empName != null and empName !=''">
            emp_name = #{empName}
        </if>
        <if test="age != null and age != ''">
            and age = #{age}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
        <if test="email != null and email != ''">
            and email = #{email}
        </if>
    </where>
</select>

三、trim标签

prefix/suffix : 将trim标签中的内容前面或后面添加内容
suffixOverrides/prefixOverrides : 将trim标签中内容后或内容前 去掉指定内容(去掉条件不成立的内容)

 <select id="getEmpByCondition" resultType="emp">
        select <include refid="empColumns"></include> from emp
        <trim prefix="where" suffixOverrides="and|or">
            <if test="empName != null and empName !=''">
                emp_name = #{empName} and
            </if>
            <if test="age != null and age !=''">
                age = #{age} or
            </if>
            <if test="sex != null and sex !=''">
                sex = #{sex}
            </if>
        </trim>
    </select>

四、choose、when、otherwise

相当于if …else

List<Emp> getEmpByChoose(Emp emp);

select id="getEmpByChoose" resultType="emp">
    select * from emp
    <where>
        <choose>
            <when test="empName != null and empName != ''">
                emp_name = #{empName}
            </when>
            <when test="age != null and age != ''">
                age = #{age}
            </when>
            <when test="sex != null and sex != ''">
                sex = #{sex}
            </when>
            <when test="email != null and email != ''">
               email = #{email}
            </when>
            <otherwise>
                did = 1
            </otherwise>
        </choose>
    </where>
</select>

五、foreach

collection: 设置需要循环的数组或集合
item : 表示数组或集合的每一个数据
separator : 循环体之间的分隔符
open : foreach标签中 所循环所有内容的开始符
close : …结束符

 <!--int insertMoreByList(List<Emp> emps);-->
    <insert id="insertMoreByList">
        insert into emp values
        <foreach collection="emps" item="emp" separator=",">
            (null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
        </foreach>
    </insert>

六、SQL : 设置SQL片段

 <sql id="empColumns">
        emp_name,age,sex,email
</sql> 
 <select id="getEmpByCondition" resultType="emp">
        select <include refid="empColumns"></include> from emp
        <trim prefix="where" suffixOverrides="and|or">
            <if test="empName != null and empName !=''">
                emp_name = #{empName} and
            </if>
            <if test="age != null and age !=''">
                age = #{age} or
            </if>
            <if test="sex != null and sex !=''">
                sex = #{sex}
            </if>
        </trim>

七、Junit测试

import com.atguigu.mybatis.mapper.DynamicSQLMapper;
import com.atguigu.mybatis.pojo.Emp;
import com.atguigu.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Arrays;
import java.util.List;

public class DynamicSQLMapperTest {
    /*
        动态SQL
            1.if : 根据标签中的test属性所对应的表达式决定标签中的内容是否需要拼接到SQL中
            2.where : 当where有内容时 会自动生成 where关键字 并且将内容前多余的and或or去掉
                当where标签中 没有内容时 where标签就没有任何效果
                where标签不能将其中内容后面多余的and或or去掉
            3.trim :
                prefix/suffix : 将trim标签中的内容前面或后面添加内容
                suffixOverrides/prefixOverrides : 将trim标签中内容后或内容前 去掉指定内容(去掉条件不成立的内容)
            4.choose、when、otherwise 相当于 if...else ie...else
                when至少有一个 otherwise只能有一个
            5、foreach
                collection: 设置需要循环的数组或集合
                item : 表示数组或集合的每一个数据
                separator : 循环体之间的分隔符
                open : foreach标签中 所循环所有内容的开始符
                close : ....结束符
            6、SQL标签 : 设置SQL片段
            引用SQL片段 <include refin>
     */
    @Test
    public void testInsertMoreByList(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp1 = new Emp(null,"a1",18,"男","123@qq.com");
        Emp emp2 = new Emp(null,"a2",18,"男","123@qq.com");
        Emp emp3 = new Emp(null,"a3",18,"男","123@qq.com");
        List<Emp> emps = Arrays.asList(emp1,emp2,emp3);
        int result = mapper.insertMoreByList(emps);
        System.out.println(result);
    }
    @Test
    public void testDeleteEmpByForEach(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);

        int result = mapper.deleteMoreByArray(new Integer[]{5,6,7});
        System.out.println(result);
    }

    @Test
    public void testGetEmpByChoose(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        //所有条件都不成立 执行otherwise
        List<Emp> emp = mapper.getEmpByChoose(new Emp(
                null, "", null, "", null
        ));
        System.out.println(emp);
    }
    @Test
    public void testGetEmpByCondition(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);

        List<Emp> emp = mapper.getEmpByCondition(new Emp(null, "张三", null, null, "123@qq.com"));
        System.out.println(emp);
    }
}