How a MyBatis Plugin Can Color‑Mark SQL for Instant Business Traceability

Introduction

The article analyzes the pain points backend developers and operations engineers face when trying to pinpoint the business origin of online SQL statements, and presents a lightweight, non‑intrusive MyBatis plugin that adds color‑marking metadata to SQL without altering the original query.

Pain Points

In complex systems a single table may be accessed by dozens or hundreds of different SQL statements. Developers often need to know which business module or method generated a particular SQL, but remembering every statement is impossible, especially as SQL evolves with new joins, conditions, or column changes.

SELECT COUNT(*) FROM st_stock m
INNER JOIN st_lot_shelf_life slsl ON m.tenant_code = slsl.tenant_code
AND m.sku = slsl.sku
AND m.lot_no = slsl.lot_no
AND slsl.deleted = 0
WHERE m.deleted = 0
AND m.stock_qty > 0
AND m.warehouse_no = ?
AND m.lot_no != '-1'
AND m.owner_no IN (?)

Idea

Inspired by the visibility of name‑tags on staff uniforms, the author proposes adding a similar “tag” to SQL statements. By leveraging MyBatis’s plugin mechanism, the plugin can automatically embed identifying information as SQL comments, achieving zero‑intrusion for business code.

Solution

The plugin intercepts StatementHandler and rewrites the SQL to include a comment containing statementId, pFinderId, and any user‑defined context such as the executing thread’s ID. Because the added data is inside a comment, it does not affect execution, fingerprinting, or performance.

Core Code Snippet

The essential modification occurs in the StatementHandler interceptor, where the original SQL is wrapped with a comment like:

/* [SQLMarking] statementId: com.example.dao.MyDao.selectData, pFinderId: 123456.789, operator: user123 */

Thread‑Local Context

The plugin provides a SQLMarkingThreadLocal that allows developers to attach custom key‑value pairs (e.g., operator ID, trace ID) to the SQL comment at runtime.

// other code
SQLMarkingThreadLocal.put("operator", UserInfoUtil.getUserCode());
// other code
SQLMarkingThreadLocal.remove();
// other code

Effect

When the enhanced SQL is logged, the comment makes the originating mapper and method instantly visible, e.g.:

2025-02-11 00:27:19.982 [http-nio-8082-exec-7] DEBUG [pfinderId:4630283.56667.17392048399060130] ...
/* [SQLMarking] statementId: com.jdwl.wms.stock.infrastructure.jdbc.report.dao.StockShelfLifeReportDao.selectStockShelfLifeReport, pFinderId: 4630283.56667.17392048399060130, operator: guozhongqiang5, traceId: 59f48d4d-5346-4ffe-9837-693a090090fc */

Performance impact is minimal—most statements incur 0‑1 ms overhead, with occasional spikes of 3‑4 ms, which is negligible for typical business response times.

Supported Scenarios

MyBatis SQL (SELECT, INSERT, UPDATE, DELETE), including statements without a WHERE clause; compatible with MyBatis‑Plus.

Examples

SELECT:

SELECT COUNT(DISTINCT ito.transfer_order_no) AS qty
FROM inv_transfer_order AS ito
LEFT JOIN inv_transfer_order_detail itd ON ito.warehouse_no = itd.warehouse_no
AND ito.transfer_order_no = itd.transfer_no
AND itd.deleted = 0
WHERE ito.deleted = 0
AND ito.warehouse_no = ?
AND ito.transfer_status IN (?,?,?,?,?,?,?,?)
/* [SQLMarking] statementId: com.example.dao.TransferOrderDao.selectOverstockOrderQty, pFinderId: 4900300.56689.17397685906403801, operator: xxx */

UPDATE:

UPDATE inv_transfer_task_detail
SET task_status = ?, task_user = ?, update_user = ?, update_time = now(), receive_time = now()
WHERE warehouse_no = ?
AND deleted = 0
AND order_detail_id IN (?)
AND task_status IN (?,?,?)
/* [SQLMarking] statementId: com.example.dao.TransferTaskDetailDao.updateStatusAndTaskUserByOrderDetailAndStatus, pFinderId: 4900300.56689.17397685881342999, operator: xxx */

INSERT:

INSERT INTO inv_transfer_task_result (id, result_no, transfer_type, task_type, location_no, container_level_1, container_level_2, container_full, extend_content, warehouse_no, create_user, create_time, update_user, update_time, task_no, tenant_code)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,now(),?,now(),?, 'TC26473419')
/* [SQLMarking] statementId: com.example.dao.TransferTaskResultDao.insert, pFinderId: 4900300.56689.17397685845562352, operator: xxx */

No‑WHERE SQL:

SELECT NOW()
/* [SQLMarking] statementId: com.example.dao.StockQueryDao.dbTime, pFinderId: 2033056.56579.17392526509236705 */

Unsupported Scenarios

SQL executed via raw JDBC, JdbcTemplate, or any ORM that does not use MyBatis.

Use Cases

The plugin aids slow‑SQL analysis, session management, and PFinder‑based tracing by making the business origin of each statement explicit in logs and monitoring tools.

Integration Guide

1. Add Maven Dependency

<dependency>
    <groupId>com.jd.sword</groupId>
    <artifactId>sword-mybatis-plugins</artifactId>
    <version>1.0.2-SNAPSHOT</version>
    <exclusions>
        <exclusion>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </exclusion>
    </exclusions>
</dependency>

2. Register the Plugin in mybatis‑config.xml

<!-- SQLMarking Plugin -->
<plugin interceptor="com.jd.sword.mybatis.plugin.sql.SQLMarkingInterceptor">
    <property name="enabled" value="true"/>
</plugin>

FAQ

Does it support MyBatis‑Plus? Yes, because MyBatis‑Plus builds on MyBatis and the interceptor works the same way.

Must the plugin be the first in the plugins list? No strict order is required; adjust its position if other plugins cause conflicts.

What if I see an error about missing getter for ‘delegate’? This usually indicates a plugin ordering issue; moving the SQLMarking plugin up or down resolves it.

What does the “NoClassDefFoundError RoutingStatementHandlerUtils” mean? It indicates a missing dependency; adding the mybatis-plugins library resolves the problem.

How can I add custom information to the marking? Use SQLMarkingThreadLocal.put(key, value) before the SQL executes and remove it afterwards.