MyBatis_动态SQL

实现动态SQL语句： 

<?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="mybatis.dao.EmployeeMapperDynamicSQL">
    <!--测试动态SQL的xml文件-->
    <!--实现动态SQL的标签
    if：判断
    choose（when,otherwise）；分支选择
    trim(where(封装),set（封装条件）)：字符截取
    foreach
    -->
    <!--查询员工，要求，携带了哪个字段查询条件就带上这个字段的值
    //按照条件查询
    public List<Employee> getEmpsByCondutionIf(Employee employee);
    -->
    <select id="getEmpsByCondutionIf" resultType="mybatis.bean.Employee">
        select id,last_name lastName,gender,email  FROM tbl_employee
        <where><!--where标签代替sql语句中的where防止出现id=null但是后面有不等于null而出现的sql语法错误  也可以用1=1代替-->
        <!--test:判断表达式（OGNL）-->
        <if test="id!=null">
             id=#{id}
        </if>

        <if test="lastName !=null and lastName != '' ">
            AND last_name like #{lastName}
        </if>

        <!-- email.trim-->
        <if test="email !=null and email.trim()!='' ">
            AND email = #{email}
        </if>

        <if test="gender == 0 or gender == 1">
            AND gender = #{gender}
        </if>
        </where>
    </select>


    <!--
    //测试trim标签
    public List<Employee> getEmpsByCondutionIfAndTrim(Employee employee);
    -->
    <select id="getEmpsByCondutionIfAndTrim" resultType="mybatis.bean.Employee">
    select id,last_name lastName,gender,email
    FROM tbl_employee
        <!--trim 标签 sql语句中后面多出的and 和 or  where标签不能解决
            trim标签体中是整个字符串拼接后的结果
                prefix：给拼串后的字符串加一个前缀
                prefixOverrides:前缀覆盖；前面多了什么给去掉
                suffix:给拼串后的字符串加一个后缀
                suffixOverrides:后缀覆盖；后面多了什么给去掉
        -->
        <trim prefix="where" suffixOverrides="and">

        <if test="id!=null">
            id=#{id} AND
        </if>

        <if test="lastName !=null and lastName != '' ">
            last_name like #{lastName} AND
        </if>

        <!-- email.trim-->
        <if test="email !=null and email.trim()!='' ">
             email = #{email} AND
        </if>

        <if test="gender == 0 or gender == 1">
            gender = #{gender}
        </if>
        </trim>
    </select>


    <!--
    查询员工：如果带了id用id查，带了lastName 用lastName查.....多选一 都不带 查所有
    //按照id或者lastName查询员工
    public List<Employee> getEmpsByCondutionChoose(Employee employee);
    -->
    <select id="getEmpsByCondutionChoose" resultType="mybatis.bean.Employee">
        <!--bind 绑定 -->
        <bind name="my_lastname" value="'%'+lastName+'%'"></bind>
        SELECT id,last_name lastName,gender,email
        FROM tbl_employee
        <where>
            <choose>
                <when test="id != null">
                    id=#{id}
                </when>
                <when test="lastName != null and lastName != '' ">
                    last_name LIKE #{my_lastname}
                </when>
                <when test="email != null and email != '' ">
                    email LIKE #{email}
                </when>
                <when test="gender == 0 and gender == 1 ">
                    gender = #{gender}
                </when>
                <otherwise >
                    1=1
                </otherwise>
            </choose>

        </where>

    </select>


    <!--
    要求，传入的employee带了什么参数则更新哪个参数（区别之前的全更新）

    //employee 传入什么则更新什么
    public void updateEmp(Employee employee);
    -->
    <update id="updateEmp">
        UPDATE tbl_employee
        <set>
            <if test="lastName!=null and lastName !='' ">
                last_name = #{lastName},
            </if>
            <if test="email !=null and email !='' ">
                email = #{email},
            </if>
            <if test="gender == 0 or gender == 1">
                gender = #{gender},
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>


    <!--
    //测试foreach 传入一个id的list集合查询员工
    public List<Employee> getEmpsByList (List<Integer> list);
    -->
    <select id="getEmpsByList" resultType="mybatis.bean.Employee">
        SELECT id,last_name lastName,gender,email
        FROM tbl_employee
        WHERE id IN
        <!--
        foreach:遍历集合
            collection：需要遍历哪个集合
            item：将遍历出的数据赋值给谁
            separator：每个遍历中间用什么隔开
            open：遍历的集合用什么开始
            close：遍历的集合用什么结束
        -->
        <foreach collection="list" item="ltem_id" separator="," open="(" close=")">
          #{ltem_id}
        </foreach>

    </select>

    <!--
     //测试利用foreach标签实现批量插入
    public void addEmpsByForeach(@Param("emps")List<Employee> employees);
    -->
    <insert id="addEmpsByForeach">
        INSERT INTO tbl_employee( last_name,gender,email)
        VALUES
        <foreach collection="emps" item="item_emps" separator=",">
            (#{item_emps.lastName},#{item_emps.gender},#{item_emps.email})
        </foreach>
    </insert>


    <!--
    两个内置参数：
    _parameter：代表整个参数
            单个参数：_parameter就是代表这个参数
            多个参数：_parameter就是代表这个map
    _databaseId:如果配置了databaseIdProvider标签
            _databaseId就是代表当前数据库的别名


    -->
    
</mapper>