前言

动态sql是mybatis强大特性之一,简化了拼装sql的操作,大量的判断都可以在mybatis的映射xml文件里面配置,打到原本需要大量代码才能实现的功能,大大减少了编写代码的工作量,体现了mybatis的灵活性,高度可配置性和可维护性

元素

mybatis的动态sql包含以下几个元素
MyBatis动态SQL - 图1
下面来讨论一些这些元素的用法

if

常用的判断语句,相当于java中的if语句,常常与test属性联合使用

这么一个需求:我们需要根据lastName查询员工信息,但是lastName是一个可填可不填的条件,不填写的时候就不要用它作为查询条件了.

  1. select * from tbl_employee
  2.    <!-- test：判断表达式（OGNL）
  3.    OGNL参照PPT或者官方文档。
  4.        c:if  test
  5.    从参数中取值进行判断
  7.    遇见特殊符号应该去写转义字符：
  8.    &&：
  9.    -->
  10. where
  11.    <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
  12.       and last_name like #{lastName}
  13.    </if>

这样就会有一个问题啊,如果lastname有值,那么sql就会多了一个and,那么这样有两个解决方案

1.给where后面加上1=1,以后的条件都and xxx

2.mybatis使用where标签来将所有的查询条件包括在内,mybatis就会将where标签中拼装的sql,多出来的and或者or去掉. 注意: where只会去掉第一个多出来的and或者or

eg:

  2. select * from tbl_employee
  3. <!-- where -->
  4. <where>
  5.    <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
  6.       and last_name like #{lastName}
  7.    </if>
  8. </where>

choose

  2. select * from tbl_employee 
  3. <where>
  4.    <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
  5.    <choose>
  6.       <when test="id!=null">
  7.          id=#{id}
  8.       </when>
  9.       <when test="lastName!=null">
  10.          last_name like #{lastName}
  11.       </when>
  12.       <when test="email!=null">
  13.          email = #{email}
  14.       </when>
  15.       <otherwise>
  16.          gender = 0
  17.       </otherwise>
  18.    </choose>
  19. </where>

trim

我们在讲if的时候, 最后讲到了,为了去掉多余的and或者or,添加了where标签.但是where只会去掉第一个多出来的and或者or. 如果现在是在后面多出的and或者or, where标签就不能解决了. 引入新的标签trim

trim意味着我们需要去掉一些特殊的字符串

prefix代表的语句的前缀,给拼串后的整个字符串加上一个前缀

prefixOverrides代表的是需要去掉的那种字符串,去掉整个字符串前面多余的字符

suffix:后缀,给拼串后的整个字符串加上一个后缀

suffixOverrides:后缀覆盖,去掉整个字符串后面多余的字符

  2. select * from tbl_employee
  3. <!-- 自定义字符串的截取规则 -->
  4. <trim prefix="where" suffixOverrides="and">
  5.     <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
  6.          last_name like #{lastName} and
  7.     </if>
  8. </trim>

在hibernate中如果想更新某个字段,是需要发送所有的字段给持久对象的.但是这样对网络宽带消耗很大呀.而在mybatis中,就可以使用set来完成这些功能

set

  2. <!-- Set标签的使用 -->
  3.    update tbl_employee 
  4. <set>
  5.    <if test="lastName!=null">
  6.       last_name=#{lastName},
  7.    </if>
  8.    <if test="email!=null">
  9.       email=#{email},
  10.    </if>
  11.    <if test="gender!=null">
  12.       gender=#{gender}
  13.    </if>
  14. </set>
  15. where id=#{id}

set元素遇到了逗号,它会把对应的逗号去掉

如果不用set标签,那就转变成对应的trim元素,代码如下:

  2. update tbl_employee 
  3. <trim prefix="set" suffixOverrides=",">
  4.    <if test="lastName!=null">
  5.       last_name=#{lastName},
  6.    </if>
  7.    <if test="email!=null">
  8.       email=#{email},
  9.    </if>
  10.    <if test="gender!=null">
  11.       gender=#{gender}
  12.    </if>
  13. </trim>
  14. where id=#{id}

foreach

  2. <!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids);  -->
  3. <select id="getEmpsByConditionForeach" resultType="com.kwy.mybatis.bean.Employee">
  4.    select * from tbl_employee
  5.    <!--
  6.       collection：指定要遍历的集合：
  7.          list类型的参数会特殊处理封装在map中，map的key就叫list
  8.       item：将当前遍历出的元素赋值给指定的变量
  9.       separator:每个元素之间的分隔符
  10.       open：遍历出所有结果拼接一个开始的字符
  11.       close:遍历出所有结果拼接一个结束的字符
  12.       index:索引。遍历list的时候是index就是索引，item就是当前值
  13.                   遍历map的时候index表示的就是map的key，item就是map的值
  15.       #{变量名}就能取出变量的值也就是当前遍历出的元素
  16.      -->
  17.    <foreach collection="ids" item="item_id" separator=","
  18.       open="where id in(" close=")">
  19.       #{item_id}
  20.    </foreach>
  21. </select>

保存的时候,就会有批量保存,可以foreach遍历,mysql支持values(),(),()语法

eg1:

  2. <insert id="addEmps">
  3.    insert into tbl_employee(
  4.    <!-- 引用外部定义的sql -->
  5.       <include refid="insertColumn"></include>
  6.    ) 
  7.    values
  8.    <foreach collection="emps" item="emp" separator=",">
  9.       (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
  10.    </foreach>
  11.  </insert>
  2. <!-- 
  3.    抽取可重用的sql片段。方便后面引用 
  4.    1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
  5.    2、include来引用已经抽取的sql：
  6.    3、include还可以自定义一些property，sql标签内部就能使用自定义的属性
  7.          include-property：取值的正确方式${prop},
  8.          #{不能使用这种方式}
  9. -->
  10. <sql id="insertColumn">
  11.       <if test="_databaseId=='oracle'">
  12.          employee_id,last_name,email
  13.       </if>
  14.       <if test="_databaseId=='mysql'">
  15.          last_name,email,gender,d_id
  16.       </if>
  17. </sql>

eg2:

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

讲完了mysql,我们来讲一下oracle

Oracle数据库批量保存: 不支持values(),(),()

Oracle支持的批量方式

  1. 1、多个insert放在begin - end里面
  2.       begin
  3.        insert into employees(employee_id,last_name,email) 
  4.        values(employees_seq.nextval,'test_001','test_001@atguigu.com');
  5.        insert into employees(employee_id,last_name,email) 
  6.        values(employees_seq.nextval,'test_002','test_002@atguigu.com');
  7.       end;
  8. sql:
  9.     <insert id="addEmps" databaseId="oracle">
  10.         <!-- oracle第一种批量方式 -->
  11.          <foreach collection="emps" item="emp" open="begin" close="end;">
  12.             insert into employees(employee_id,last_name,email) 
  13.             values(employees_seq.nextval,#{emp.lastName},#{emp.email});
  14.         </foreach>
  17. 2、利用中间表：
  18.    insert into employees(employee_id,last_name,email)
  19.        select employees_seq.nextval,lastName,email from(
  20.               select 'test_a_01' lastName,'test_a_e01' email from dual
  21.               union
  22.               select 'test_a_02' lastName,'test_a_e02' email from dual
  23.               union
  24.               select 'test_a_03' lastName,'test_a_e03' email from dual
  25.        )
  27. sql:
  28.      <!-- oracle第二种批量方式  -->
  29.         insert into employees(employee_id,last_name,email)
  30.         <foreach collection="emps" item="emp" separator="union"
  31.                 open="select employees_seq.nextval,lastName,email from("
  32.                 close=")">
  33.                     select #{emp.lastName} lastName,#{emp.email} email from dual
  34.         </foreach>

内置参数

两个内置参数：

  1.  不只是方法传递过来的参数可以被用来判断，取值。。。     mybatis默认还有两个内置参数：     _parameter:代表整个参数         单个参数：_parameter就是这个参数         多个参数：参数会被封装为一个map；_parameter就是代表这个map          _databaseId:如果配置了databaseIdProvider标签。         _databaseId就是代表当前数据库的别名oracle

bind,可以将gnl表达式的值绑定到一个变量中,方便后来引用这个变量的值

eg:

  2. <!--根据课程名称+学年学期查试卷名称-->
  3. <select id="findPaperNameByName" parameterType="String" resultType="String">
  4.     <bind name="_tmpName" value="'%'+tmpName+'%'"/>
  5.     SELECT
  6.     tpq.name
  7.     FROM
  8.     te_template_paper tpq
  9.     WHERE
  10.     tpq.name LIKE #{_tmpName}
  11.     AND tpq.is_delete = 0 ORDER BY tpq.name desc limit 1
  12. </select>

如果不使用bind

应该是这样的

  2. <!--根据课程名称+学年学期查试卷名称-->
  3. <select id="findPaperNameByName" parameterType="String" resultType="String">
  4.     SELECT
  5.     tpq.name
  6.     FROM
  7.     te_template_paper tpq
  8.     WHERE
  9.     tpq.name LIKE concat('%'+tmpName +'%')
  10.     AND tpq.is_delete = 0 ORDER BY tpq.name desc limit 1
  11. </select>

使用concat函数连接字符串,在mysql中,这个函数是支持多个函数的,但是oracle只支持两个参数,由于不同数据库之间的语法差异,如果更换数据库,有些sql语句可能就需要重写,针对这种情况,使用bind标签就可以避免了.

bind 标签的两个属性都是必选项， name 为绑定到上下文的变量名， va l ue 为 OGNL 表 达式。创建一个 bind 标签的变量后 ， 就可以在下面直接使用，使用 bind 拼接字符串不仅可 以避免因更换数据库而修改 SQL，也能预防 SQL 注入。