我们在使用springboot + mybatis的时候,会发现官方提供的日志打印是debug级别的,而且不一定符合自己的日志习惯.因此基于mybatis的 Interceptor,可以自己实现一个完整sql打印 + sql执行耗时的小功能.不多说直接上代码
package com.ke.jiaoyi.ordercore.common.interceptor;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.db.sql.SqlUtil;
import com.google.common.collect.Lists;
import com.ke.jiaoyi.ordercore.common.util.Services;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
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.ResultHandler;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Value;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Properties;
/**
* Sql执行时间记录拦截器
*/
@Slf4j
@Intercepts({@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})})
public class SqlCostInterceptor implements Interceptor {
@Value("${sql.format:false}")
private boolean sqlFormat;
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget();
long startTime = System.currentTimeMillis();
StatementHandler statementHandler = (StatementHandler) target;
try {
return invocation.proceed();
} finally {
printSqlLog(startTime, statementHandler);
}
}
/**
* 打印sql 日志
*/
public void printSqlLog(long startTime, StatementHandler statementHandler) {
long endTime = System.currentTimeMillis();
long sqlCost = endTime - startTime;
BoundSql boundSql = statementHandler.getBoundSql();
Object parameterObject = boundSql.getParameterObject();
String sql = boundSql.getSql();
// 格式化Sql语句,去除换行符,替换参数
String formattedSql = null;
try {
formattedSql = getFllSql(boundSql, parameterObject);
} catch (Exception e) {
LOGGER.warn("Failed to format SQL: {}", e.getMessage());
}
if (formattedSql == null) {
formattedSql = sql;
}
LOGGER.info("SQL: [{}] Execution time: [{}ms]", formattedSql, sqlCost);
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
/**
* 获取完整的sql(推荐方案)
*/
public String getFllSql(BoundSql boundSql, Object parameterObject) {
// 从
SqlSessionFactory sqlSessionFactory = Services.of(SqlSessionFactory.class);
TypeHandlerRegistry typeHandlerRegistry = sqlSessionFactory.getConfiguration().getTypeHandlerRegistry();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
if (CollectionUtil.isEmpty(parameterMappings)) {
return null;
}
StringBuilder sqlBuilder = new StringBuilder(boundSql.getSql());
List<Object> paramValues = Lists.newArrayListWithExpectedSize(parameterMappings.size());
for (ParameterMapping parameterMapping : parameterMappings) {
if (parameterMapping.getMode() == ParameterMode.OUT) {
continue;
}
String propertyName = parameterMapping.getProperty();
Object value;
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 = sqlSessionFactory.getConfiguration().newMetaObject(parameterObject);
value = metaObject.getValue(propertyName);
}
paramValues.add(value);
}
for (Object paramValue : paramValues) {
sqlBuilder.replace(sqlBuilder.indexOf("?"), sqlBuilder.indexOf("?") + 1, formatParamValue(paramValue));
}
String sql = sqlBuilder.toString();
if (sqlFormat) {
return SqlUtil.formatSql(sql);
} else {
return sql;
}
}
public String formatParamValue(Object paramValue) {
if (paramValue == null) {
return "null";
} else if (paramValue instanceof String || paramValue instanceof Date) {
return "'" + paramValue.toString() + "'";
} else {
return paramValue.toString();
}
}
}
代码说明,基于mybatis的拦截器,在sql执行完后,打印完整sql语句和耗时.其中Services类是一个Spring applicationContext 的工具类,就不提供了.
SqlUtil是hutool工具包里面的,自行依赖hutool即可.
下面是sql执行效果
2023-03-30 15:21:16.110 INFO 14064 --- [nio-8080-exec-3] c.k.j.o.c.i.SqlCostInterceptor : SQL: [SELECT id,content,created_at,updated_at FROM t_document WHERE id = 404] Execution time: [14ms]