SoFunction
Updated on 2025-05-16

How to use MyBatis/MyBatis Plus to implement SQL log printing and execution monitoring

Use MyBatis/MyBatis Plus to implement SQL log printing and execution monitoring

1. Background and value

During development, the complete output of SQL logs is crucial for debugging and performance optimization. MyBatis default log output only displays SQL statements with placeholders, cannot directly see the actual parameter values, and lacks execution time statistics. This article will introduce two implementation solutions:

  • Native configuration solution: Direct output of basic SQL logs through the log framework
  • Enhancement Solution: Use MyBatis interceptor to achieve complete SQL printing and execution monitoring

2. Native configuration solution (quickly get started)

1. Log framework configuration (taking Logback as an example)

<!--  -->
<configuration>
    <logger name="" level="INFO"/>
    <logger name="" level="INFO"/>
    <logger name="" level="DEBUG"/>
    <logger name="" level="DEBUG"/>
</configuration>

2. Output example

DEBUG [main] - ==>  Preparing: SELECT * FROM user WHERE name = ?
DEBUG [main] - ==> Parameters: John(String)

3. Limitations

  • Parameter values ​​are displayed separately, and complete SQL cannot be spliced ​​directly
  • Lack of execution time-consuming statistics
  • Dynamic SQL processing is not intuitive enough

3. Enhancement solution: Custom interceptor implementation

1. SQL beautification and parameter replacement

public class MybatisPlusAllSqlLog implements InnerInterceptor {
    public static final Logger log = ("sys-sql");
    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        logInfo(boundSql, ms, parameter);
    }
    @Override
    public void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
        BoundSql boundSql = (parameter);
        logInfo(boundSql, ms, parameter);
    }
    private static void logInfo(BoundSql boundSql, MappedStatement ms, Object parameter) {
        try {
            ("parameter = " + parameter);
            // Get the id of the node, that is, the id of the sql statement            String sqlId = ();
            ("sqlId = " + sqlId);
            // Get the node configuration            Configuration configuration = ();
            // Get the final sql statement            String sql = getSql(configuration, boundSql, sqlId);
            ("Completesql:{}", sql);
        } catch (Exception e) {
            ("abnormal:{}", (), e);
        }
    }
    // Encapsulate the sql statement, so that the result returns the sql statement node id + sql statement under the full xml path    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId) {
        return sqlId + ":" + showSql(configuration, boundSql);
    }
    // conduct?  Replacement    public static String showSql(Configuration configuration, BoundSql boundSql) {
        // Get parameters        Object parameterObject = ();
        List&lt;ParameterMapping&gt; parameterMappings = ();
        // Multiple spaces in sql statements are replaced by one space instead        String sql = ().replaceAll("[\\s]+", " ");
        if (!(parameterMappings) &amp;&amp; parameterObject != null) {
            // Get the type processor registrant. The function of the type processor is to convert Java type and database type            TypeHandlerRegistry typeHandlerRegistry = ();
            // If the corresponding type can be found according to (), replace            if ((())) {
                sql = ("\\?",
                        (getParameterValue(parameterObject)));
            } else {
                // MetaObject mainly encapsulates originalObject object, provides get and set methods to obtain and set the attribute value of originalObject, and mainly supports operations on three types of objects: JavaBean, Collection, and Map.                MetaObject metaObject = (parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = ();
                    if ((propertyName)) {
                        Object obj = (propertyName);
                        sql = ("\\?",
                                (getParameterValue(obj)));
                    } else if ((propertyName)) {
                        // This branch is dynamic sql                        Object obj = (propertyName);
                        sql = ("\\?",
                                (getParameterValue(obj)));
                    } else {
                        // Print out missing, remind the parameter to be missing and prevent misalignment                        sql = ("\\?", "Missing");
                    }
                }
            }
        }
        return sql;
    }
    // If the parameter is String, add single quotes, if it is a date, convert to a time formatter and add single quotes; deal with cases where the parameters are null and not null    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + () + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = (,
                    , );
            value = "'" + (new Date()) + "'";
        } else {
            if (obj != null) {
                value = ();
            } else {
                value = "";
            }
        }
        return value;
    }
}

2. Execute time-consuming monitoring

@Intercepts({
        @Signature(type = , method = "update", args = {,
                }),
        @Signature(type = , method = "query", args = {,
                , , , , })})
public class SqlStatementInterceptor implements Interceptor {
    public static final Logger log = ("sys-sql");
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = ();
        try {
            return ();
        } finally {
            long timeConsuming = () - startTime;
            ("implementSQL:{}ms", timeConsuming);
            if (timeConsuming &gt; 999 &amp;&amp; timeConsuming &lt; 5000) {
                ("implementSQLGreater than1s:{}ms", timeConsuming);
            } else if (timeConsuming &gt;= 5000 &amp;&amp; timeConsuming &lt; 10000) {
                ("implementSQLGreater than5s:{}ms", timeConsuming);
            } else if (timeConsuming &gt;= 10000) {
                ("implementSQLGreater than10s:{}ms", timeConsuming);
            }
        }
    }
    @Override
    public Object plugin(Object target) {
        return (target, this);
    }
    @Override
    public void setProperties(Properties properties) {
    }
}

After customizing the interceptor, please be careful to configure the interceptor.

3. Output example

INFO  [http-nio-8080-exec-1] - SQLID:
INFO  [http-nio-8080-exec-1] - Complete SQL: SELECT id,name,age FROM user WHERE id=1
INFO  [http-nio-8080-exec-1] - Execution time: 48ms
WARN  [http-nio-8080-exec-1] - Slow SQL Warning: Execution takes 1204ms

4. Summary

By configuring SQL log output rationally, developers can:

  • Quickly locate SQL execution issues
  • Intuitively analyze the actual executed SQL statements
  • Effectively identify performance bottlenecks
  • Improve dynamic SQL debugging efficiency

This is the article about using MyBatisMyBatis Plus to implement SQL log printing and execution monitoring. For more related contents of MyBatisMyBatis Plus SQL log printing, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!