前言
我们平常在跑项目的时候，有时候一不留神，写了一个慢sql，导致整个系统变的很慢，但是我们有不知道是哪个sql导致的，这段代码，就能够实现我们想要的功能
既可以统计sql耗时，又可以定位到执行sql的是哪段代码，还可以拿到完整替换过?号的完整sql，直接复制下来，就能够在sql执行器里面执行，然后你就可以通过explain去分析为什么慢了，是不是因为索引导致的等等
还有你们平常用到的idea的mybatis log插件，将？号替换成完整sql也是通过这个逻辑完成的
实现的效果
- 1.时间超过n秒的要打印，n秒通过配置形式
- 2.可配置打印级别info/debug
- 3.Sql打印日志开关，如果为关就不打印sql日志
- 4.日志格式：耗时、sql执行的所在dao层和方法、sql完整语句
默认设置
- 执行时间>=3s，要打印sql
- Info级别
- 默认开关为开，打印sql
- 日志格式：eg:执行sql耗时:5023 ms - id:com.xxxx.xxxx.getXxxxx - Sql:select id,name,age from student
完整代码
package cn.zygxsq.example.common.mybatis; import lombok.extern.slf4j.Slf4j; 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.ParameterMode; 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.beans.factory.annotation.Value; import org.springframework.stereotype.Component; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.regex.Matcher; @Slf4j @Component @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, CacheKey.class, BoundSql.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "queryCursor", args = {MappedStatement.class, Object.class, RowBounds.class}) }) public class ExecutorSqlInterceptor implements Interceptor { /** * 超过该时间打印sql */ @Value("${mybatis.sql.log.time}") private BigDecimal logTime; /** * 日志级别 */ @Value("${mybatis.sql.log.logLevel}") private String logLevel; /** * 日志开关 */ @Value("${mybatis.sql.log.switch}") private String logSwitch; /** * DATE_FORMAT */ private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); /** * 关 */ private static final String SWITCH_FALSE = "false"; @Override public Object intercept(Invocation invocation) throws Throwable { try { if (SWITCH_FALSE.equalsIgnoreCase(logSwitch)){ return invocation.proceed(); } long start = System.currentTimeMillis(); Object result = invocation.proceed(); long end = System.currentTimeMillis(); long timing = end - start; // 打印3s以上的sql语句 BigDecimal timingBigDecimal = new BigDecimal(timing); BigDecimal maxTime = logTime.multiply(new BigDecimal("1000")); if (timingBigDecimal.compareTo(maxTime)>=0) { MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameterObject = null; if (invocation.getArgs().length > 1) { parameterObject = invocation.getArgs()[1]; } String statementId = mappedStatement.getId(); BoundSql boundSql = mappedStatement.getBoundSql(parameterObject); Configuration configuration = mappedStatement.getConfiguration(); String sql = getSql(boundSql, parameterObject, configuration); switch (logLevel){ case "debug": if (log.isDebugEnabled()){ log.debug("执行sql耗时:{} ms - id:{} - Sql:{}", timing, statementId, sql); } break; default: if (log.isInfoEnabled()){ log.info("执行sql耗时:{} ms - id:{} - Sql:{}", timing, statementId, sql); } } } return result; }catch (Exception e){ log.error("拦截sql异常:",e); } return invocation.proceed(); } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } private String getSql(BoundSql boundSql, Object parameterObject, Configuration configuration) { String sql = boundSql.getSql().replaceAll("[\\s]+", " "); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); if (parameterMappings != null) { for (int i = 0; i < parameterMappings.size(); i++) { ParameterMapping parameterMapping = parameterMappings.get(i); if (parameterMapping.getMode() != ParameterMode.OUT) { Object value; String propertyName = parameterMapping.getProperty(); if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (parameterObject == null) { value = null; } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { value = parameterObject; } else { MetaObject metaObject = configuration.newMetaObject(parameterObject); value = metaObject.getValue(propertyName); } sql = replacePlaceholder(sql, value); } } } return sql; } private String replacePlaceholder(String sql, Object propertyValue) { String result; if (propertyValue != null) { if (propertyValue instanceof String) { result = "'" + propertyValue + "'"; } else if (propertyValue instanceof Date) { result = "'" + DATE_FORMAT.format(propertyValue) + "'"; } else { result = propertyValue.toString(); } } else { result = "null"; } return sql.replaceFirst("\\?", Matcher.quoteReplacement(result)); } }
配置文件
######慢sql日志打印 #超过该事件打印，单位s mybatis.sql.log.time=3 #打印级别，info/debug mybatis.sql.log.logLevel=info #是否打印日志开关，true开，false关 mybatis.sql.log.switch=true
代码简要分析
@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, CacheKey.class, BoundSql.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "queryCursor", args = {MappedStatement.class, Object.class, RowBounds.class}) })
@Intercepts注解只有一个属性，即value，其返回值类型是一个@Signature类型的数组，表示我们可以配置多个@Signature注解。
@Signature注解其实就是一个方法签名，其共有三个属性，分别为：
type指接口的class，
method指接口中的方法名，
args指的是方法参数类型(该属性返回值是一个数组)。
顾名思义，上述就是，我要拦截Executor这个类的update、query、queryCursor这个4个方法
大家可以看下Executor这个类里面是不是有这4个方法
update和query大家应该都知道是增删改查，queryCursor主要是执行存储过程用的。
里面的具体方法是做什么的，可以参考一下这篇博客：
