How to Instantly Spot Problematic SQL with MyBatis Interceptor Coloring

During large‑scale promotional events, traffic spikes can overload the database, causing slow responses or service interruptions, and it becomes difficult to identify which SQL statement is the performance bottleneck. This article proposes an SQL "coloring" method that tags each query with its mapper ID and execution stack, allowing developers to locate problematic SQL at a glance.

What Is SQL Coloring?

SQL coloring means adding a comment annotation to the SQL before execution, marking the corresponding Mapper file entry and the related method call stack. The monitoring console can then display this coloring information directly.

The coloring overhead is negligible, typically 0–1 ms, as shown by the log entries:

[JSF-BZ-22000-366-T-20] INFO c.j.b.t.s.SqlExecutorInterceptor [67] - SQL coloring time: 0ms

Two Implementation Approaches

We provide two ways to implement SQL coloring: a MyBatis interceptor and an AspectJ weaving solution. Both implementations are concise, with only a few hundred lines of code, and can be copied directly into a project.

Quick Integration of SQL Coloring

MyBatis framework integration : Add the interceptor source to the project, configure it in MyBatis, and replace com.your.package with the actual package name.

Non‑MyBatis integration : Use the AspectJ weaving approach to intercept SQL execution in older ibatis‑based applications.

MyBatis Interceptor Implementation

Before diving into code, we explain the interceptor’s principle to deepen understanding of MyBatis. The interceptor works on four core handlers: ParameterHandler, ResultSetHandler, StatementHandler, and Executor. By intercepting the appropriate method, we can modify the SQL before it reaches the database.

Choosing the interception point: ParameterHandler handles parameter setting – not ideal for coloring. Executor may bypass the interceptor in certain cache configurations. StatementHandler#prepare is the most reliable point because the BoundSql object is still accessible.

The interceptor uses the responsibility‑chain pattern ( InterceptorChain) to apply multiple interceptors sequentially.

public class InterceptorChain {
    private final List<Interceptor> interceptors = new ArrayList<>();
    public Object pluginAll(Object target) {
        for (Interceptor interceptor : interceptors) {
            target = interceptor.plugin(target);
        }
        return target;
    }
}

Implementation details:

@Intercepts({
    @Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class, Integer.class})
})
public class SQLMarkingInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        RoutingStatementHandler routing = getRoutingStatementHandler(invocation.getTarget());
        if (routing != null) {
            StatementHandler delegate = getFieldValue(RoutingStatementHandler.class, routing, "delegate", StatementHandler.class);
            marking(delegate);
        }
        return invocation.proceed();
    }
    // ... helper methods getRoutingStatementHandler, getFieldValue, marking, trace ...
}

The marking method retrieves the original SQL from BoundSql, checks if it is a SELECT, obtains the mapper ID, builds a stack‑trace string, and prepends a comment like /* [SQLMarking] {STATEMENT_ID=..., STACK_TRACE=...} */ to the SQL. The comment is injected via reflection.

AspectJ Weaving Implementation

For projects not using MyBatis, we can weave the coloring logic at compile time with AspectJ. Add the following dependencies and plugin configuration to pom.xml:

<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjrt</artifactId>
    <version>1.8.13</version>
</dependency>

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>aspectj-maven-plugin</artifactId>
    <version>1.11</version>
    <configuration>
        <forceAjcCompile>true</forceAjcCompile>
        <weaveDirectories>
            <weaveDirectory>${project.build.directory}/classes</weaveDirectory>
        </weaveDirectories>
        <complianceLevel>1.8</complianceLevel>
        <source>1.8</source>
        <target>1.8</target>
        <showWeaveInfo>true</showWeaveInfo>
        <weaveDependencies>
            <weaveDependency>
                <groupId>org.apache.ibatis</groupId>
                <artifactId>ibatis-sqlmap</artifactId>
            </weaveDependency>
        </weaveDependencies>
    </configuration>
    <executions>
        <execution>
            <goals>
                <goal>compile</goal>
            </goals>
        </execution>
    </executions>
</plugin>

The AspectJ aspect intercepts com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery and injects the same coloring comment:

@Aspect
public class SqlExecutorInterceptor {
    @Around("execution(* com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(..))")
    public Object aroundExecuteQuery(ProceedingJoinPoint joinPoint) throws Throwable {
        Object[] args = joinPoint.getArgs();
        String sql = (String) args[2];
        if (StringUtils.containsIgnoreCase(sql, "select")) {
            String statementId = "";
            // retrieve MappedStatement ID from StatementScope if possible
            String trace = trace();
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            map.put("STATEMENT_ID", statementId);
            map.put("STACK_TRACE", trace);
            String marking = "[SQLMarking] " + map.toString();
            args[2] = String.format(" /* %s */ %s", marking, sql);
        }
        return joinPoint.proceed(args);
    }
    // ... trace() implementation similar to the interceptor version ...
}

After compiling with mvn clean compile, the console shows weaving logs, and the generated SqlExecutor.class contains the woven advice, confirming successful integration.

Scan the QR code to join the technical discussion group.