mybatis打印真实SQL的插件

桃扇骨 2022-03-09 09:39 316阅读 0赞

package com.zhanjixun.mybatis.interceptor;
    
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.ibatis.cache.CacheKey;
    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.mapping.SqlCommandType;
    import org.apache.ibatis.plugin.*;
    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;
    import org.springframework.stereotype.Component;
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Properties;
    
    /** * 1.可以用来分析SQL执行效率 * 2.可以用来获取实际执行的SQL * * @author zhanjixun * @time 2018年6月28日 19:20:23 */
    @Slf4j
    @Intercepts({ 
            @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
            @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
            @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class})}
    )
    @Component
    public class SqlInterceptor implements Interceptor { 
    
        private int MIN_SIZE = 0;
    
        @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 startTime = System.currentTimeMillis();
            Object result = null;
            try { 
                result = invocation.proceed();
            } finally { 
                try { 
                    long sqlCostTime = System.currentTimeMillis() - startTime;
                    String sql = getSql(configuration, boundSql);
                    formatSqlLog(mappedStatement.getSqlCommandType(), sqlId, sql, sqlCostTime, result);
                } catch (Exception ignored) { 
    
                }
            }
            return result;
        }
    
        @Override
        public Object plugin(Object target) { 
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) { 
            if (properties == null) { 
                return;
            }
            if (properties.containsKey("minLogSize")) { 
                MIN_SIZE = Integer.valueOf(properties.getProperty("minLogSize"));
            }
        }
    
        private String getSql(Configuration configuration, BoundSql boundSql) { 
            // 输入sql字符串空判断
            String sql = boundSql.getSql();
            if (StringUtils.isBlank(sql)) { 
                return "";
            }
    
            //美化sql
            sql = beautifySql(sql);
    
            //填充占位符, 目前基本不用mybatis存储过程调用,故此处不做考虑
            Object parameterObject = boundSql.getParameterObject();
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            if (!parameterMappings.isEmpty() && parameterObject != null) { 
                TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
                if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { 
                    sql = this.replacePlaceholder(sql, 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 = replacePlaceholder(sql, obj);
                        } else if (boundSql.hasAdditionalParameter(propertyName)) { 
                            Object obj = boundSql.getAdditionalParameter(propertyName);
                            sql = replacePlaceholder(sql, obj);
                        }
                    }
                }
            }
            return sql;
        }
    
        private String beautifySql(String sql) { 
            return sql.replaceAll("[\\s\n ]+", " ");
        }
    
        private String replacePlaceholder(String sql, Object parameterObject) { 
            String result;
            if (parameterObject instanceof String) { 
                result = "'" + parameterObject.toString() + "'";
            } else if (parameterObject instanceof Date) { 
                result = "'" + getDate2String((Date) parameterObject) + "'";
            } else { 
                result = parameterObject.toString();
            }
            return sql.replaceFirst("\\?", result);
        }
    
        private String getDate2String(Date parameterObject) { 
            return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(parameterObject);
        }
    
        private void formatSqlLog(SqlCommandType sqlCommandType, String sqlId, String sql, long costTime, Object obj) { 
            String log = String.format("DAO [%s]\n[%dms] ===> %s\n", sqlId, costTime, sql);
    
            if (sqlCommandType == SqlCommandType.UPDATE || sqlCommandType == SqlCommandType.INSERT || sqlCommandType == SqlCommandType.DELETE) { 
                log += "Count ===> " + obj;
            }
            if (costTime > MIN_SIZE) { 
                SqlInterceptor.log.warn(log);
            }
        }
    }

使用插件可以拼接组装SQL，其实也是在开发过程中使用而已。后面发现IDEA有个插件`mybatis log plugin`可以将IDEA控制台输出的mybatis默认SQL日志组装成为真实执行的SQL。不过上面这个插件还可以分析SQL执行时间，在开发时候调试SQL，做好索引，还是挺有用的。

在`mybatis-config.xml`文件中配置

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <settings>
            
          
            <setting name="logImpl" value="STDOUT_LOGGING" />
        </settings>
    </configuration>