MybatisInterceptor.java
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.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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.regex.Matcher;
/**
* MyBatis拦截器打印不带问号的完整sql语句
* @Classname MybatisInterceptor
* @Description TODO
* @Date 2020/11/17 14:19
* @Created by 陈大宝
*/
@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})})
@SuppressWarnings({"unchecked", "rawtypes"})
public class MybatisInterceptor implements Interceptor {
public static final Logger log = LoggerFactory.getLogger(MybatisInterceptor.class);
@Override
public Object intercept(Invocation invocation)
throws Throwable {
try {
// 获取xml中的一个select/update/insert/delete节点,是一条SQL语句
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameter = null;
// 获取参数,if语句成立,表示sql语句有参数,参数格式是map形式
if (invocation.getArgs().length > 1) {
parameter = invocation.getArgs()[1];
log.info("parameter = " + parameter);
}
// 获取到节点的id,即sql语句的id
String sqlId = mappedStatement.getId();
log.info("sqlId = " + sqlId);
// BoundSql就是封装myBatis最终产生的sql类
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
// 获取节点的配置
Configuration configuration = mappedStatement.getConfiguration();
// 获取到最终的sql语句
String sql = getSql(configuration, boundSql, sqlId);
log.info("完整的sql:{}",sql);
} catch (Exception e) {
log.error("异常:{}",e.getLocalizedMessage(),e);
}
// 执行完上面的任务后,不改变原有的sql执行过程
return invocation.proceed();
}
// 封装了一下sql语句,使得结果返回完整xml路径下的sql语句节点id + sql语句
public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId) {
return sqlId + ":" + showSql(configuration, boundSql);
}
// 如果参数是String,则添加单引号, 如果是日期,则转换为时间格式器并加单引号; 对参数是null和不是null的情况作了处理
private static String getParameterValue(Object obj) {
String value = null;
if (obj instanceof String) {
value = "'" + obj.toString() + "'";
} else if (obj instanceof Date) {
DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT,
DateFormat.DEFAULT, Locale.CHINA);
value = "'" + formatter.format(new Date()) + "'";
} else {
if (obj != null) {
value = obj.toString();
} else {
value = "";
}
}
return value;
}
// 进行?的替换
public static String showSql(Configuration configuration, BoundSql boundSql) {
// 获取参数
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
// sql语句中多个空格都用一个空格代替
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (!CollectionUtils.isEmpty(parameterMappings)&& parameterObject != null) {
// 获取类型处理器注册器,类型处理器的功能是进行java类型和数据库类型的转换
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
// 如果根据parameterObject.getClass()可以找到对应的类型,则替换
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(parameterObject)));
} else {
// MetaObject主要是封装了originalObject对象,提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作
MetaObject metaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(obj)));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
// 该分支是动态sql
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(obj)));
} else {
// 打印出缺失,提醒该参数缺失并防止错位
sql = sql.replaceFirst("\\?", "缺失");
}
}
}
}
return sql;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
配置文件
mybatis-config.xml
开启插件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="cacheEnabled" value="true" /> <!-- 全局映射器启用缓存 -->
<setting name="useGeneratedKeys" value="false" /> <!-- 允许 JDBC 支持自动生成主键 -->
<setting name="defaultExecutorType" value="REUSE" /> <!-- 配置默认的执行器 -->
<setting name="logImpl" value="SLF4J" /> <!-- 指定 MyBatis 所用日志的具体实现 -->
<setting name="mapUnderscoreToCamelCase" value="true"/> <!--驼峰式命名 -->
</settings>
<plugins>
<plugin interceptor="com.config.MybatisInterceptor"/>
</plugins>
</configuration>
测试图:
10:35:06.134|INFO |http-nio-80-exec-27 --- |com.config.MybatisInterceptor|
intercept:63 |
完整的sql:com.system.mapper.PUserMapper.selectPUserList_COUNT:
select id, p_name, p_code, p_password, p_dept_id, p_status,u.create_time,u.remarks,u.nickname,
u.openid,u.headimgurl, d.dept_id, d.parent_id,
d.dept_name, d.order_num, d.leader, d.status
as dept_status from p_user u
left join sys_dept d on u.p_dept_id = d.dept_id
WHERE p_code is not null and p_code != ''
and p_code = 'test' and p_status != '9' ORDER BY u.p_status,u.create_time DESC