目标
将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>
方案
- 转换工具 ibatis2mybatis
- 传参格式的转换
- replace $id$ with ${id}
- replace #id# with #{id}
- replace xyz[] with item for use in iterators
- 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
- 无法转换的Element
- dynamic
- isPropertyAvailable
- isNotPropertyAvailable
- isNotParameterPresent
- isParameterPresent
(to be continued ……)