iBATIS 常用 16 條 SQL 映射模式大全：動態標籤、參數映射、分頁、分組、去重實戰

iBATIS 常用 16 條 SQL 映射模式大全：動態標籤、參數映射、分頁、分組、去重實戰

iBATIS (現 MyBatis 前身) 通過 XML 配置 SQL 映射，提供豐富的動態標籤、參數處理、結果映射機制。本文匯總 16 種高頻實戰模式，涵蓋參數傳遞、動態 SQL、分頁、隨機查詢、分組拼接、排序、結果映射、類型別名、結果重映射等核心技巧。

1. 單值參數

<delete id="deleteLog" parameterClass="long">
    delete from MemberAccessLog where accessTimestamp = #value#
</delete>

2. 對象參數 (POJO)

<insert id="insertLog" parameterClass="com.model.MemberAccessLog">
    insert into MemberAccessLog (accessLogId, memberId, clientIP, ...)
    values (#accessLogId#, #memberId#, #clientIP#, ...)
</insert>

3. Map 參數

<select id="selectActionStat" parameterClass="hashMap" resultMap="actionStatMap">
    select actionId, count(*) as count from MemberAccessLog
    where memberId = #memberId# and accessTimestamp > #start# and accessTimestamp <= #end#
    group by actionId
</select>

4. 數組/集合參數 (IN 查詢)

<update id="updateStatusBatch" parameterClass="hashMap">
    update Question set status = #status#
    <dynamic prepend="where questionId in">
        <isNotNull property="actionIds">
            <iterate property="actionIds" open="(" close=")" conjunction=","> #actionIds[]# </iterate>
        </isNotNull>
    </dynamic>
</update>

actionIds[] 表示遍歷數組/集合元素，dynamic 防止空集合導致 SQL 語法錯誤。

5. 純數組參數 (無 Map 包裹)

<select id="selectActionIds" resultClass="hashMap">
    select moduleId, actionId from StatMemberAction
    <dynamic prepend="where moduleId in">
        <iterate open="(" close=")" conjunction=","> #[]# </iterate>
    </dynamic>
    order by moduleId
</select>

參數直接為數組，#[]# 代表當前元素，無 parameterClass 屬性。

6. 字符串直接替換 ($) — 防注入慎用

<select id="selectCount" parameterClass="hashMap" resultClass="int">
    select count(distinct memberId) from MemberAccessLog
    where accessTimestamp >= #start# and accessTimestamp < #end#
    and actionId in $actionIdString$  <!-- 直接拼接字符串，如 '1,2,3' -->
</select>

7. 分頁查詢 (pagedQuery 配合)

<select id="selectLogPage" parameterClass="hashMap" resultMap="logMap">
    <include refid="selectAllSql"/>
    <include refid="whereSql"/>
    <include refid="pageSql"/>
</select>
<select id="selectLogPage.Count" parameterClass="hashMap" resultClass="int">
    <include refid="countSql"/> <include refid="whereSql"/>
</select>
<sql id="pageSql">
    <dynamic>
        <isNotNull property="startIndex"><isNotNull property="pageSize"> limit #startIndex#, #pageSize# </isNotNull></isNotNull>
    </dynamic>
</sql>

框架先查 .Count 語句獲取總數，再執行帶 limit 的分頁查詢。

8. 特殊字符轉義 (> <)

<delete id="delBefore" parameterClass="long">
    delete from MemberAccessLog where accessTimestamp &lt;= #value#
</delete>
<!-- 或用 CDATA 區塊，但動態標籤失效 -->
<delete id="delBefore" parameterClass="long">
    <![CDATA[ delete from MemberAccessLog where accessTimestamp <= #value# ]]>
</delete>

9. SQL 片段復用 (include/sql)

<sql id="selectBase"> select samplingTimestamp, onlineNum, year, month, week, day, hour from OnlineMemberNum </sql>
<sql id="whereBefore"> where samplingTimestamp &lt;= #samplingTimestamp# </sql>
<select id="selectBefore" parameterClass="hashmap" resultClass="OnlineMemberNum">
    <include refid="selectBase"/> <include refid="whereBefore"/>
</select>

sql 標簽僅供引用，不可直接作為映射語句執行。

10. 隨機查詢 (MySQL)

<sql id="randomSql"> ORDER BY rand() LIMIT #number# </sql>

11. GROUP BY 分組字段拼接 (MySQL GROUP_CONCAT)

<sql id="selectGroupBy">
    SELECT a.answererCategoryId, ..., GROUP_CONCAT(q.categoryName) as categoryName
    FROM AnswererCategory a, QuestionCategory q
    WHERE a.questionCategoryId = q.questionCategoryId
    GROUP BY a.answererId ORDER BY a.answererCategoryId
</sql>

12. 按 IN 列表順序排序

-- MySQL
order by instr(',3,5,1,' , ','+ltrim(moduleId)+',')
-- SQL Server
order by charindex(','+ltrim(moduleId)+',' , ',3,5,1,')

13. resultMap 顯式結果映射

<resultMap class="java.util.HashMap" id="actionStatMap">
    <result column="actionId" property="actionId" jdbcType="BIGINT" javaType="long"/>
    <result column="count" property="count" jdbcType="INT" javaType="int"/>
</resultMap>

對比 resultClass 內聯映射：resultMap 支持指定 jdbcType/javaType，類型轉換更嚴格可控。

14. typeAlias 類型別名

<typeAlias alias="MemberOnlineDuration" type="com.model.MemberOnlineDuration" />

15. remapResults 動態結果集重映射

<select id="testRemap" parameterClass="hashMap" resultClass="hashMap" remapResults="true">
    select userId
    <isEqual property="tag" compareValue="1">, userName</isEqual>
    <isEqual property="tag" compareValue="2">, userPassword</isEqual>
    from UserInfo
</select>

根據參數動態改變 SELECT 列，remapResults="true" 強制每次重新映射結果集元數據，避免緩存首次結果集導致後續查詢報錯。

16. dynamic prepend 屬性機制

<sql id="whereSql">
    <dynamic prepend="where ">
        <isNotNull property="userId" prepend="BOGUS"> userId = #userId# </isNotNull>
        <isNotEmpty property="userName" prepend="and "> userName = #userName# </isNotEmpty>
    </dynamic>
</sql>

dynamic 有 prepend 時，會自動忽略第一個子標簽的 prepend (此處 BOGUS 被忽略)，第二個子標簽的 and 生效。若 dynamic 無 prepend，則不忽略子標簽 prepend。

遷移提示：iBATIS → MyBatis

• 包名：com.ibatis → org.apache.ibatis。
• 動態標簽：<isNotNull> → <if test="prop != null">，<iterate> → <foreach>。
• 參數佔位符：#prop# → #{prop}，$prop$ → ${prop}。
• resultMap 子標簽：<result> 保持，新增 <constructor>、<association>、<collection>。