嘘~ 正在从服务器偷取页面 . . .

Mybatis(三)动态SQL


Mybatis(三)动态SQL

动态 SQL 是 MyBatis 的强大特性之一。

1、主要的动态SQL元素

元素作用备注
if判断语句单条件分支判断
choose(when、otherwise)选择语句,类似case when语句多条件分支判断
trim(where、set)辅助元素处理SQL拼装问题
foreach循环控制语句在in语句等列举条件差用

2、if 元素

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。还是以 EmployeeMapper.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.ssm.web.demo.dao.emp.EmployeeMapper">

  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
    select 
    emp_id as empId, emp_name as empName,email as email
    from tbl_emp
    where 1=1
      <if test="empName != null  and empName!='' ">
      and emp_name like #{empName}
      </if>
  </select>

</mapper>

test="empName != null and empName!=''如果不传入 “empName”,那么所有Employee都会返回;如果传入了 “empName” 参数不为空且不等于空字符串,那么就会对 “empName” 一列进行模糊查找并返回对应的结果。

test属性比较常见的是判空,如要判固定值或字符串,则可以这样:

 <if test="sex == '1'  or sex =='MALE' ">
     ...
</if>

你以为这样就可以了吗?看着可以,实际测试过程中还是不行的。

需要这样才可以:

 <if test="sex == '1'.toString()  or sex =='MALE'.toString() ">
     ...
</if>

或者这样:

 <if test=' sex == "1"  or sex == "MALE" ' >
     ...
</if>

2、choose-when-otherwise元素

示例如下:

 <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
    select 
    emp_id as empId, emp_name as empName,email as email
    from tbl_emp
    where 1=1
      <choose>
       <when test="empId != null  and empId!='' ">
          and empId = #{empId}
      </when>
      <when test="empName != null  and empName!='' ">
          and emp_name like #{empName}
      </when>
       <otherwise>
          AND emp_name is not null
        </otherwise>
      </choose>
  </select>

当编号不为空的时候使用编号查询,当编号为空,名称不为空就用名称查询,当编号和名称都为空,就默认查名称不为空的Employee的记录。

3、trim、where、set 元素

上面要写where 1=1这种条件,使用where元素就可以去掉这种条件。示例如下:

<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
    select emp_id as empId, emp_name as empName,email as email
    from tbl_emp
    <where>
          <choose>
           <when test="empId != null  and empId!='' ">
            and empId = #{empId}
          </when>
          <when test="empName != null  and empName!='' ">
            and emp_name like #{empName}
          </when>
           <otherwise>
             AND emp_name is not null
           </otherwise>
      </choose>
      </where>
  </select>

但是默认条件下 有个where AND emp_name is not null会多出一个and,那么使用trim就可以去掉这个多余的and,如:

<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
    select emp_id as empId, emp_name as empName,email as email
    from tbl_emp
    <trim prefix="where"  prefixOverrides="and">
          <choose>
           <when test="empId != null  and empId!='' ">
            and empId = #{empId}
          </when>
          <when test="empName != null  and empName!='' ">
            and emp_name like #{empName}
          </when>
           <otherwise>
             AND emp_name is not null
           </otherwise>
      </choose>
      </trim>
  </select>

trim元素表示要去掉一些特殊的字符串。prefix代表语句前缀,prefixOverrides代表的是需要去掉的那种字符串。与where写法等效。

Set元素:动态地在行首插入 SET 关键字,并会删掉额外的逗号。常常用于不确定字段更新,可以只更新需要变动的字段而不是全部字段。例如:

<update id="updateByExampleSelective" parameterType="map">
    update tbl_emp
    <set>
      <if test="record.empId != null">
        emp_id = #{record.empId,jdbcType=INTEGER},
      </if>
      <if test="record.empName != null">
        emp_name = #{record.empName,jdbcType=VARCHAR},
      </if>
      <if test="record.sex != null">
        sex = #{record.sex,jdbcType=CHAR},
      </if>
      <if test="record.email != null">
        email = #{record.email,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>

也可以使用trim的写法:

<trim prefix="set"  prefixOverrides=",">
    ...
</trim>

4、foreach

foreach是个循环语句,用于对集合进行遍历。

foreach允许指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。

<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
    select emp_id as empId, emp_name as empName,email as email
    from tbl_emp
    where emp_id in
    <foreach item="item" index="index" collection="idList" open="("  separator="," close=")" >
        #{item}
    </foreach>
  </select>

属性说明:

collection 配置的是传进来的集合参数名称。可以是数组、List、Set、Map等集合。

item 配置的是循环中本次迭代的当期元素。如果是Map集合,item是Map的值value。

index 配置的是当前元素在集合的位置下标。如果是Map集合,index就是键,item是值。

open和close 配置以什么符号包装集合元素。

separator 配置的是各个元素的间隔符。

5、bind 元素

bind 元素允许你在 OGNL 表达式以外创建一个变量,并将其绑定到当前的上下文。

实际应用场景1

在实际应用中like查询,比如Mysql数据库,使用concat将参数和”%”拼接。但是Oracle和DB2数据库连接符是”||” ,SQL需要提供两种方式去实现。bind 元素可以很好的解决这种问题。比如以刚才的模糊查询为例:

  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.ssm.web.demo.entity.emp.Employee">
    <bind name="pattern" value="'%' + empName + '%'" />
    select 
    emp_id as empId, emp_name as empName,email as email
    from tbl_emp
    where  emp_name like #{pattern}
  </select>

empName就是实际传过来的参数,与通配符“%”连接之后,赋值给了pattern,下面就可以直接使用pattern变量。绑定多个同理,注意name不要重复即可。

实际应用场景2

如果配置了 databaseIdProvider,可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。比如:

<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into tbl_emp values (#{id}, #{empName},#{sex},#{email})
</insert>

6、script

在带注解的映射器接口类中使用动态 SQL,可以使用 script元素。

@Update({"<script>",
      "update tbl_emp",
      "  <set>",
      "    <if test='emp.empName != null'>emp_Name=#{emp.empName},</if>",
      "    <if test='emp.email != null'>email=#{emp.email},</if>",
      "    <if test='emp.sex != null'>sex=#{emp.sex}</if>",
      "  </set>",
      "where empId=#{empId}",
      "</script>"})
    void updateEmployeeValues(Employee emp);

7、动态 SQL 中的插入脚本语言

MyBatis 从 3.2 版本开始支持插入脚本语言,这允许你插入一种语言驱动,并基于这种语言来编写动态 SQL 查询语句。

这种情况目前没有遇到,如有需要参考Mybatis官网说明。


相关文章:

文章名称
《Mybatis(一)主要组件》
《Mybatis(二)配置》
《Mybatis(三)动态SQL》
《Mybtis(四)工作原理》
《Mybtis(五)Mapper映射器》
《Mybtis(六)Mapper级联》


文章作者: Small-Rose /张小菜
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Small-Rose /张小菜 !
评论
  目录