Chapter 14

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.

⏱ 20 min readLevel: allReference

Naming and layout

ThingConventionExample
Mapper interface<Entity>MapperUserMapper
XML fileSame name, mirrored package under resourcescom/bitsnippets/shop/mapper/UserMapper.xml
NamespaceFully qualified interface namecom.bitsnippets.shop.mapper.UserMapper
Statement idExactly the method namefindByStatus
Finder methodsfindX returns data, countX returns a number, existsX returns booleanfindByEmail, countActive
Writersinsert, update, delete + qualifierupdateSelective
ResultMap id<entity>Map, or <entity>WithXuserMap, userWithOrders
SQL fragment idDescribes the contentuserColumns, tenantPredicate
Column aliasesOne prefix per tableu_id, o_total
recommended structure
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

  1. 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.
  2. One mapper per aggregate, not per table. OrderMapper may own customer_order and order_line because they are never used apart.
  3. 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.
  4. Return domain types or projections, never Map. A Map<String,Object> return type pushes column names into every caller.
  5. Name the SELECT columns. SELECT * breaks silently when a column is added, wastes I/O on wide tables and defeats covering indexes.
  6. Every write returns int and every caller checks it.

Performance checklist

CheckWhyChapter
No nested select on list queriesN+108
Bulk writes use BATCH or multi-row VALUESRound-trip cost06
foreach inputs are chunkedDriver parameter limits07
Keyset pagination for deep pagesOFFSET is O(n)06
fetchSize set for exportsMemory and round trips03
Cursor for very large readsAvoids materialising the list04
Statement timeout configuredOne bad query should not take the service down03
Pool sized and monitoredPending connections mean queueing11
Indexes match your WHERE and ORDER BYMyBatis will happily run a table scan forever—
Second level cache only where it paysStale data and memory pressure09

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. TRACE on 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

logback-spring.xml
<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.pending above 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

  1. From JDBCMove one DAO at a time. The SQL is already written; you are deleting boilerplate, so the diff is almost entirely removals.
  2. From HibernateStart with the read-heavy reporting queries — the ones already written as native SQL. Both can share a data source and a transaction.
  3. From iBATIS 2Namespaces become interface names, SqlMapClient becomes SqlSession, 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.