1. 背景
经常会遇到需要根据sql排查问题的情况，而mybatis输出的sql日志通常是这样的
Preparing: SELECT COUNT(*) AS total FROM system_notify_message WHERE deleted = 0 AND (read_status = ? AND user_id = ? AND user_type = ?) AND tenant_id = 1
==> Parameters: false(Boolean), 1(Long), 2(Integer)
<== Total: 1
==> Preparing: INSERT INTO system_users (username, password, nickname, remark, dept_id, post_ids, email, mobile, sex, status, create_time, update_time, creator, updater, tenant_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
==> Parameters: kk123457(String), $2a$04$fFi.Oou.rsQQLCZvfViiZ.FPm9C.B8QKhkO5puEUPfNyJVoMF8LKu(String), kk123457(String), 123(String), 108(Long), [](String), (String), (String), 2(Integer), 0(Integer), 2024-09-30T15:17:58.856(LocalDateTime), 2024-09-30T15:17:58.856(LocalDateTime), 1(String), 1(String)
<== Updates: 1
需要自己手动拼接sql，是件很麻烦，且浪费时间的事情，于是就找到了
2. 解决方案一：p6spy集成日志
- 添加pom依赖，注意该功能需要配合mybatis-plus实现
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.8.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.gavlyukovskiy/p6spy-spring-boot-starter -->
<dependency>
<groupId>com.github.gavlyukovskiy</groupId>
<artifactId>p6spy-spring-boot-starter</artifactId>
<version>1.9.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
- spy.properties配置如下：
#相关的包
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 日志格式
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.p6spy.engine.spy.appender.StdoutLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 开启慢sql
outagedetection=true
# 慢SQL记录标准（单位秒）
outagedetectioninterval=2
sql输出格式
Consume Time：71 ms 2024-09-30 15:51:01
Execute SQL：select * from t_heart_beat_record where project_path = 'D:\code\demo\smart-admin' and server_ip = '127.0.0.1;172.17.47.23;2.0.0.1;172.19.224.1;172.23.0.1' and process_no =6968
但这个有局限，如果是以上都用到，可以直接使用。
3. 解决方案二：sql自定义日志输出
-
自定义拦截器，拦截sql
打印sql，参数，耗时，默认使用了mybatis为orm，只需要一个拦截器就可以实现该功能，具体代码如下：
import org.apache.ibatis.executor.statement.StatementHandler;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import java.lang.reflect.Proxy;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 简易版sql日志输出工具
* 注意：有枚举值需要增加枚举转换
*/
@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})})
@Component
public class SqlLogInterceptor implements Interceptor {
public static final DefaultReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();
private static final Logger log = LoggerFactory.getLogger(SqlLogInterceptor.class);
/**
* 获取配置中需要拦截的表,自定义配置，逗号隔开
*/
@Value("#{'${tmall.sync.tables:}'.split(',')}")
private List<String> tableNames;
/**
* 打印的参数字符串的最大长度
*/
private final static int MAX_PARAM_LENGTH = 50;
/**
* 记录的最大SQL长度
*/
private final static int MAX_SQL_LENGTH = 500;
@Override
public Object intercept(Invocation invocation) throws Throwable {
long startTime = System.currentTimeMillis();
if (CollectionUtils.isEmpty(tableNames)) {
return invocation.proceed();
}
StatementHandler statementHandler = realTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
String sql = boundSql.getSql().replaceAll("\\s+", " ").toLowerCase();
List<ParameterMapping> parameterMappings = new ArrayList<>(boundSql.getParameterMappings());
Object parameterObject = boundSql.getParameterObject();
if (parameterMappings.isEmpty() && parameterObject == null) {
log.warn("parameterMappings is empty or parameterObject is null");
return invocation.proceed();
}
Configuration configuration = mappedStatement.getConfiguration();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
try {
String parameter = "null";
MetaObject newMetaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
if (parameterMapping.getMode() == ParameterMode.OUT) {
continue;
}
String propertyName = parameterMapping.getProperty();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
parameter = getParameterValue(parameterObject);
} else if (newMetaObject.hasGetter(propertyName)) {
parameter = getParameterValue(newMetaObject.getValue(propertyName));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
parameter = getParameterValue(boundSql.getAdditionalParameter(propertyName));
}
// 转义参数值中的 $ 字符
String escapedValue = escapeDollarSign(parameter.toString());
sql = sql.replaceFirst("\\?", escapedValue);
}
// 美化sql
sql = beautifySql(sql);
if (sql.length() > MAX_SQL_LENGTH) {
sql= sql.substring(0, MAX_SQL_LENGTH);
}
long endTime = System.currentTimeMillis();
long timeCount = endTime - startTime;
log.info("耗时{} ms,SQL:{}", timeCount,sql);
// 将拦截到的sql语句插入日志表中
} catch (Exception e) {
log.error(String.format("intercept sql error: [%s]", sql), e);
}
return invocation.proceed();
}
/**
* 解决string替换$匹配问题
* @param value
* @return
*/
private String escapeDollarSign(String value) {
// 转义 $ 字符
return value.replaceAll("\\$", "\\\\\\$");
}
/**
* 获取参数
*
* @param param Object类型参数
* @return 转换之后的参数
*/
private static String getParameterValue(Object param) {
if (param == null) {
return "null";
}
if (param instanceof Number) {
return param.toString();
}
String value = null;
if (param instanceof String) {
value = param.toString();
} else if (param instanceof Date) {
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) param);
// } else if (param instanceof IEnum) {
// value = String.valueOf(((IEnum) param).getValue());
} else {
value = param.toString();
}
String srcStr = String.valueOf(value);
return value instanceof CharSequence ? escapeString(srcStr) : srcStr;
}
@Override
public Object plugin(Object o) {
if (o instanceof StatementHandler) {
return Plugin.wrap(o, this);
}
return o;
}
@Override
public void setProperties(Properties properties) {
}
/**
* 美化sql
* @param sql sql语句
*/
private String beautifySql(String sql) {
sql = sql.replaceAll("[\\s\n ]+", " ");
return sql;
}
public static String escapeString(String escapeStr) {
if (escapeStr.matches("'(.+)'")) {
escapeStr = escapeStr.substring(1, escapeStr.length() - 1);
}
return "'" + escapeRawString(escapeStr) + "'";
}
public static String escapeRawString(String escapeStr) {
int stringLength = escapeStr.length();
if (isEscapeNeededForString(escapeStr, stringLength)) {
StringBuilder buf = new StringBuilder((int)((double)escapeStr.length() * 1.1));
for(int i = 0; i < stringLength; ++i) {
char c = escapeStr.charAt(i);
switch (c) {
case '\u0000':
buf.append('\\');
buf.append('0');
break;
case '\n':
buf.append('\\');
buf.append('n');
break;
case '\r':
buf.append('\\');
buf.append('r');
break;
case '\u001a':
buf.append('\\');
buf.append('Z');
break;
case '"':
buf.append('\\');
buf.append('"');
break;
case '\'':
buf.append('\\');
buf.append('\'');
break;
case '\\':
buf.append('\\');
buf.append('\\');
break;
default:
buf.append(c);
}
}
return buf.toString();
} else {
return escapeStr;
}
}
private static boolean isEscapeNeededForString(String str, int len) {
boolean needsHexEscape = false;
for(int i = 0; i < len; ++i) {
char c = str.charAt(i);
switch (c) {
case '\u0000':
needsHexEscape = true;
break;
case '\n':
needsHexEscape = true;
break;
case '\r':
needsHexEscape = true;
break;
case '\u001a':
needsHexEscape = true;
break;
case '"':
needsHexEscape = true;
break;
case '\'':
needsHexEscape = true;
break;
case '\\':
needsHexEscape = true;
}
if (needsHexEscape) {
break;
}
}
return needsHexEscape;
}
public static <T> T realTarget(Object target) {
if (Proxy.isProxyClass(target.getClass())) {
Plugin plugin = (Plugin)Proxy.getInvocationHandler(target);
MetaObject metaObject = getMetaObject(plugin);
return realTarget(metaObject.getValue("target"));
} else {
return (T) target;
}
}
public static MetaObject getMetaObject(Object object) {
return MetaObject.forObject(object, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);
}
}
输出日志：
2024-09-30 15:17:58.861 | INFO 27772 | http-nio-48080-exec-5 [TID: N/A] c.i.y.server.handler.SqlLogInterceptor | 耗时1 ms,SQL:insert into system_users (username, password, nickname, remark, dept_id, post_ids, email, mobile, sex, status, create_time, update_time, creator, updater, tenant_id) values ('kk123457', '$2a$04$fFi.Oou.rsQQLCZvfViiZ.FPm9C.B8QKhkO5puEUPfNyJVoMF8LKu', 'kk123457', '123', 108, '[]', '', '', 2, 0, '2024-09-30T15:17:58.856', '2024-09-30T15:17:58.856', '1', '1', 1)
本文由博客一文多发平台 OpenWrite 发布！
