MyBatis Pitfalls and Best Practices: A Guide for Java Developers

Most developers have used Hibernate or MyBatis and have inevitably encountered various pitfalls.

Common Issues

In MyBatis/Ibatis, the difference between # and $ is crucial: # prevents SQL injection by using prepared statements, while $ does not. Therefore, beginners should prefer #. The #{} syntax is pre‑compiled and safe; ${} is not and can lead to injection.

When you need to assign a literal value without parameter processing, use (${a}) together with @Param(value="a") String a.

For date fields, if the database column type is DATE, set jdbcType=DATE in MyBatis instead of jdbcType=TIMESTAMP.

When using resultMap, the id element must be placed first; otherwise an error occurs.

A frequent "big pit" is java.lang.ArrayIndexOutOfBoundsException caused by missing database permissions for the executing user.

Case 1

A beginner wrote a SQL query joining two tables that both contain Time and Content columns. The DTO fields did not match the column aliases, resulting in null values. Renaming the selected columns to match the DTO property names resolved the issue.

The corrected SQL produced the expected data.

Case 2

When a table uses a composite primary key, reverse‑engineering generates two entity classes. It is better to generate without the primary key, then add it back manually. Also, tinyint should be used for boolean values, not for small integers.

Case 3

Misusing sum() and count() leads to errors. count() (or count(1), count(0)) counts rows regardless of null values, while count(column) counts only non‑null entries. sum(column) aggregates numeric values.

MyBatis treats an empty string for an int parameter as 0. Therefore, before passing values to a mapper, check for empty strings or null and set an appropriate sentinel value.

Timestamp Usage

To set a creation timestamp that never updates: TIMESTAMP DEFAULT CURRENT_TIMESTAMP. To update on each modification:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

.

Case 4

XML escape characters must be used for special symbols: <, >, &, single and double quotes.

Case 5

When selectOne returns null (e.g., no matching row) and the result is assigned to a primitive int, Java auto‑unboxing throws a NullPointerException. Always check for null before unboxing.

Case 6

Multiple Parameters

MyBatis supports several ways to pass multiple parameters:

Object mapping: pass a Java object and reference its fields with #{property}.

Map: use a Map where keys are parameter names.

Annotated parameters: add @Param("name") before each method argument.

Positional parameters: use #{0}, #{1}, etc.

For date range queries, use dynamic SQL such as:

and lbr.update_time > #{startTime}
and lbr.update_time < #{endTime, javaType=Date, jdbcType=TIMESTAMP}

When inserting timestamps, specify {installTime, jdbcType=TIMESTAMP}.

Mapper Overloading

MyBatis mappers cannot be overloaded because each id must be unique. Use a Map as the parameter type and handle overload logic in the service layer.

Avoid Excessive if / choose Tags

Prefer native SQL functions (e.g., DECODE, CASE WHEN) over MyBatis dynamic tags to improve readability, reduce generated SQL size, and avoid hard parsing in Oracle.

SELECT * FROM T_NEWS_TEXT
WHERE PUBLISHTIME >= DECODE(#{startdate},NULL,SYSDATE-7,#{startdate})
  AND PUBLISHTIME <= DECODE(#{enddate},NULL,SYSDATE,#{enddate})

Use XML Comments Instead of SQL Comments

SQL comments inside MyBatis XML can interfere with pagination wrappers and cause runtime errors. Use XML comments ( <!-- comment -->) to keep the generated SQL clean.

Prefer #{} Over ${}

Using ${} can lead to hard parsing in Oracle and potential SQL injection. For LIKE queries, concatenate the wildcard in the parameter: ID LIKE #{ID} || '%'.

Simple MyBatis Usage

MyBatis is lightweight but limited. It excels at separating SQL from Java code, providing mapping tags, and supporting dynamic SQL, yet it lacks advanced features such as cascade operations, sophisticated caching, and easy debugging of complex dynamic statements.

Pros

Easy to learn and adopt.

SQL resides in XML, facilitating centralized management and optimization.

Decouples SQL from application code.

Provides mapping tags for object‑relational mapping.

Supports dynamic SQL generation.

Cons

Large amount of SQL to maintain, especially with many fields or tables.

SQL is database‑specific, reducing portability.

XML id must be unique, preventing method overloading in DAOs.

Mapping tags describe relationships but still rely on correctly written SQL.

DAO layer is simple, requiring extensive object assembly in service code.

No built‑in cascade update/delete.

Debugging complex dynamic SQL is difficult.

Improper caching can produce stale data.

Conclusion

MyBatis’s simplicity is both its strength and weakness. While it reduces boilerplate by handling result‑set mapping, it places the burden of SQL correctness and performance on the developer. For small projects or teams with limited SQL expertise, MyBatis works well; for larger, high‑load systems, consider alternatives such as Hibernate (when used correctly) or Spring JDBC templates.

Java developers’ top 10 mistakes

Optimizing large MySQL pagination queries

System slowdown, CPU 100%, excessive Full GC troubleshooting

Never misuse $ and # in MyBatis!

A thousand reasons to avoid Null