MyBatis拦截器实现打印sql语句执行时间

思路

利用MyBatis拦截器提供的StatementHandler，在执行sql语句的前后拦截，记录开始时间和结束时间，然后利用StatementHandler.getBoundSql方法获取执行的sql语句。

编写MyBatis拦截器

/**
 * StatementHandler是封装JDBC的Statement操作
 * query方法 执行查询语句
 * update方法 调用execute方法执行插入、更新、删除语句
 * batch方法 将SQL命令添加到批处量执行列表中
 */
@Intercepts(value = {
        @Signature(
            type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}
        ),
        @Signature(
                type = StatementHandler.class, method = "update", args = {Statement.class}
        ),
        @Signature(
                type = StatementHandler.class, method = "batch", args = {Statement.class}
        )
})
@Slf4j
@Component
public class PrintSqlInterceptor implements Interceptor {

    @Value("${slow-sql.max-cost-time}")
    private int maxCostTime;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long beginTime = System.currentTimeMillis();
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        try {
            return invocation.proceed();
        } finally {
            long endTime = System.currentTimeMillis();
            long costTime = endTime - beginTime;
            BoundSql boundSql = statementHandler.getBoundSql();
            log.info("慢SQL语句【{}】，执行耗时：{}ms",boundSql.getSql(), costTime);
        }
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Interceptor.super.plugin(target);
        }

        return target;
    }
}

结果：

拦截慢SQL

生产上SQL执行时间标准：
10ms - 20ms 正常
超过100ms 异常
超过500ms 很慢

yml：

slow-sql:
  max-cost-time: 10

拦截器：

@Intercepts(value = @Signature(
        type = StatementHandler.class,
        method = "query",
        args = {Statement.class, ResultHandler.class}
))
@Slf4j
@Component
public class PrintSqlInterceptor implements Interceptor {

    @Value("${slow-sql.max-cost-time}")
    private int maxCostTime;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long beginTime = System.currentTimeMillis();
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        try {
            return invocation.proceed();
        } finally {
            long endTime = System.currentTimeMillis();
            long costTime = endTime - beginTime;
            BoundSql boundSql = statementHandler.getBoundSql();
            if (costTime > maxCostTime) {
                log.info("慢SQL语句【{}】，执行耗时：{}ms",boundSql.getSql(), costTime);
            }
        }
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Interceptor.super.plugin(target);
        }

        return target;
    }
}

结果：