mybatis动态sql详细讲解

动态 SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。

动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

• if
• where
• choose (when, otherwise)
• trim
• set
• foreach

 

一、用xml实现的动态sql

 

if + where 的条件判断

if        判断条件是否满足  满足 if中的sql 自动拼接到主sql
where 自动判断第一个条件是否存在where 如果没有where 追加 同时去掉一个and

    <!-- if 判断条件是否满足  满足 if中的sql 自动拼接到主sql -->
    <select id="getStudent" resultMap="studentMap">
        select * from student
        <where>
            <if test="sex != null and sex != ''">
                and ssex = #{sex}
            </if>
            <if test="name !=null and name != ''">
                and sname like '%${name}%'
            </if>
            <if test="age !=null and age != ''">
                and sage = #{age}
            </if>
        </where>
    </select>

 

choose (when, otherwise)标签

choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

    <!-- choose 判断条件是否满足  满足 when中的sql 自动拼接到主sql 不满足就进入 otherwise-->
    <select id="queryStudentSex" resultMap="studentMap">
        select * from student
        <where>
            <choose>
                <when test="sex!=null and sex != ''">
                    and ssex=#{sex}
                </when>
                <otherwise>
                    and ssex='男'
                </otherwise>
            </choose>
        </where>
    </select>

 

trim标签

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

prefix                           配置的参数会被添加 在sql语句开始的地方
prefixOverrides          sql语句首次出现的参数会被覆盖
suffix                           配置的参数会被添加 在sql语句结束的地方
suffixOverrides          sql语句最后一次出现的参数会被覆盖

使用trim去除and 添加where

  <select id="queryStudent" resultType="student">
 		select * from student 
 		<trim prefix="where" prefixOverrides="and">
 			<if test="sname!=null">
 			and sname like '%${sname}%'
	 		</if>
	 		<if test="address!=null">
	 			and address like '%${address}%'
	 		</if>
 		</trim>
 		
  </select>

 

if + set实现修改语句

当update语句中没有使用if标签时，如果有一个参数为null，都会导致错误。 
当在update语句中使用if标签时，如果前面的if没有执行，则或导致逗号多余错误。使用set标签可以将动态的配置SET 关键字，和剔除追加到条件末尾的任何不相关的逗号。使用if+set标签修改后，如果某项为null则不进行更新，而是保持数据库原值。如下示例：

    <update id="updateStudent">
        update student
        <set>
            <if test="name!=null and name != ''">
                sname=#{name},
            </if>
            <if test="age!=null and age != ''">
                sage=#{age},
            </if>
            <if test="sex!=null and sex != ''">
                ssex=#{sex}
            </if>
        </set>
        where sid=#{id}
    </update>

 

if + trim代替if/set标签

trim是更灵活的去处多余关键字的标签，他可以实践where和set的效果。

    <update id="updateStudent">
        update student
        <trim prefix="set" prefixOverrides="" suffixOverrides="," suffix="">
            <if test="name!=null and name != ''">
                sname=#{name},
            </if>
            <if test="age!=null and age != ''">
                sage=#{age},
            </if>
            <if test="sex!=null and sex != ''">
                ssex=#{sex}
            </if>
        </trim>
        where sid=#{id}
    </update>

 

foreach标签

对于动态SQL 非常必须的，主是要迭代一个集合，通常是用于IN 条件。List 实例将使用“list”做为键，数组实例以“array” 做为键。

foreach元素是非常强大的，它允许你指定一个集合，声明集合项和索引变量，它们可以用在元素体内。它也允许你指定开放和关闭的字符串，在迭代之间放置分隔符。这个元素是很智能的，它不会偶然地附加多余的分隔符。

注意：你可以传递一个List实例或者数组作为参数对象传给MyBatis。当你这么做的时候，MyBatis会自动将它包装在一个Map中，用名称在作为键。List实例将会以“list”作为键，而数组实例将会以“array”作为键。

open               表示循环开始之前追加sql
close              表示 循环结束之后追加的sql
collection       指定传入的值的集合
separator       非最后一次的循环 值上添加 分隔符,
item                表示每一次循环的值被存储的变量名

    <select id="queryStudentByClasses" resultMap="studentMap">
        select * from student
        <foreach item="cid" collection="classesList" open="where gid in (" close=")" separator=",">
            ${cid}
        </foreach>
    </select>

 

concat模糊查询

比如说我们想要进行条件查询,但是几个条件不是每次都要使用,那么我们就可以通过判断是否拼接到sql中

 <select id="queryById" resultMap="studentMap">
    SELECT *  from student
    <where>
        <if test="name!=null">
            sname like concat('%',concat(#{name},'%'))
        </if>
    </where>
  </select>

bind标签

 使用用一个bind标签 bind标签差不多就是 name定义一个名字 value给数据拼接

    <select id="getStudent" resultMap="studentMap">
        select * from student
        <trim prefix="where" prefixOverrides="and">
            <if test="name !=null and name != ''">
                <bind name="nameLike" value="'%'+name+'%'"></bind>
                and sname like #{nameLike}
            </if>
        </trim>
    </select>