前言
项目开发中,sql执行会出现错误及性能缺陷,我们想把sql复制出现到客户端运行一下,先把sql打印到控制台
打印sql配置
方式一: 指定mybatis日志级别
# application.yml
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 等价于application.properties
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
方式二:配置mybatis-config.xml
<configuration>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
</configuration>
方式三:指定整个mapper包下的日志级别
# application.yml
logging:
level:
com.example.demo.mapper: debug
# 等价于application.properties
logging.level.com.example.demo.mapper=debug
但这个打印的sql有个缺陷。
由于占位符,会出现好多?????
,参数少还好,可以复制出来,但参数几十个呢,有没有一种办法,能把完整的sql打印出来呢?
mybatis 拦截器打印完整sql
代码
import java.lang.reflect.Field;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import org.apache.commons.lang.StringUtils;
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.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
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.core.Ordered;
import org.springframework.util.ReflectionUtils;
/**
*
* @ClassName: MybatisSqlPrintIntercepter
* @Description: 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 }) })
public class MybatisSqlPrintIntercepter implements Interceptor, Ordered {
private static final Logger logger = LoggerFactory.getLogger(MybatisSqlPrintIntercepter.class);
private Configuration configuration = null;
private static final ThreadLocal<SimpleDateFormat> DATE_FORMAT_THREAD_LOCAL = new ThreadLocal<SimpleDateFormat>() {
@Override
protected SimpleDateFormat initialValue() {
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
}
};
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget();
long startTime = System.currentTimeMillis();
try {
return invocation.proceed();
} finally {
try {
long endTime = System.currentTimeMillis();
long sqlCost = endTime - startTime;
StatementHandler statementHandler = (StatementHandler) target;
BoundSql boundSql = statementHandler.getBoundSql();
if (configuration == null) {
final DefaultParameterHandler parameterHandler = (DefaultParameterHandler) statementHandler
.getParameterHandler();
Field configurationField = ReflectionUtils.findField(parameterHandler.getClass(), "configuration");
ReflectionUtils.makeAccessible(configurationField);
this.configuration = (Configuration) configurationField.get(parameterHandler);
}
// 替换参数格式化Sql语句,去除换行符
String sql = formatSql(boundSql, configuration).concat(";");
String warning = "";
if(sqlCost > 100) {
warning = "[耗时过长]";
}
if(logger.isDebugEnabled()) {
logger.debug("SQL==> {} 执行耗时:{} ms {}", sql, sqlCost,warning);
}
} catch (Exception e) {
logger.error("==> 打印sql 日志异常 {}",e);
}
}
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
/**
* 获取完整的sql实体的信息
*
* @param boundSql
* @return
*/
private String formatSql(BoundSql boundSql, Configuration configuration) {
String sql = boundSql.getSql();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
Object parameterObject = boundSql.getParameterObject();
// 输入sql字符串空判断
if (StringUtils.isBlank(sql)) {
return "";
}
if (configuration == null) {
return "";
}
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
sql = beautifySql(sql);
// 参考mybatis 源码 DefaultParameterHandler
if (parameterMappings != null) {
for (ParameterMapping parameterMapping : parameterMappings) {
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);
}
String paramValueStr = "";
if (value instanceof String) {
paramValueStr = "'" + value + "'";
} else if (value instanceof Date) {
paramValueStr = "'" + DATE_FORMAT_THREAD_LOCAL.get().format(value) + "'";
} else {
paramValueStr = value + "";
}
sql = sql.replaceFirst("\\?", paramValueStr);
}
}
}
return sql;
}
private String beautifySql(String sql) {
sql = sql.replaceAll("[\\s\n ]+", " ");
return sql;
}
@Override
public int getOrder() {
return Ordered.HIGHEST_PRECEDENCE;
}
}
2.添加拦截器
/**
* 完整sql 打印 拦截器配置
* @return
*/
@Bean
@ConditionalOnExpression("${mybatis.sql.print:true}")
public MybatisSqlPrintIntercepter mybatisSqlPrintIntercepter() {
return new MybatisSqlPrintIntercepter();
}
配置上此拦截器会打印完整sql