ibatis to mybatis.Part 1

作者

目标

将ibatis的dynamic SQL，翻译为符合mybatis规范的SQL

两者区别

ibatis

官方文档
标签可以嵌套，具体包括以下几种：

  • Binary Conditional Elements
<isEqual prepend="AND"  property="status"  compareValue="Y">
MARRIED = 'TRUE'
</isEqual>
  • Unary Conditional Elements
<isNotNull prepend="AND" property="order.id" >
  ORDER.ORDER_ID = #order.id#
</isNotEmpty>
  • Parameter Present Elements
<isParameterPresent prepend="AND">
  EMPLOYEE_TYPE = #empType#
</isParameterPresent>
  • Iterate Element
<select id="select-test" resultMap="MyTableResult" parameterClass="list">
select * from my_table where col_1 in
  <iterate open="(" close=")" conjunction=",">
   #[]#
  </iterate>
</select>
  • Simple Dynamic SQL Elements
<statement id="getProduct" resultMap="get-product-result">
  select * from PRODUCT order by $preferredOrder$
</statement>

mybatis

官方文档

  • if
<if test="title != null">
  AND title like #{title}
</if>
<if test="author != null and author.name != null">
  AND author_name like #{author.name}
</if>
  • choose (when, otherwise)
<choose>
  <when test="title != null">
    AND title like #{title}
  </when>
  <when test="author != null and author.name != null">
    AND author_name like #{author.name}
  </when>
  <otherwise>
    AND featured = 1
  </otherwise>
</choose>
  • trim (where, set)
<trim prefix="WHERE" prefixOverrides="AND |OR ">   ... </trim>
  • foreach
<foreach item="item" index="index" collection="list"
    open="(" separator="," close=")">
      #{item}
</foreach>
  • Direct substitution
<select id="selectByNetworkId" parameterType="java.util.Map" resultMap="userResult">
    select user_profile.user_profile_id,  user_profile.first_name
    from user_profile user_profile
    where  user_profile.network_id = #{network_id}  
    order by  user_profile.user_profile.first_name ${sortType}
</select>

方案

  1. 传参格式的转换
    • replace $id$ with ${id}
    • replace #id# with #{id}
    • replace xyz[] with item for use in iterators
  2. Element写法的转换
    • isNull -> object == null OR object.property == null
    • isNotNull -> object != null AND object.property != null
    • isNotEmpty -> object != null AND object.property != null AND object.property != ”
    • isEmpty -> object != null AND (object.property == null OR object.property == ”)
    • isGreaterThan -> property > value (number or another property)
    • isLessThan -> property < value (number or another property)
    • isEqual -> object != null AND object.property == value (number or another property)
    • isLessEqual -> object != null AND object.property <= value (number or another property)
    • iterate
  3. 无法转换的Element
    • dynamic
    • isPropertyAvailable
    • isNotPropertyAvailable
    • isNotParameterPresent
    • isParameterPresent

(to be continued ……)

发表评论