Instantly Spot Problematic SQL with MyBatis Interceptor Coloring

During large‑scale 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 annotates SQL statements with their mapper ID and call stack, allowing one‑click identification of problematic queries.

What is SQL coloring? Before execution, a comment is added to the SQL indicating the corresponding Mapper file entry and the method execution stack, which can be viewed directly in the SGM SQL monitoring console.

The coloring operation adds only 0‑1 ms overhead.

Quick Integration of SQL Coloring

MyBatis application : copy the interceptor source into your project and add it to the MyBatis interceptor configuration, adjusting the package path ( com.your.package ) to match your application.

Non‑MyBatis application : use the AspectJ‑based approach described later to weave the logic into older frameworks.

MyBatis Interceptor Implementation

The interceptor targets the StatementHandler#prepare method, which is invoked before the SQL is sent to the database. By retrieving the BoundSql object, the interceptor modifies the sql field to prepend a comment containing the statement ID and a formatted stack trace.

<code>@Intercepts({@Signature(method="prepare",type=StatementHandler.class,args={Connection.class,Integer.class})})
public class SQLMarkingInterceptor implements Interceptor {
    // ... (omitted for brevity)
    private void marking(StatementHandler delegate) throws Exception {
        BoundSql boundSql = delegate.getBoundSql();
        String sql = boundSql.getSql().trim();
        if (StringUtils.containsIgnoreCase(sql,"select")) {
            MappedStatement ms = getFieldValue(BaseStatementHandler.class, delegate, "mappedStatement", MappedStatement.class);
            String id = ms.getId();
            String trace = trace();
            String comment = String.format(" /* [SQLMarking] {STATEMENT_ID=%s, STACK_TRACE=%s} */ ", id, trace);
            sql = comment + sql;
            Field sqlField = getField(BoundSql.class, "sql");
            sqlField.set(boundSql, sql);
        }
    }
}
</code>

The trace() method builds a concise call‑stack string, filtering out framework and proxy classes.

<code>private String trace() {
    StackTraceElement[] stack = Thread.currentThread().getStackTrace();
    List<String> methods = new ArrayList<>();
    for (int i = stack.length - 1 - DEFAULT_INDEX; i >= DEFAULT_INDEX; i--) {
        String cn = stack[i].getClassName();
        if (!cn.startsWith("com.your.package") || cn.contains("FastClassBySpringCGLIB") || cn.contains("EnhancerBySpringCGLIB") || stack[i].getMethodName().contains("lambda$")) continue;
        if (cn.contains("Interceptor") || cn.contains("Aspect")) continue;
        methods.add(cn.substring(cn.lastIndexOf('.') + 1) + "#" + stack[i].getMethodName());
    }
    return String.join(" ==> ", methods);
}
</code>

AspectJ Weave Implementation (for non‑MyBatis projects)

Using compile‑time weaving, the same coloring logic can be applied to the com.ibatis.sqlmap.engine.execution.SqlExecutor#executeQuery method. Add the AspectJ runtime dependency and configure aspectj-maven-plugin to weave the target JAR.

<code>&lt;dependency&gt;
    &lt;groupId&gt;org.aspectj&lt;/groupId&gt;
    &lt;artifactId&gt;aspectjrt&lt;/artifactId&gt;
    &lt;version&gt;1.8.13&lt;/version&gt;
&lt;/dependency&gt;

&lt;plugin&gt;
    &lt;groupId&gt;org.codehaus.mojo&lt;/groupId&gt;
    &lt;artifactId&gt;aspectj-maven-plugin&lt;/artifactId&gt;
    &lt;version&gt;1.11&lt;/version&gt;
    &lt;configuration&gt;
        &lt;forceAjcCompile&gt;true&lt;/forceAjcCompile&gt;
        &lt;weaveDirectories&gt;
            &lt;weaveDirectory&gt;${project.build.directory}/classes&lt;/weaveDirectory&gt;
        &lt;/weaveDirectories&gt;
        &lt;weaveDependencies&gt;
            &lt;weaveDependency&gt;
                &lt;groupId&gt;org.apache.ibatis&lt;/groupId&gt;
                &lt;artifactId&gt;ibatis-sqlmap&lt;/artifactId&gt;
            &lt;/weaveDependency&gt;
        &lt;/weaveDependencies&gt;
    &lt;/configuration&gt;
    &lt;executions&gt;
        &lt;execution&gt;
            &lt;goals&gt;
                &lt;goal&gt;compile&lt;/goal&gt;
            &lt;/goals&gt;
        &lt;/execution&gt;
    &lt;/executions&gt;
&lt;/plugin&gt;
</code>

The AspectJ advice extracts the SQL string, adds the same comment, and proceeds with the original arguments.

<code>@Aspect
public class SqlExecutorInterceptor {
    @Around("execution(* com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(..))")
    public Object aroundExecuteQuery(ProceedingJoinPoint pjp) throws Throwable {
        Object[] args = pjp.getArgs();
        String sql = (String) args[2];
        if (StringUtils.containsIgnoreCase(sql, "select")) {
            String id = ""; // obtain MappedStatement ID via StatementScope if needed
            String trace = trace();
            String comment = String.format(" /* [SQLMarking] {STATEMENT_ID=%s, STACK_TRACE=%s} */ ", id, trace);
            args[2] = comment + sql;
        }
        return pjp.proceed(args);
    }
}
</code>

Verification

Run mvn clean compile . The console will show AJC messages confirming that the SqlExecutor.executeQuery method is advised by the AspectJ advice. Inspect the compiled SqlExecutor.class to see the woven code.