How to Instantly Spot Problematic SQL with a MyBatis Coloring Interceptor

During large promotional events, database load can spike and cause slow responses or outages, making it hard to pinpoint which SQL statement is the bottleneck. This article introduces a "SQL coloring" technique that tags each query with its mapper ID and call stack, allowing one‑click identification of problematic SQL without hunting through multiple code paths.

What Is SQL Coloring?

SQL coloring means adding a comment before the SQL that records the mapper file location and the related method execution stack. The colored information can be seen directly in monitoring tools.

Implementation Approaches

The article provides two ways to achieve SQL coloring: a MyBatis interceptor and an AspectJ weave‑in method. Both implementations are concise, with only a few hundred lines of code, and can be copied directly into a project.

MyBatis Interceptor

The interceptor works by intercepting the StatementHandler.prepare method, retrieving the BoundSql object, and inserting a comment that contains the statement ID and a formatted stack trace. The interceptor avoids interfering with non‑SELECT statements and adds minimal overhead (0‑1 ms).

public class SQLMarkingInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        RoutingStatementHandler handler = getRoutingStatementHandler(invocation.getTarget());
        if (handler != null) {
            StatementHandler delegate = getFieldValue(RoutingStatementHandler.class, handler, "delegate", StatementHandler.class);
            marking(delegate);
        }
        return invocation.proceed();
    }
    // ... helper methods getRoutingStatementHandler, getFieldValue, marking, trace, getField ...
}

The marking method extracts the original SQL, checks if it contains "select", obtains the mapper ID via reflection, builds a map with STATEMENT_ID and STACK_TRACE, formats it as a comment, and rewrites the sql field of BoundSql.

Choosing the Interception Point

Although the interceptor could target Executor, StatementHandler, or ParameterHandler, the article recommends StatementHandler.prepare because the BoundSql object is readily available before the SQL is sent to the database, making it easier to modify.

AspectJ Weave‑In Alternative

For systems that do not use MyBatis, the article shows how to weave a similar interceptor into the com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery method using AspectJ. The advice captures the SQL string, adds the same coloring comment, and proceeds with the original execution.

@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")) {
            // obtain mapper ID and stack trace, build comment
            String marking = "[SQLMarking] {STATEMENT_ID=..., STACK_TRACE=...}";
            args[2] = String.format(" /* %s */ %s", marking, sql);
        }
        return joinPoint.proceed(args);
    }
}

Maven Configuration for AspectJ

To enable compile‑time weaving, add the AspectJ runtime dependency and configure the aspectj-maven-plugin with weaveDependencies pointing to the target library (e.g., ibatis-sqlmap). The plugin also forces Ajc compilation to avoid Lombok conflicts.

<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>
        <weaveDependencies>
            <weaveDependency>
                <groupId>org.apache.ibatis</groupId>
                <artifactId>ibatis-sqlmap</artifactId>
            </weaveDependency>
        </weaveDependencies>
        <showWeaveInfo>true</showWeaveInfo>
    </configuration>
    <executions>
        <execution>
            <goals><goal>compile</goal></goals>
        </execution>
    </executions>
</plugin>

Verification

After running mvn clean compile, the console shows that the SqlExecutor.executeQuery method is advised by the AspectJ advice. The generated SqlExecutor.class in target/classes contains the woven logic.

With the interceptor in place, every SELECT statement executed by MyBatis (or the woven SqlExecutor) will be prefixed with a comment like /* [SQLMarking] {STATEMENT_ID=..., STACK_TRACE=...} */, making it trivial to trace performance issues back to the originating mapper method.