mybatis--动态SQL,sql里使的各种标签

一、if判断

  <?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
          mybats使用<where>标签时来将所有的查询条件包括在内，mybatis就会将where标签里多余的and或or去掉，
便会出现问题
        -->
        <where>
            <!-- test：判断表达式（OGNL）
            OGNL参照PPT或者官方文档。
                 c:if  test
            从参数中取值进行判断

            遇见特殊符号应该去写转义字符：
            &&：&amp;&amp;
            '':&quot;
            还可以调用相应方法：email.trim()，去除前后空格
            -->
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                and last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                and email=#{email}
            </if> 
            <!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
        </where>
     </select>

二、 trim 自定义字符串的截取规则

<!--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="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                email=#{email} and
            </if> 
            <!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
         </trim>
     </select>

此标签使的情况并不是很多

三、 choose分支选择

做一个选择查询，如果参数带了id便用id进行查询，如果带了lastName便用lastName进行查询，即只进入一个分支。如下例：

<!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
    <select id="getEmpsByConditionChoose" resulType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <where>
            <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>

四、 set 封装修改条件

set标签同where标签有一样的功能， set标签可以去掉多余的逗号，如果不用标签，只有set那可以会应该某些条件没有传值，而有多余的逗号，从而导致报错。当然也可以使用上述的trim自定义字符串，如下例：

 <update id="updEmp">
    update tbl_employee 
     <set>
         <if test="lastName!=null">
            last_name=#{lastName},
        </if>
        <if test="email!=null">
            email=#{email},
        </if>
        <if test="gender!=null">
            gender=#{gender}
        </if>
    </set>       
 </update>

             <!---Trim：更新拼串-->
        update tbl_employee 
        <trim prefix="set" suffixOverrides=",">
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </trim>
        where id=#{id}  
     </update>

五 、foreach

<!--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>

使用foreach可以完成批量插入，其批量插入有两种方法如下：
1

 <!-- 批量保存 -->
     <!--public void addEmps(@Param("emps")List<Employee> emps);  -->
     <!--MySQL下批量保存：可以foreach遍历   mysql支持values(),(),()语法-->
    <insert id="addEmps">
        insert into tbl_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>

2.

<!-- 这种方式需要数据库连接属性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> 

注：数据库配置如 allowMultiQueries可如下：
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
jdbc.username=root
jdbc.password=123456

但是oracle数据库的批量保存跟mysql有所不同：

 <!-- Oracle数据库批量保存： 
        Oracle不支持values(),(),()
        Oracle支持的批量方式
        1、多个insert放在begin - end里面
            begin
                insert into employees(employee_id,last_name,email) 
                values(employees_seq.nextval,'test_001','test_001@atguigu.com');
                insert into employees(employee_id,last_name,email) 
                values(employees_seq.nextval,'test_002','test_002@atguigu.com');
            end;
        2、利用中间表：
            insert into employees(employee_id,last_name,email)
               select employees_seq.nextval,lastName,email from(
                      select 'test_a_01' lastName,'test_a_e01' email from dual
                      union
                      select 'test_a_02' lastName,'test_a_e02' email from dual
                      union
                      select 'test_a_03' lastName,'test_a_e03' email from dual
               )    
     -->
      <insert id="addEmps" databaseId="oracle">
        <!-- oracle第一种批量方式 -->
        <foreach collection="emps" item="emp" open="begin" close="end;">
            insert into employees(employee_id,last_name,email) 
                values(employees_seq.nextval,#{emp.lastName},#{emp.email});
        </foreach>
<!-- oracle第二种批量方式  -->
    <insert id="addEmps" databaseId="oracle" >
        insert into employees(employee_id,last_name,email)
        select employees_seq.nextval,lastName,email from(
            <foreach collection="emps" item="emp" separator="union">
                select #{emp.lastName} lastName,#{emp.emial} email from dual
            </foreach>
        )
     </insert>
<!--也可以使用foreach里的open和close属性改变上述写法-->
    <insert id="addEmps" databaseId="oracle" >
        insert into employees(employee_id,last_name,email)
            <foreach collection="emps" item="emp" separator="union"
             open="select employees_seq.nextval,lastName,email from(" 
             close=")">
                select #{emp.lastName} lastName,#{emp.emial} email from dual
            </foreach>
        )
     </insert>