Mybatis——动态SQL详解

动态SQL

<!-- 
• if:判断
• choose (when, otherwise):分支选择；带了break的swtich-case
    如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合
     -->

bean:

public class Department {

    private Integer id;
    private String departmentName;
    private List<Employee> emps;    

    public Department(Integer id) {
        super();
        this.id = id;
    }
    public Department() {
        super();
        // TODO Auto-generated constructor stub
    }
    public List<Employee> getEmps() {
        return emps;
    }
    public void setEmps(List<Employee> emps) {
        this.emps = emps;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getDepartmentName() {
        return departmentName;
    }
    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
    @Override
    public String toString() {
        return "Department [id=" + id + ", departmentName=" + departmentName + "]";
    }


}

public class Employee {
    private Integer id;
    private String last_name;
    private String email;
    private String gender;
    private Department department;

    public Employee(Integer id, String last_name, String email, String gender, Department department) {
        super();
        this.id = id;
        this.last_name = last_name;
        this.email = email;
        this.gender = gender;
        this.department = department;
    }
    public Department getDepartment() {
        return department;
    }
    public void setDepartment(Department department) {
        this.department = department;
    }
    public Employee(Integer id, String last_name, String email, String gender) {
        super();
        this.id = id;
        this.last_name = last_name;
        this.email = email;
        this.gender = gender;
    }
    public Employee(){

    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getLast_name() {
        return last_name;
    }
    public void setLast_name(String last_name) {
        this.last_name = last_name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    @Override
    public String toString() {
        return "Employee [id=" + id + ", last_name=" + last_name + ", email=" + email + ", gender=" + gender + "]";
    }
}

接口 EmployeeMapperDynamicSQL

public interface EmployeeMapperDynamicSQL {

    //查询员工，要求，携带了哪个字段查询条件就带上这个字段的值
    public List<Employee> getEmpsByConditionIf(Employee employee);

    public List<Employee> getEmpsByConditionTrim(Employee employee);

    public List<Employee> getEmpsByConditionChoose(Employee employee);

    public void updateEmp(Employee employee);

    public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);

    public void addEmps(@Param("emps")List<Employee> emps);
}

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.mybatis.dao.EmployeeMapperDynamicSQL">
 <!-- 
• if:判断
• choose (when, otherwise):分支选择；带了break的swtich-case
    如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合
     -->
     <!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 -->
    <!--  public List<Employee> getEmpsByConditionIf(Employee employee); -->
    <select id="getEmpsByConditionIf" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee 
        <where>
        <!-- test: 判断表达式
            从参数中取值判断
         -->
        <if test="id!=null">
            and id = #{id}
        </if>
        <if test="last_name!=null and last_name !=''">
            and last_name like #{last_name}
        </if>
        <if test="email!=null and email.trim()!=''">
            and email = #{email}
        </if>
        <if test="gender==0 or gender==1">
            and gender = #{gender}
        </if>
        </where>
    </select>

    <!-- public List<Employee> getEmpsByConditionTrim(Employee employee); -->
    <select id="getEmpsByConditionTrim" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee 
        <!-- 后面多出的and或者or where标签不能解决 
        prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。
                prefix给拼串后的整个字符串加一个前缀 
        prefixOverrides="":
                前缀覆盖： 去掉整个字符串前面多余的字符
        suffix="":后缀
                suffix给拼串后的整个字符串加一个后缀 
        suffixOverrides=""
                后缀覆盖：去掉整个字符串后面多余的字符
        -->
        <trim prefix="where" suffixOverrides="and">
        <if test="id!=null">
            id = #{id} and 
        </if>
        <if test="last_name!=null and last_name !=''">
            last_name like #{last_name} and 
        </if>
        <if test="email!=null and email.trim()!=''">
            email = #{email} and 
        </if>
        <if test="gender==0 or gender==1">
            gender = #{gender}
        </if>
        </trim>
    </select>

    <!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
    <select id="getEmpsByConditionChoose" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <where>
            <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="last_name!=null">
                    last_name like #{last_name}
                </when>
                <when test="email!=null">
                    email = #{email}
                </when>
                <otherwise>
                    gender = 0
                </otherwise>
            </choose>
        </where>
    </select>

    <!-- public void updateEmp(Employee employee); -->
    <update id="updateEmp">
        update tbl_employee 
        <set>
            <if test="last_name!=null">
                last_name=#{last_name},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
        where id=#{id} 
        <!--        
        Trim：更新拼串
        update tbl_employee 
        <trim prefix="set" suffixOverrides=",">
            <if test="last_name!=null">
                last_name=#{last_name},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </trim>
        where id=#{id}  -->
    </update>

    <!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids);  -->
     <select id="getEmpsByConditionForeach" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <!--
            collection：指定要遍历的集合：
                list类型的参数会特殊处理封装在map中，map的key就叫list
            item：将当前遍历出的元素赋值给指定的变量
            separator:每个元素之间的分隔符
            open：遍历出所有结果拼接一个开始的字符
            close:遍历出所有结果拼接一个结束的字符
            index:索引。遍历list的时候是index就是索引，item就是当前值
                          遍历map的时候index表示的就是map的key，item就是map的值

            #{变量名}就能取出变量的值也就是当前遍历出的元素
          -->
        <foreach collection="ids" item="item_id" separator=","
            open="where id in(" close=")">
            #{item_id}
        </foreach>
     </select>

<!-- 批量保存 -->
    <!-- public void addEmps(@Param("emps")List<Employee> emps); -->    
    <insert id="addEmps">
        insert into tbl_employee(last_name,email,gender,d_id)
        values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.last_name},#{emp.email},#{emp.gender},#{emp.department.id})
        </foreach>
    </insert> 
    <!-- 这种方式需要数据库连接属性allowMultiQueries=true；
        这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->
     <!-- <insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
            insert into tbl_employee(last_name,email,gender,d_id)
            values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        </foreach>
     </insert> -->
 </mapper>

测试类

public class MybatisTest {

    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testDynamicSQL() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(3,"Admin",null,null);
            /*List<Employee> emps = mapper.getEmpsByConditionIf(employee);
            for (Employee employee2 : emps) {
                System.out.println(employee2);
            }*/

            //测试 Trim
            /*List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee);
            for (Employee employee2 : emps2) {
                System.out.println(employee2);
            }*/
            //测试Choose
            /*List<Employee> emps3 = mapper.getEmpsByConditionChoose(employee);
            for (Employee employee2 : emps3) {
                System.out.println(employee2);
            }*/

            //测试 set 更新
            /*mapper.updateEmp(employee);
            openSession.commit();*/

            /*List<Employee> emp2 = mapper.getEmpsByConditionForeach(Arrays.asList(1,2,3,4));
            for (Employee employee2 : emp2) {
                System.out.println(employee2);
            }*/


        } finally {
            openSession.close();
        }
    }
    @Test
    public void testBatchSave() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> emps = new ArrayList<Employee>();
            emps.add(new Employee(null, "smith", "smith@qq.com", "1",new Department(1)));
            emps.add(new Employee(null, "alien", "alien@qq.com", "0",new Department(2)));
            emps.add(new Employee(null, "TTT", "TTT@qq.com", "1",new Department(1)));
            emps.add(new Employee(null, "hhh", "hhh@qq.com", "1",new Department(1)));
            mapper.addEmps(emps);
            openSession.commit();
        }finally {
            openSession.close();
        }
    }

}