【Mybatis】（五）动态SQL

在【Mybatis】（一）第一个mybatis实例中已经创建了数据库和基本的运行环境，接下来将介绍Mybatis动态SQL。

1、定义EmployeeMapperDynamicSQL接口

     package com.lhk.mybatis.dao;
        import com.lhk.mybatis.bean.Employee;
        import org.apache.ibatis.annotations.Param;
        import java.util.List;
        
        /**
         * @author lhk
         * @create 2018-09-16 20:21
         */
        public interface EmployeeMapperDynamicSQL {
        
            // 查询员工，要求携带了哪个字段查询字段就带上这个字段的值，测试where
            public List<Employee> getEmpByConditionIf(Employee employee);
        
            // 查询员工，测试Trim
            public List<Employee> getEmpByConditionTrim(Employee employee);
        
            // 查询员工，测试Choose，When，otherwise
            public List<Employee> getEmpByConditionChoose(Employee employee);
        
            // 更新员工信息
            public void updateEmp(Employee employee);
        
            // 插入员工信息
            public void insertEmp(Employee employee);
        
            // 通过foreach在指定集合中查询员工id
            public List<Employee> getEmpByConditionForeach(@Param("ids") List<Integer> id);
        
            // 通过foreach批量插入员工信息
            public void addEmp(@Param("emps") List<Employee> employee);
        }

2、定义EmployeeMapperDynamicSQL.xml配置文件

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.lhk.mybatis.dao.EmployeeMapperDynamicSQL">
        <!--
            • if 判断
            • choose (when, otherwise) 分支选择（带了break的switch-case）
                如果带了id就用id查，如果带了lastName就用lastName查；只会进入其中一个
            • trim (where(封装查询条件), set(封装修改条件)) 字符串截取
            • foreach
        -->
    
        <resultMap id="Simple" type="com.lhk.mybatis.bean.Employee">
            <result column="last_name" property="lastName"></result>
        </resultMap>
    
    
        <!--查询员工，要求携带了哪个字段查询字段就带上这个字段的值
            getEmpByConditionIf
        -->
        <select id="getEmpByConditionIf" resultMap="Simple">
            select * from tb1_employee
    --         where
            <where>
                <!--
                测试where：
                1、当if条件不满足，即where元素中没有内容，在SQL语句中就不会出现where
                2、当if条件满足，where元素的内容是以and开头，where会自动去掉开头的and or，以保证where条件正确
                3、where不能解决SQL语句后面多出的and or，要通过trim解决
                test:判断表达式，从参数中取值进行判断-->
                <if test="id!=null">
                    id=#{id}
                </if>
                <if test="lastName!=null and lastName!=''">
                    and last_name like #{lastName}
                </if>
                <if test="email!=null and email.trim()!=''">
                    and email=#{email}
                </if>
                <if test="gender==0 and gender==1">
                    and gender=#{gender}
                </if>
            </where>
        </select>
    
    
        <!--通过Trim方式 getEmpByConditionTrim -->
        <select id="getEmpByConditionTrim" resultMap="Simple">
            select * from tb1_employee
            --         where
            <!--
                where，set标签的功能都可以通过Trim来实现
                解决SQL语句后面多出的and or，where标签不能解决
    
            <trim prefix="" 前缀，当trim元素内包含内容时，会给内容增加prefix指定的前缀
                  prefixOverrides="" 前缀覆盖，当trim元素内包含内容时，会把内容中匹配的前缀字符串去掉
                  suffix="" 后缀，当trim元素内包含内容时，会给内容增加suffix指定的后缀
                  suffixOverrides="" 后缀覆盖，当trim元素内包含内容时，会把内容中匹配的后缀字符串去掉
            </trim>
            -->
    
    
            <!--自定义字符串截取规则-->
            <!--在SQL语句前面添加where，去除SQL语句中后面多余的and -->
            <trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and">
                <!-- test:判断表达式，从参数中取值进行判断-->
                <if test="id!=null">
                    id=#{id} and
                </if>
                <if test="lastName!=null and lastName!=''">
                    last_name like #{lastName} and
                </if>
                <if test="email!=null and email.trim()!=''">
                    email=#{email} and
                </if>
                <if test="gender==0 and gender==1">
                    gender=#{gender}
                </if>
            </trim>
        </select>
    
    
        <!--测试分支选择 getEmpByConditionChoose -->
        <select id="getEmpByConditionChoose" resultMap="Simple">
            select * from tb1_employee
            <where>
                <!-- 如果带了id就用id查，如果带了lastName就用lastName查；只会进入其中一个 -->
                <choose>
                    <when test="id!=null">
                        id=#{id}
                    </when>
                    <when test="lastName!=null">
                        last_name like #{lastName}
                    </when>
                    <when test="email!=null">
                        email=#{email}
                    </when>
                    <otherwise>
                        gender=0
                    </otherwise>
                </choose>
            </where>
        </select>
    
        <!-- 测试update  updateEmp
            只更新要更新的值。为null的值就不更新
            set：1、如果该标签包含的元素中有返回值，就插入一个set
                 2、如果set后面的字符串是以逗号结尾，就删除这个逗号
                 3、如果set元素中没有内容，则仍然会出现SQL错误，所以id=#{id}仍有保留的必要
         -->
        <update id="updateEmp">
            update tb1_employee
            <set>
                <if test="lastName!=null">
                    last_name=#{lastName},
                </if>
                <if test="email!=null">
                    email=#{email},
                </if>
                <if test="gender!=null">
                  gender=#{gender}
                </if>
                id=#{id},
            </set>
            where id=#{id}
        </update>
    
        <!--测试insert insertEmp
            只插入要插入的值。为null的值就不插入。
            在列的部分增加if条件，则values的部分也要增加相同的if条件，
            必须保证上下相互对应，完全匹配
        -->
        <insert id="insertEmp">
            insert into tb1_employee(
            <if test="id!=null and id!=''">
                id,
            </if>
            <if test="lastName!=null and lastName!=''">
                last_name,
            </if>
            <if test="email!=null and email!=''">
                email,
            </if>
            <if test="gender!=null and gender!=''">
                gender
            </if>
            )
            <trim suffixOverrides=",">
                values(
                <if test="id!=null and id!=''">
                    #{id},
                </if>
                <if test="lastName!=null and lastName!=''">
                    #{lastName},
                </if>
                <if test="email!=null and email!=''">
                    #{email},
                </if>
                <if test="gender!=null and gender!=''">
                    #{gender}
                </if>
                )
            </trim>
        </insert>
    
    
        <!--测试foreach getEmpByConditionForeach-->
        <!--
            collection：指定要遍历的集合，
                   list类型的参数会特殊处理封装在map中，map的key就叫list
            item：将当前遍历出的元素赋值给指定的变量
            separate：每个元素之间的分隔符
            open：遍历出所有的结果拼接一个开始字符
            close：遍历出所有的结果拼接一个结束字符
            index：遍历list的时候，index是list的索引，item是list的值
                   遍历map的时候，index是map的key，item是map的值
        -->
        <select id="getEmpByConditionForeach" resultMap="Simple">
            select * from tb1_employee where id in
    
            <foreach collection="ids" item="item_id" separator="," open="(" close=")">
                #{item_id}
            </foreach>
        </select>
    
        <!-- 批量保存：addEmp-->
        <insert id="addEmp">
            insert into tb1_employee(last_name,email,gender,d_id)
            values
            <foreach collection="emps" item="emp" separator=",">
                (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
            </foreach>
        </insert>
    </mapper>

3、定义MybatisDynamicSQLTest测试类

package com.lhk.mybatis.test;

import com.lhk.mybatis.bean.Department;
import com.lhk.mybatis.bean.Employee;
import com.lhk.mybatis.dao.EmployeeMapperDynamicSQL;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author lhk
 * @create 2018-09-16 20:46
 */
public class MybatisDynamicSQLTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "conf/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    /**
     * 测试If
     */
    @Test
    public void testgetEmpByConditionIf() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(2,"%e%","Jerry@qq.com","1");
            List<Employee> list = mapper.getEmpByConditionIf(employee);
            for (Employee emp : list) {
                System.out.println(emp);
            }
            //查询的时候如果某些条件没有，SQL拼装可能会出问题
            //1、给where后面加上1=1，以后的条件都and xxx
            //2、mybatis使用where标签将所有的查询条件包括在内
            //mybatis就会将where标签中的拼装的SQL中多的and,or去掉。只会去掉第一个多出来的and，or
        }finally {
            sqlSession.close();
        }
    }

    /**
     * 测试Trim
     */
    @Test
    public void testGetEmpByConditionTrim() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null, "%e%", "Jerry@qq.com", "1");

            List<Employee> list = mapper.getEmpByConditionTrim(employee);
            for (Employee emp : list) {
                System.out.println(emp);
            }
        }finally {
            sqlSession.close();
        }
    }

    /**
     * 测试Choose
     */
    @Test
    public void testGetEmpByConditionChoose() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null, null, "Jerry@qq.com", "1");

            List<Employee> list = mapper.getEmpByConditionChoose(employee);
            for (Employee emp : list) {
                System.out.println(emp);
            }
        }finally {
            sqlSession.close();
        }
    }

    /**
     * 测试set
     */
    @Test
    public void testUpdateEmp() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null, null, null, null);
            mapper.updateEmp(employee);
            sqlSession.commit(); // 提交
        }finally {
            sqlSession.close();
        }
    }

    /**
     * 测试insert
     */
    @Test
    public void testInsertEmp() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null, "lll", null, "0");
            mapper.insertEmp(employee);
            sqlSession.commit(); // 提交
        }finally {
            sqlSession.close();
        }
    }

    /**
     * 测试foreach实现in集合
     */
    @Test
    public void testGetEmpByConditionForeach() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> list = mapper.getEmpByConditionForeach(Arrays.asList(2,6));
            for (Employee emp : list) {
                System.out.println(emp);
            }
        }finally {
            sqlSession.close();
        }
    }

    /**
     * 测试foreach实现批量插入
     */
    @Test
    public void testBatchSave() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> emps = new ArrayList<>();
            emps.add(new Employee(null,"Smith","Smith@qq.com","1",new Department(1)));
            emps.add(new Employee(null,"Tom","Tom@qq.com","0",new Department(2)));
            mapper.addEmp(emps);
            sqlSession.commit(); //提交
        }finally {
            sqlSession.close();
        }
    }
}