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", "[e-mail]", "1",new Department(1)));            emps.add(new Employee(null, "alien", "[e-mail]", "0",new Department(2)));            emps.add(new Employee(null, "TTT", "[e-mail]", "1",new Department(1)));            emps.add(new Employee(null, "hhh", "[e-mail]", "1",new Department(1)));            mapper.addEmps(emps);            openSession.commit();        }finally {            openSession.close();        }    }}