The Mybatis interceptor prints the full SQL

I have written a previous article: Mybatis interceptor to achieve Geometry type data storage and query

This is mainly about the use of Mybatis interceptors. Judging from the number of likes, 🤣, it is possible that not many people know the Geometry data type, which is the data type used to process geographic data in MySQL, such as longitude and latitude.

Today I’ll show you how to print the full SQL using the Mybatis interceptor.

MybatisPlus bring a SQL performance analysis of the interceptor: com. Baomidou. MybatisPlus. The extension. Plugins. PerformanceInterceptor

This can print part of the SQL, like this:

But there are placeholders in SQL? Is incomplete, we would rather see the completed SQL directly.

If there is a Bug, when we look at the log, we can see the SQL directly, and the copy can run directly, wouldn’t it be happy?

Interceptor complete code

package com.ler.manager.interceptor;

import com.baomidou.mybatisplus.core.toolkit.StringPool;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;

/ * * *@author lww
 * @dateThe 2020-09-01 00:13 * /
 @Slf4j
@Intercepts({ @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class PrintSqlInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }
        String sqlId = mappedStatement.getId();
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        Configuration configuration = mappedStatement.getConfiguration();
        long start = System.currentTimeMillis();
        Object returnValue = invocation.proceed();
        long time = System.currentTimeMillis() - start;
        showSql(configuration, boundSql, time, sqlId);
        return returnValue;
    }

    private static void showSql(Configuration configuration, BoundSql boundSql, long time, String sqlId) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        // Replace Spaces, line feeds, TAB indentation, etc
        String sql = boundSql.getSql().replaceAll("[\\s]+"."");
        if (parameterMappings.size() > 0&& parameterObject ! =null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\ \"?", getParameterValue(parameterObject));
            } else {
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\ \"?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\ \"?", getParameterValue(obj));
                    }
                }
            }
        }
        logs(time, sql, sqlId);
    }

    private static String getParameterValue(Object obj) {
        String value;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if(obj ! =null) {
                value = obj.toString();
            } else {
                value = ""; }}return value.replace("$".\ \ "$");
    }

    private static void logs(long time, String sql, String sqlId) {
        StringBuilder sb = new StringBuilder()
                .append("Time.").append(time)
                .append("Ms-id:").append(sqlId)
                .append(StringPool.NEWLINE).append("The Execute SQL.")
                .append(sql).append(StringPool.NEWLINE);
        log.info(sb.toString());
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties0) {}}Copy the code

intercept.plugin.setPropertiesThese three methods are the interfaces to the implementationInterceptorMethod in.

invocation.getArgs()One of the results isMappedStatementOne is parameter information.

showSqlIn the?Replace with the true value of the parameter.
logsAssembly log.
Finally, in theMybatisIn the configuration class

Table structure

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` int(11) DEFAULT NULL,
  `location` geometry DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Copy the code

Debugging interface

@ ApiOperation (" add ")
@postmapping (value = "/add", name = "add")
public HttpResult add(a) {
    User user = new User();
    user.setName(123456);
    user.setLocation("POINT (121.58623, 31.150897)");
    user.insert();
    return HttpResult.success();
}
Copy the code

The effect is as follows:

You can see that the full SQL is displayed, along with the execution time. Perfect!

The last

Welcome everyone to follow my public account, study together, progress together. Refueling 🤣

The Mybatis interceptor prints the full SQL