15 MyBatis Pitfalls and How to Avoid Them

Overview

MyBatis is a powerful Java persistence framework, but overlooking details can easily lead to bugs, security issues, and performance problems. The article compiles the 15 most frequent pitfalls and provides concrete best‑practice remedies.

Pitfall Reference

1. Direct SQL concatenation – vulnerable to injection. Correct: use #{} placeholders.

2. Mismatched DB column and Java property – mapping fails. Correct: define an explicit <resultMap>.

3. Conditional queries written with many if‑else blocks . Correct: use <where> together with <if>.

4. In‑memory pagination . Correct: let the database paginate (e.g., PageHelper).

5. Missing transaction annotation . Correct: annotate with @Transactional.

6. N+1 query problem . Correct: enable lazy loading and map associations properly.

7. Improper handling of enums or special types . Correct: implement a custom TypeHandler.

8. Cache causing data inconsistency . Correct: enable cache selectively.

9. Chaotic multi‑table association mapping . Correct: use <association> / <collection>.

10. Batch operations executing single‑row SQL repeatedly . Correct: use <foreach> for bulk inserts.

11. Multiple mutually exclusive conditions written with many <if> . Correct: use <choose> with <when> / <otherwise>.

12. Manual AND/OR handling in dynamic SQL . Correct: wrap conditions with <trim prefixOverrides="AND |OR ">.

13. Repeatedly writing field mappings . Correct: enable mapUnderscoreToCamelCase globally.

14. Repeating column lists in SQL . Correct: define reusable <sql> fragments.

15. Debugging SQL errors . Correct: turn on MyBatis logging (e.g., STDOUT_LOGGING).

Detailed Usage Examples

1. SQL Injection Protection

<select id="getUser" resultType="User">
  SELECT * FROM users WHERE id = #{id}
</select>

2. Field‑to‑Property Mapping

<resultMap id="userResultMap" type="User">
  <id property="userId" column="user_id"/>
  <result property="userName" column="user_name"/>
</resultMap>

3. Dynamic Conditional Query

<select id="findUsers" resultType="User">
  SELECT * FROM users
  <where>
    <if test="name != null">AND name = #{name}</if>
    <if test="age != null">AND age = #{age}</if>
  </where>
</select>

4. Database‑Level Pagination

PageHelper.startPage(1, 10);
List<User> users = userMapper.selectAll();
PageInfo<User> pageInfo = new PageInfo<>(users);

5. Transaction Management

@Transactional
public void updateUser(User user) {
    userMapper.update(user);
    logMapper.insertLog(user.getId(), "update");
}

6. Avoiding N+1 Queries

<settings>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="aggressiveLazyLoading" value="false"/>
</settings>

7. Custom TypeHandler

public class MyEnumTypeHandler implements TypeHandler<MyEnum> {
    // implement methods
}

8. Reasonable Cache Usage

<select id="getConfig" resultType="Config" useCache="true">
  SELECT * FROM config WHERE id = #{id}
</select>

9. Multi‑Table Association Mapping

<resultMap id="orderResultMap" type="Order">
  <id property="id" column="order_id"/>
  <association property="user" javaType="User">
    <id property="id" column="user_id"/>
    <result property="name" column="user_name"/>
  </association>
</resultMap>

10. Batch Operations

<insert id="insertUsers">
  INSERT INTO users (name, age)
  VALUES
  <foreach collection="list" item="user" separator=",">
    (#{user.name}, #{user.age})
  </foreach>
</insert>

11. Conditional Branching

<choose>
  <when test="id != null">id = #{id}</when>
  <when test="name != null">name = #{name}</when>
  <otherwise>age > 18</otherwise>
</choose>

12. SQL Trimming Optimization

<trim prefixOverrides="AND |OR ">
  <if test="name != null">AND name = #{name}</if>
  <if test="age != null">OR age = #{age}</if>
</trim>

13. Camel‑Case Mapping

<settings>
  <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

14. SQL Fragment Reuse

<sql id="userColumns">id, name, age, email</sql>
<select id="getUser" resultType="User">
  SELECT <include refid="userColumns"/> FROM users WHERE id = #{id}
</select>

15. Logging for Debugging

<settings>
  <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

Conclusion

Avoid using ${} expressions whenever possible.

Leverage global settings and reusable SQL fragments to reduce duplication.

Be intentional about pagination, batch processing, and cache configuration for optimal performance.

Mastering these practices makes your MyBatis code more secure , more efficient , and easier to maintain .