Master MyBatis: Common Pitfalls, Tips, and Best Practices

Common MyBatis Pitfalls and Tips

Most developers have used Hibernate or MyBatis and have inevitably encountered some traps.

Parameter Syntax

In MyBatis, # placeholders are pre‑compiled and protect against SQL injection, while $ inserts raw values and can be unsafe. Prefer #{} for most cases; use ${} only when absolutely necessary.

Date Handling

When a column is of DATE type, set jdbcType=DATE in the mapper; do not use TIMESTAMP unless the column is a timestamp.

ResultMap Ordering

Always place the id element as the first line in a resultMap; otherwise MyBatis will throw an error.

Case 1: DTO Field Mapping

A query that joins two tables with identical column names ( Time and Content) returned null for those fields in the DTO because the column aliases did not match the DTO property names. Renaming the selected columns to exactly match the DTO fields resolved the issue.

Case 2: Composite Primary Keys and Tinyint

When reverse‑engineering tables with composite primary keys, MyBatis may generate two entity classes; removing the primary key during generation and adding it back later avoids conflicts. Also, tinyint columns are mapped to boolean by default; use them only for true/false values.

Case 3: sum() vs count()

count()

, count(1), and count(0) all count rows, even if all columns are NULL. count(column) counts only non‑null values. sum(column) aggregates numeric values.

MyBatis treats an empty string or NULL for integer parameters as 0. To distinguish these states, check for empty strings or NULL before passing the value to the mapper, or convert integers to strings in the service layer.

Timestamp Usage

Define a column as TIMESTAMP DEFAULT CURRENT_TIMESTAMP to set the creation time automatically, and

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

to refresh it on updates.

XML Escaping

When writing XML mapper files, escape special characters: <, >, &, single quotes, and double quotes.

Case 5: selectOne Returning Null

If a mapper method is declared with resultType=Integer and the query finds no rows, MyBatis returns null. Using Java’s auto‑unboxing on the result causes a NullPointerException. Always check for null before unboxing.

Multi‑Parameter Strategies

Object mapping – pass a POJO and reference its fields with #{property}.

Map – use a Map where keys are parameter names.

@Param annotation – name parameters directly in the method signature.

Index access – use #{0}, #{1}, etc.

Time Field Formatting

Return formatted timestamps as strings using MySQL’s date_format function, e.g., date_format(update_time, '%Y-%c-%d %H:%i:%s') AS updatetime, or return them as Timestamp objects.

Mapper Parameters as Map vs Service Overloading

Because MyBatis mappers cannot be overloaded, many developers pass a Map to the mapper. To keep signatures clear, overload methods in the service layer and delegate to a single mapper method, converting the service parameters to a map internally.

Reduce Dynamic if/choose Constructs

Prefer native SQL constructs such as CASE WHEN or DECODE for default values, and move simple conditional logic to the service layer. This improves readability, reduces generated SQL size, and eases caching.

Use XML Comments Instead of SQL Comments

SQL comments inside mapper files can interfere with pagination wrappers and cause parts of the query to be ignored. Replace them with XML comments to keep the generated SQL clean.

Prefer #{} Over ${}

Excessive use of ${} leads to hard parsing in databases like Oracle and can degrade performance. Use #{} whenever possible; for dynamic LIKE patterns, concatenate the wildcard in the parameter, e.g., ID LIKE #{id} || '%'.

Simple MyBatis Usage

MyBatis is lightweight and focuses on mapping SQL in XML to Java objects. It lacks many advanced features of full‑featured ORMs, so avoid embedding complex business logic or heavy dynamic SQL inside mapper files.

Pros and Cons

Advantages

Easy to learn and adopt.

SQL is centralized in XML, simplifying management and optimization.

Decouples SQL from Java code.

Provides mapping tags for object‑relational mapping.

Supports dynamic SQL via XML tags.

Disadvantages

Large amount of SQL to write, especially for many fields or tables.

SQL is database‑specific, reducing portability.

Mapper IDs must be unique, preventing method overloading.

Mapping tags describe relationships but still rely on SQL for actual joins.

DAO layer is thin, requiring extra work to assemble objects.

No built‑in cascade update/delete.

Debugging dynamic SQL is cumbersome.

Missing primary key in queries can cause object overwriting.

Parameter type support is limited (e.g., Date needs @Param).

Multi‑parameter handling is not very convenient.

Improper cache usage can lead to stale data.

Conclusion

MyBatis’s simplicity is both its strength and weakness. It is well‑suited for small projects or teams with limited ORM experience, but for medium to large applications, a more feature‑rich framework or direct JDBC templates may be preferable.