Best practices and common pitfalls
Everything from the previous thirteen chapters, distilled into rules you can put in a code review checklist — plus the twelve mistakes that account for most MyBatis incidents.
Naming and layout
| Thing | Convention | Example |
|---|---|---|
| Mapper interface | <Entity>Mapper | UserMapper |
| XML file | Same name, mirrored package under resources | com/bitsnippets/shop/mapper/UserMapper.xml |
| Namespace | Fully qualified interface name | com.bitsnippets.shop.mapper.UserMapper |
| Statement id | Exactly the method name | findByStatus |
| Finder methods | findX returns data, countX returns a number, existsX returns boolean | findByEmail, countActive |
| Writers | insert, update, delete + qualifier | updateSelective |
| ResultMap id | <entity>Map, or <entity>WithX | userMap, userWithOrders |
| SQL fragment id | Describes the content | userColumns, tenantPredicate |
| Column aliases | One prefix per table | u_id, o_total |
src/main/java/com/bitsnippets/shop ├── domain/ entities and value objects ├── mapper/ mapper interfaces only — no logic ├── query/ query parameter objects (UserQuery, OrderQuery) ├── service/ transaction boundaries and business rules ├── web/ controllers, request/response DTOs ├── handler/ custom TypeHandlers └── plugin/ interceptors src/main/resources ├── com/bitsnippets/shop/mapper/*.xml └── db/migration/*.sql Flyway
Mapper design rules
- Mappers contain no logic. No default methods that combine calls, no conditionals. If you need orchestration, that is the service's job — and it keeps the mapper trivially testable.
-
One mapper per aggregate, not per table.
OrderMappermay owncustomer_orderandorder_linebecause they are never used apart. -
Take a query object, not eight parameters.
search(UserQuery q)survives the next three feature requests;search(a, b, c, d, e, f, g, h)does not. -
Return domain types or projections, never
Map. AMap<String,Object>return type pushes column names into every caller. -
Name the SELECT columns.
SELECT *breaks silently when a column is added, wastes I/O on wide tables and defeats covering indexes. -
Every write returns
intand every caller checks it.
Performance checklist
| Check | Why | Chapter |
|---|---|---|
No nested select on list queries | N+1 | 08 |
Bulk writes use BATCH or multi-row VALUES | Round-trip cost | 06 |
foreach inputs are chunked | Driver parameter limits | 07 |
| Keyset pagination for deep pages | OFFSET is O(n) | 06 |
fetchSize set for exports | Memory and round trips | 03 |
Cursor for very large reads | Avoids materialising the list | 04 |
| Statement timeout configured | One bad query should not take the service down | 03 |
| Pool sized and monitored | Pending connections mean queueing | 11 |
Indexes match your WHERE and ORDER BY | MyBatis will happily run a table scan forever | — |
| Second level cache only where it pays | Stale data and memory pressure | 09 |
Security
#{}everywhere. The only exception is identifiers, and those need a whitelist in Java.- Never concatenate SQL in a service. If you are building a string, you have chosen the wrong tool — use dynamic SQL.
- Least-privilege database user. The application account rarely needs DDL.
- Do not log parameters in production.
TRACEon a mapper logger prints personal data straight into your log aggregator. - Escape LIKE wildcards from user input, and always add
ESCAPE. - Bound every result set. An endpoint without a maximum page size is a denial-of-service waiting to happen.
- Encrypt at the type handler if a column holds secrets, so no query can forget to do it.
The twelve mistakes
1 · Forgetting session.commit()
The insert logs, no exception is thrown, the row is not there. openSession()
does not autocommit. In Spring, the equivalent mistake is a service method with no
@Transactional and a manual SqlSession.
2 · Nested selects in a list query (N+1)
One query becomes 501. Use a join with a nested <collection>, and assert the
statement count in a test so it cannot come back.
3 · Missing <id> in a nested result map
Twelve User objects with the same id instead of one user with twelve orders.
MyBatis needs the identity column to collapse rows.
4 · ${} with user input
Straightforward SQL injection. Grep your codebase for ${ today; every hit
should be an identifier fed from a whitelist.
5 · SqlSession stored in a field
Sessions are not thread-safe. Symptoms are random and awful: interleaved results, cache corruption, connections that never return to the pool.
6 · LIMIT combined with a collection join
LIMIT counts rows, not parents. Page the ids first, then fetch the graph for
those ids.
7 · Empty collection in foreach
Produces IN (). Guard in Java and return early — an empty filter almost always
means “no results”, not “all results”.
8 · Enabling the second level cache by default
Works beautifully on one instance, serves stale data on three. Leave
cacheEnabled=false until you have measured a reason and picked a distributed
provider.
9 · Mapper XML in src/main/java without a resource include
Maven does not copy it, so you get Invalid bound statement (not found) in the
jar while the IDE is perfectly happy.
10 · Ignoring the affected-row count
An update that matched nothing looks identical to one that succeeded. Optimistic locking, soft deletes and idempotency checks all depend on that number.
11 · OGNL char comparison
test="flag == 'Y'" compares a String to a char and is
silently false. Use "Y".equals(flag) or flag == "Y".toString().
12 · Catching an exception inside @Transactional and continuing
The transaction is already marked rollback-only, so the commit fails with
UnexpectedRollbackException. Use REQUIRES_NEW for genuinely
recoverable sub-operations.
Code review checklist
- ☐ Statement id matches the method name; namespace matches the interface.
- ☐ No
${}outside a validated identifier. - ☐ Columns listed explicitly — no
SELECT *. - ☐ Every joined column aliased with a table prefix.
- ☐
<id>present at every level of a nested result map. - ☐ Dynamic branches all covered by tests, including the all-null case.
- ☐ Collection inputs guarded against empty and chunked when large.
- ☐ Write statements' return values checked.
- ☐ Transaction boundary is in the service, not the mapper or the controller.
- ☐ New queries have supporting indexes; the execution plan was checked.
- ☐ No personal data in log statements enabled in production.
- ☐ Result sets are bounded by a maximum page size.
Observability worth having
<configuration>
<springProfile name="dev,test">
<logger name="com.bitsnippets.shop.mapper" level="DEBUG"/>
</springProfile>
<springProfile name="prod">
<!-- no SQL, no parameters, no personal data -->
<logger name="com.bitsnippets.shop.mapper" level="INFO"/>
<!-- but keep the slow-query plugin at WARN -->
<logger name="com.bitsnippets.shop.plugin.SlowQueryPlugin" level="WARN"/>
</springProfile>
</configuration>
Metrics worth alerting on:
hikaricp.connections.pendingabove 0 for more than a few seconds.- Statement latency p99 per mapper id (from the timing plugin in chapter 12).
- Statements per request — a sudden jump is an N+1 regression.
- Cache hit ratio, if you enabled the second level cache.
Migrating an existing codebase
- From JDBCMove one DAO at a time. The SQL is already written; you are deleting boilerplate, so the diff is almost entirely removals.
- From HibernateStart with the read-heavy reporting queries — the ones already written as native SQL. Both can share a data source and a transaction.
- From iBATIS 2Namespaces become interface names,
SqlMapClientbecomesSqlSession, and<isNotNull>becomes<if test>. Mechanical, but do it with tests in place.
Which single change most reliably prevents an N+1 regression from reaching production?
Caching and lazy loading hide the symptom; a bigger pool lets you fire the extra queries faster. Only an assertion on statement count fails the build when someone reintroduces a nested select.