mybatis-patterns

MyBatis Core ORM

Source:https://mybatis.org/mybatis-3/
GitHub：https://github.com/mybatis/mybatis-3

Capability Boundaries

✅ Strong Suits

1. XML vs annotation selection — Use annotations for simple CRUD, XML for complex queries/dynamic SQL
2. ResultMap reuse — extends inheritance, discriminator, columnPrefix for multiple JOINs on the same table
3. Association queries — association (one-to-one)/collection (one-to-many), Join query (recommended) vs nested select (N+1 risk)
4. Dynamic SQL — if/choose/when/otherwise/where/set/foreach/trim/return
5. Injection protection — #{} prepared compilation vs ${} table name/column name (whitelist validation required)
6. Pagination — RowBounds (not recommended) / PageHelper (recommended) physical pagination
7. sql fragment + include — Reuse common SQL fragments

❌ Out of Scope

1. MyBatis-Plus LambdaWrapper → mybatis-plus-patterns
2. Spring Data JPA → spring-data-jpa(existing skill)

The mistakes LLMs make most often

#	Mistake	Correct approach
1	Writing even simple CRUD in XML	@Select("SELECT * FROM user WHERE id=#{id}") Annotations are enough
2	N+1 query: querying child tables in a loop	Join query (recommended) or association/collection + batch select
3	${column} Dynamic sorting without a whitelist	First check whether columnName is in the whitelist, otherwise SQL injection
4	Not configuring resultMap, using alias mapping	Define <resultMap> + <result column="user_name" property="userName"/>
5	SELECT * all fields	Query only the fields you need (especially large fields/TEXT/BLOB)
6	Fuzzy query LIKE '%${keyword}%' SQL injection	LIKE CONCAT('%', #{keyword}, '%') Prepared compilation
7	Multi-table JOIN without handling column name conflicts	Use aliases + resultMap column mapping
8	JOINing different tables with the same table twice, manually writing all columns	Use <association columnPrefix="addr_" to reuse resultMap

Core patterns

Pattern 1: ResultMap reuse + extends

<!-- 基础 ResultMap -->
<resultMap id="BaseResultMap" type="com.example.User">
    <id column="id" property="id"/>
    <result column="user_name" property="userName"/>
    <result column="email" property="email"/>
    <result column="status" property="status"/>
</resultMap>

<!-- 继承 + 关联(推荐 Join 方式) -->
<resultMap id="UserWithDept" extends="BaseResultMap" type="com.example.UserVO">
    <association property="dept" javaType="Department"
        resultMap="com.example.DeptMapper.BaseResultMap"
        columnPrefix="dept_"/>  <!-- ← 同名字段加前缀 -->
    <collection property="roles" ofType="Role"
        resultMap="com.example.RoleMapper.BaseResultMap"
        columnPrefix="role_"/>
</resultMap>

Pattern 2: Join query (recommended, replaces nested select)

<!-- ✅ 推荐：Join 查询 → 一次SQL，无N+1 -->
<select id="selectUserWithDept" resultMap="UserWithDept">
    SELECT u.id, u.user_name, u.email, u.status,
           d.id AS dept_id, d.dept_name AS dept_dept_name,  <!-- ← 加前缀 -->
           r.id AS role_id, r.role_name AS role_role_name
    FROM sys_user u
    LEFT JOIN sys_dept d ON u.dept_id = d.id
    LEFT JOIN sys_user_role ur ON u.id = ur.user_id
    LEFT JOIN sys_role r ON ur.role_id = r.id
    WHERE u.id = #{id}
</select>

<!-- ❌ 避免：嵌套 select 导致 N+1 -->
<resultMap id="UserWithDeptNested" extends="BaseResultMap" type="com.example.UserVO">
    <association property="dept" javaType="Department"
        column="dept_id" select="selectDeptById"/>  <!-- ← 每次查询一个部门 -->
</resultMap>
<!-- 如果查100个用户 → 1(主查询) + 100(部门查询) = 101次SQL -->

Pattern 3: Complete dynamic SQL pattern

<select id="findByCondition" resultMap="BaseResultMap">
    SELECT id, user_name, email, status, create_time
    FROM sys_user
    <where>  <!-- ← 自动处理 AND/OR 前缀 -->
        <if test="username != null and username != ''">
            AND user_name LIKE CONCAT('%', #{username}, '%')  <!-- ✅ 防注入 -->
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="deptId != null">
            AND dept_id = #{deptId}
        </if>
    </where>
    <if test="orderBy != null">
        ORDER BY ${orderBy}  <!-- ⚠️ 必须白名单校验 -->
    </if>
</select>

<!-- 批量插入 -->
<insert id="batchInsert">
    INSERT INTO sys_user (user_name, email, status)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.userName}, #{user.email}, #{user.status})
    </foreach>
</insert>

<!-- 动态更新(set 自动去尾逗号) -->
<update id="updateSelective">
    UPDATE sys_user
    <set>
        <if test="userName != null">user_name = #{userName},</if>
        <if test="email != null">email = #{email},</if>
        <if test="status != null">status = #{status},</if>
    </set>
    WHERE id = #{id}
</update>

<!-- choose/when/otherwise 多分支 -->
<select id="findByCondition" resultMap="BaseResultMap">
    SELECT * FROM sys_user
    <where>
        <choose>
            <when test="queryType == 'name'">AND user_name LIKE CONCAT('%', #{keyword}, '%')</when>
            <when test="queryType == 'email'">AND email = #{keyword}</when>
            <otherwise>AND status = 'ACTIVE'</otherwise>
        </choose>
    </where>
</select>

Pattern 4: SQL fragment reuse

<sql id="BaseColumns">
    id, user_name, email, status, create_time, update_time
</sql>

<select id="selectById" resultMap="BaseResultMap">
    SELECT <include refid="BaseColumns"/>
    FROM sys_user WHERE id = #{id}
</select>

Pattern 5: Annotation approach (simple queries)

@Select("SELECT * FROM sys_user WHERE id = #{id}")
@Results(id = "userMap", value = {
    @Result(column = "user_name", property = "userName"),
    @Result(column = "dept_id", property = "deptId")
})
User findById(Long id);

Pattern 6: Sort field whitelist validation

// ✅ 安全：校验排序字段在白名单中
private static final Set<String> SORT_COLUMNS = Set.of(
    "create_time", "update_time", "user_name", "status"
);

public List<User> findByCondition(String orderBy, boolean asc) {
    if (!SORT_COLUMNS.contains(orderBy)) {
        throw new SecurityException("非法排序字段: " + orderBy);
    }
    // 传入 ${orderBy} 安全使用
}

Gotchas

1. #{} adds quotes and auto-escapes,${} concatenates directly — User input must use #{}
2. ${} used for dynamic table names/column names/sorting — whitelist validation is required, otherwise SQL injection
3. The select attribute of association is lazy loading — each association triggers one query = N+1, use a Join query instead
4. resultType and resultMap cannot be used at the same time — choose one
5. The Mapper interface and XML must be in the same package path — otherwise configure mybatis.mapper-locations
6. Spring Boot enables camel case mapping by default — mybatis.configuration.map-underscore-to-camel-case=true
7. @Select/@Results does not define an @ResultMap ID — cannot be reused in XML
8. resultMap columnPrefix in JOIN queries — the only way to resolve fields with the same name, more concise than writing aliases
9. WHERE 1=1 is not as good as <where> — <where> automatically handling AND/OR prefixes, more elegant
10. <foreach> Note that an empty IN list — An empty list causes a SQL syntax error, so check for empty in advance
11. The principle of PageHelper pagination is ThreadLocal — After pagination, a query must immediately follow; no other operations can occur in between
12. Use MyBatis second-level cache with caution — Cross-session caching causes data inconsistency; do not use it in a distributed environment

Data Privacy

This skill does not collect, store, or transmit any user data.