Building an Enterprise‑Level MyBatis Persistence Layer from Zero to One

1. Real‑world incident that triggers the discussion

At 02:13 AM an order service alarm spikes: P99 latency jumps from 180 ms to 5.4 s, the DB connection pool is exhausted, MySQL CPU is maxed, and users see endless loading. The root cause is a single "batch query user coupons" SQL that builds an extremely long <foreach> … IN list, runs inside a transaction that also calls remote inventory, marketing, and member services, and finally suffers from master‑slave lag and dirty reads caused by an enabled second‑level cache.

2. What the article aims to solve

Four categories of problems are addressed:

Technical depth: MyBatis core execution chain, first‑ and second‑level cache misuse, plugin interception points, cost of dynamic SQL, batch and streaming queries.

Engineering upgrades: high‑concurrency connection‑pool and transaction governance, read/write splitting, multi‑datasource, sharding, containerized deployment, incident drills.

Production‑grade code: layered design, configuration examples, mapper and XML standards, complete order‑scenario implementation.

Structure: the article follows the flow "principle → architecture → code → tuning → governance → evolution" for easy training or reference.

3. Core MyBatis execution chain

Mapper Proxy
  → SqlSession
    → Executor
      → StatementHandler
        → ParameterHandler
          → JDBC Statement / PreparedStatement
            → ResultSetHandler
              → Java Object

The four pivotal components are SqlSession, Executor, StatementHandler, and ResultSetHandler. Most production plugins (slow‑SQL monitor, audit, pagination) hook into these points.

4. Executor types and batch performance

SIMPLE

: creates a new Statement each time. REUSE: reuses the same Statement. BATCH: buffers update/insert statements and sends them in bulk.

Batching reduces JDBC round‑trips, SQL parsing, and transaction commits, but introduces three side‑effects: delayed exceptions until flushStatements() or commit(), possible OOM of first‑level cache, and larger lock hold time. Therefore a proper batch implementation must split the data, call flushStatements() and clear the cache regularly.

5. First‑level and second‑level cache pitfalls

First‑level cache lives inside a SqlSession and only hits when the same session, SQL, and parameters are used without intervening updates. In Spring this is often misunderstood as "same transaction = same cache" – which is not guaranteed because of proxy and propagation differences.

Second‑level cache is mapper‑scoped and works well only for single‑instance deployments. In distributed environments it leads to stale data, cache‑DB sync issues, and double‑caching with Redis. The recommendation is to disable MyBatis second‑level cache and manage caching explicitly at the service layer (Caffeine + Redis).

6. Plugin mechanism – what it is good for

Slow‑SQL monitoring

SQL audit

Multi‑tenant field filling

Data‑permission rewriting

Parameter/result masking

It is not suitable for complex business logic, distributed transaction orchestration, or aggressive SQL rewriting.

7. Layered architecture for a maintainable persistence layer

API / Controller
  → Application Service
    → Domain Service
      → Repository
        → Mapper
          → MyBatis XML / Annotation SQL
            → DataSource / Sharding / JDBC

Benefits: SQL and business logic are decoupled, migration to sharding or separate databases requires only changes in the repository layer, and unit testing becomes straightforward.

8. Technical stack and Maven dependencies

MyBatis 3.5.x

mybatis‑spring‑boot‑starter 3.0.3

HikariCP (connection pool)

dynamic‑datasource‑spring‑boot3‑starter 4.3.1 (read/write splitting)

ShardingSphere‑JDBC 5.5.1 (sharding)

PageHelper for pagination

Micrometer + Prometheus + Grafana for metrics

OpenTelemetry / SkyWalking for tracing

Kafka for async outbox

9. Sample mapper and XML (production‑grade)

@Mapper
public interface OrderMapper {
    int insert(OrderDO order);
    OrderDO selectByOrderNo(@Param("orderNo") String orderNo);
    List<OrderListItemDO> selectPageByBuyer(OrderQuery query);
    int updateStatusWithVersion(@Param("orderId") Long orderId,
                               @Param("targetStatus") Integer targetStatus,
                               @Param("currentStatus") Integer currentStatus,
                               @Param("version") Integer version,
                               @Param("updatedAt") LocalDateTime updatedAt);
    List<OrderDO> selectByIds(@Param("ids") List<Long> ids);
}

Corresponding XML uses a reusable <sql id="baseColumns"> fragment, explicit resultMap, and safe dynamic conditions with <if test="...">. No select * statements are allowed.

10. High‑concurrency order flow and service implementation

@Service
public class OrderApplicationService {
    private final OrderRepository orderRepository;
    private final OrderItemRepository orderItemRepository;
    private final OutboxRepository outboxRepository;
    private final IdempotencyService idempotencyService;
    private final OrderDomainService orderDomainService;

    @DS("master")
    @Transactional(rollbackFor = Exception.class)
    public String createOrder(CreateOrderCommand cmd) {
        idempotencyService.checkOrThrow(cmd.getRequestNo());
        Order order = orderDomainService.create(cmd);
        List<OrderItem> items = orderDomainService.buildItems(cmd, order.getId());
        orderRepository.save(order);
        orderItemRepository.batchSave(items);
        OrderCreatedEvent event = new OrderCreatedEvent(order.getOrderNo(), order.getBuyerId());
        outboxRepository.save(OutboxMessage.of("order.created", order.getOrderNo(), event));
        idempotencyService.markSuccess(cmd.getRequestNo(), order.getOrderNo());
        return order.getOrderNo();
    }
}

Key ideas: write always goes to the master, the transaction contains only local DB operations, and the outbox table decouples message delivery from the DB commit (Transactional Outbox pattern).

11. Idempotency design

Three layers are recommended: a unique requestNo constraint, a unique orderNo constraint, and a short‑term Redis lock that falls back to the DB unique key.

12. Read/write splitting beyond the @DS annotation

Force recent writes to read from master for a short window.

Provide a configurable "read‑master" switch for critical queries.

Avoid switching data sources inside an active transaction.

13. Sharding strategy and migration timing

Only split when clear signals appear: massive table size, IOPS/CPU bottlenecks, or a stable sharding key. Example: shard by buyer_id (database) and created_at (monthly tables) using ShardingSphere.

14. Batch insert pitfalls and production‑grade implementation

@Service
public class OrderItemBatchRepository {
    private static final int BATCH_SIZE = 500;
    private final SqlSessionFactory sqlSessionFactory;

    public void batchSave(List<OrderItemDO> items) {
        if (items == null || items.isEmpty()) return;
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
            OrderItemMapper mapper = sqlSession.getMapper(OrderItemMapper.class);
            int counter = 0;
            for (OrderItemDO item : items) {
                mapper.insert(item);
                if (++counter % BATCH_SIZE == 0) {
                    sqlSession.flushStatements();
                    sqlSession.clearCache();
                }
            }
            sqlSession.flushStatements();
            sqlSession.commit();
        } catch (Exception ex) {
            throw new IllegalStateException("batch save order items failed", ex);
        }
    }
}

Batch size should be tuned (200‑1000) and each batch must be flushed and cache cleared to avoid OOM and long‑running transactions.

15. Streaming export for large result sets

@Mapper
public interface OrderExportMapper {
    @Options(fetchSize = 1000, resultSetType = ResultSetType.FORWARD_ONLY)
    @Select("""
        SELECT id, order_no, buyer_id, seller_id, status, pay_amount, created_at
        FROM t_order
        WHERE created_at >= #{start} AND created_at < #{end}
        ORDER BY id
    """)
    void scanOrders(@Param("start") LocalDateTime start,
                    @Param("end") LocalDateTime end,
                    ResultHandler<OrderExportRow> handler);
}

The service calls sqlSessionTemplate.select(...) with a custom ResultHandler that writes each row to a file or object storage, avoiding full result loading and long‑running transactions.

16. Slow‑SQL interceptor and metric collection

@Slf4j
@Intercepts({
    @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
@Component
public class SqlMetricsInterceptor implements Interceptor {
    private static final long SLOW_SQL_THRESHOLD_MS = 200L;
    private final MeterRegistry meterRegistry;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        String statementId = ms.getId();
        String command = ms.getSqlCommandType().name();
        long start = System.nanoTime();
        try {
            return invocation.proceed();
        } finally {
            long costMs = TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - start);
            Timer.builder("mybatis.sql.cost")
                 .tag("statementId", statementId)
                 .tag("command", command)
                 .publishPercentileHistogram()
                 .register(meterRegistry)
                 .record(costMs, TimeUnit.MILLISECONDS);
            if (costMs >= SLOW_SQL_THRESHOLD_MS) {
                log.warn("slow sql detected, statementId={}, command={}, costMs={}", statementId, command, costMs);
            }
        }
    }
}

Collects statementId, command type, latency, and can be extended to include traceId, datasource name, and affected rows.

17. Cache design – multi‑level approach

Request → Caffeine (process‑local hot cache)
        → Redis (distributed cache)
        → MyBatis / MySQL

Cache only for stable data (order summary, recent orders, config). Avoid caching rapidly changing state. Handle cache‑penetration (null‑value caching, Bloom filter), cache‑stampede (mutex rebuild, async refresh), and cache‑avalanche (random TTL, staggered expiration).

18. Transaction boundaries and outbox pattern

Bad pattern: a @Transactional method that performs DB writes, remote inventory lock, coupon lock, and message send – leading to long‑held connections and non‑atomic message delivery.

Good pattern: write the business data and an outbox record inside the same DB transaction, then let an asynchronous worker read the outbox table and publish to Kafka. This provides eventual consistency without XA.

19. Real incident fix – massive IN clause for coupons

Original SQL used IN ( … thousands of ids … ), causing long parsing, possible index loss, and connection blockage.

Solution 1 – batch the IDs (e.g., 500 per batch) and issue multiple small queries.

Solution 2 – insert the IDs into a temporary table and join.

Solution 3 – pre‑compute user‑available coupons into a cache or outbox‑style table, eliminating the heavy real‑time query.

20. Observability stack

Metrics: SQL count, avg/P95/P99 latency, slow‑SQL count, connection‑pool active/waiting/timeout, transaction duration, batch success rate, export task duration.

Logging: only statementId and essential parameters, with sensitive data masked; slow‑SQL logged to a separate channel.

Tracing: propagate traceId from HTTP down to mapper statementId, enabling end‑to‑end latency view.

21. Containerization and Kubernetes best practices

Dockerfile uses exec to make Java the PID 1 and sets -XX:+UseContainerSupport -XX:MaxRAMPercentage=75 for proper heap sizing.

K8s ConfigMap stores non‑secret configs (DB URLs, pool sizes); Secret stores passwords and keys.

Readiness probe checks HTTP health and datasource connectivity.

PreStop hook with graceful shutdown waits for in‑flight transactions.

Startup pre‑heat runs lightweight queries and warms hot caches before traffic.

22. Load testing and capacity planning

Test scenarios must cover single order creation, flash‑sale high‑TPS order, buyer pagination, seller search, batch export, and bulk cancel/update. Observe application CPU/GC, Hikari pool stats, MySQL QPS/CPU/buffer‑pool hit rate, master‑slave lag, Redis hit rate, and Kafka lag. Do not rely solely on endpoint latency; also measure SQL execution and transaction times.

23. Production checklist (quick reference)

SQL: no select *, avoid overly long IN lists, ensure indexed pagination.

Transaction: no remote calls, avoid large transactions, enforce idempotent outbox.

Connection pool: parameters tuned by formula (TPS × avg DB hold time × safety factor), leak detection enabled.

Cache: second‑level cache disabled, multi‑level cache design with penetration/stampede/avalanche mitigation.

Observability: SQL latency metrics, slow‑SQL logs, traceId‑statementId correlation, alerts on top‑SQL latency.

24. Final architecture diagram (textual)

User Request → API Gateway → order‑service → Application Service → Repository → MyBatis Mapper → Dynamic DataSource (master/slave, ShardingSphere) → MySQL

Parallel:
  → Redis / Caffeine cache
  → Outbox + Kafka for async writes
  → Micrometer/Prometheus/Grafana for metrics
  → Nacos for config
  → Kubernetes for scaling, gray release, graceful shutdown

The key is clear responsibility separation: MyBatis provides SQL control, the datasource layer handles routing, sharding adds scalability, cache serves hot reads, outbox guarantees eventual consistency, and observability enables rapid root‑cause analysis.

25. Take‑away

MyBatis is not just a CRUD shortcut; it is the backbone of transaction‑critical data access. Mastering its execution chain, cache behavior, batch processing, sharding, and observability turns a simple mapper into a robust, high‑performance, and maintainable enterprise persistence solution.