MyBatis 动态SQL 详解

动态 SQL 的元素

元素名称	描述	备注
if	判断语句	单条件分支判断
choose(when、otherwise)	相当于 Java 中的 case when 语句	多条件分支判断
trim(where、set)	辅助元素	用于处理一些 SQL 拼装问题
foreach	循环语句	在 in 语句等列举条件常用

if 元素

例如如下代码

< update id ="updateByPrimaryKeySelective" parameterType ="com.jintoufs.hyb.domain.user.UserRole" >

   UPDATE hyb_user_role

   < set >

      < if test ="userPin != null and userPin != ''" >

         user_pin = #{userPin,jdbcType=VARCHAR},

      </ if >

      < if test ="roleCode != null and roleCode != ''" >

         role_code = #{roleCode,jdbcType=VARCHAR},

      </ if >

   </ set >

   WHERE id = #{id,jdbcType=BIGINT}

</ update >

choose、when、otherwise 元素

例如如下代码

< sql id ="Common_Query" >

    < where >

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

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

        < if test ="orgCode != null and orgCode != ''" >

            < choose >

                < when test ="orgCodeMatchType != null and orgCodeMatchType.value == 1" >  AND org_code = #{orgCode}   </ when >

                < when test ="orgCodeMatchType != null and orgCodeMatchType.value == 8" >  AND org_code LIKE CONCAT(#{orgCode},'%')   </ when >

                < otherwise >  AND org_code = #{orgCode}   </ otherwise >

            </ choose >

        </ if >

        < if test ="parentOrgCode != null and parentOrgCode != ''" >

            < choose >

                < when test ="parentOrgCodeMatchType != null and parentOrgCodeMatchType.value == 1" >  AND parent_org_code = #{parentOrgCode}   </ when >

                < when test ="orgCodeMatchType != null and parentOrgCodeMatchType.value == 8" >  AND parent_org_code LIKE CONCAT(#{parentOrgCode},'%')   </ when >

                < otherwise >  AND parent_org_code = #{parentOrgCode}   </ otherwise >

            </ choose >

        </ if >

        < if test ="orgName != null and orgName != ''" >  AND org_name LIKE "%"#{orgName}"%"   </ if >

        < if test ="orgVisitCode != null and orgVisitCode != ''" >  AND org_visit_code = #{orgVisitCode}   </ if >

        < if test ="channelCode != null and channelCode != ''" >  AND LEFT(org_code, 4) = #{channelCode}   </ if >

    </ where >

</ sql >

where元素

where元素可以自动去处理 SQL 以达到预期效果，where 元素会自动删除多余的前缀

例如如下代码

< sql id ="CommonQuery" >

   < where >

      < if test ="id != null and id > 0" >  AND mbi.id = #{id}   </ if >

      < if test ="memberPin != null and memberPin != ''" >  AND mbi.member_pin = #{memberPin}   </ if >

      < if test ="scope != null and scope != ''" >  AND mbi.scope = #{scope}   </ if >

      < if test ="auditingStatus != null and auditingStatus > 0" >  AND mbi.auditing_status = #{auditingStatus}   </ if >

      < if test ="accountStatus != null and accountStatus > 0" >  AND mbi.account_status = #{accountStatus}   </ if >

      < if test ="idCardNo != null and idCardNo != ''" >  AND mbi.id_card_no = #{idCardNo}   </ if >

      < if test ="memberName != null and memberName != ''" >  AND mbi.member_name = #{memberName}   </ if >

      < if test ="mobile != null and mobile != ''" >  AND mbi.mobile = #{mobile}   </ if >

      < if test ="memberTypeId != null and memberTypeId > 0" >  AND mbi.member_type_id = #{memberTypeId}   </ if >

      < if test ="provinceCode != null and provinceCode != ''" >  AND LEFT(mbi.region_code, 6) = #{provinceCode}   </ if >

    </ where >

</ sql >

< select id ="selectMemberInfoCountsByQuery" resultType ="long" parameterType ="com.jintoufs.hyb.domain.member.query.MemberBaseInfoQuery" >

   SELECT COUNT ( * )  FROM  member_base_info AS mbi

   < include refid ="CommonQuery" />

</ select >

trim  元素

trim标记是一个格式化的标记，可以完成set或者是where标记的功能，其有四个属性 :

prefix : 代表语句前缀

prefixOverrides : 去掉前面的哪种字符串，多个元素之间使用 | 隔开

suffix : 代表语句后缀

suffixOverrides : 去掉后面的哪种字符串，多个元素之间使用 | 隔开

例如如下代码

< select id ="findByName" parameterType ="string" resultMap ="BaseResultMap" useCache ="true" >

    SELECT *
    FROM cn_user

    < trim prefix ="WHERE" prefixOverrides ="AND|OR" >

        < if test ="name != null and name.length()>0" > AND name=#{name} </ if >

        < if test ="gender != null and gender.length()>0" > AND gender=#{gender} </ if >

    </ trim >

</ select >

set 元素

常用语更新那些不为空的字段，而不必更新所有字段，set元素会自动把对应都好去掉

< update id ="updateByPrimaryKeySelective" parameterType ="com.jintoufs.hyb.domain.commission.CommissionOtherInsDetail" >

   UPDATE commission_other_ins_detail

   < set >

      < if test ="commissionStatus != null" >

         commission_status = #{commissionStatus,jdbcType=TINYINT},

      </ if >

      < if test ="commissionRate != null" >

         commission_rate = #{commissionRate,jdbcType=VARCHAR},

      </ if >

      < if test ="commission != null" >

         commission = #{commission,jdbcType=INTEGER},

      </ if >

      < if test ="summaryId != null" >

         summary_id = #{summaryId,jdbcType=BIGINT},

      </ if >

      < if test ="commissionId != null" >

         commission_id = #{commissionId,jdbcType=BIGINT},

      </ if >

   </ set >

   WHERE id = #{id,jdbcType=BIGINT}

</ update >

foreach 元素

foreach元素用于遍历集合

collection : 是一个数组、List、Set等的集合

item : 当前循环中的元素

index : 当前元素在集合的位置下标

open、close : 以什么符号将这些元素包装起来

separator : 各元素之间的分割符

< select id ="selectMemberOdrCountByStatus" resultType ="long" >

   SELECT COUNT ( * )  FROM odr_order

   WHERE  member_pin = #{memberPin}

   AND order_status IN < foreach item ="item" index ="index" collection ="statusArray" open ="(" separator ="," close =")" > #{item} </ foreach >

</ select >

test 属性

主要用于判断空和非空，有时判断字符串、数字、枚举等

< sql id ="Common_Query" >

   < where >

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

      < if test ="memberPin != null and memberPin != ''" >  AND member_pin = #{memberPin}   </ if >

      < if test ="memberMobile != null and memberMobile != ''" >  AND member_mobile = #{memberMobile}   </ if >

      < if test ="memberName != null and memberName != ''" >  AND member_name = #{memberName}   </ if >

      < if test ="mode != null and (mode == 1 or mode == 2)" >  AND mode = #{mode}   </ if >

      < if test ="beginChangeTime != null" >  AND <![CDATA[TO_DAYS(change_time) >= TO_DAYS(#{beginChangeTime})]]>   </ if >

      < if test ="endChangeTime != null" >  AND <![CDATA[TO_DAYS(change_time) <= TO_DAYS(#{endChangeTime})]]>   </ if >

   </ where >

</ sql >

bind 元素

通过 OGNL 表达式去自定义上下文变量，如果是 MySQL 数据库，常常用到一个 concat 用 “％” 和参数相连接，而 Oracle 数据库则是用连接符号 “||”。使用 bind 元素就不需要使用数据库语言，只要使用 MyBatis 语言即可与所需参数相连

在接口中

List<RoleBean> findRole( @Param( "roleName") String roleName, @Param( "note") String note);

在 xml 中

< select id ="findRole" resultType ="com.chenshun.RoleBean" >

    < bind name ="pattern_roleName" value ="'%' + roleName + '%'" />

    < bind name ="pattern_note" value ="'%' + note + '%'" />

    SELECT id,role_name as roleName, create_date as createDate,end_date as endFlag

    FROM t_role

    WHERE role_name LIKE #{pattern_roleName} AND note LIKE #{pattern_note}

</ select >