Mybatis拦截器打印完整SQL
强烈推介IDEA2020.2破解激活,IntelliJ IDEA 注册码,2020.2 IDEA 激活码
之前写过一篇:Mybatis拦截器实现Geometry类型数据存储与查询
主要是关于Mybatis
拦截器的使用。从赞的数量看🤣,可能知道Geometry
数据类型的人不多,这种类型是MySQL
中处理地理数据的数据类型,比如经纬度等。
今天再说一下,如何使用Mybatis
拦截器打印完整SQL
。
MybatisPlus
自带一款SQL
性能分析拦截器:com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor
而这个可以打印部分SQL
,就像下面这样:
可是SQL
里面是有占位符?
的,是不完整的,我们更想的是直接看到完成的SQL
。
如果出现了Bug
,我们查看日志的时候,能直接看到SQL
,而且复制出来直接可以运行,这难道不快乐吗?
package com.ler.manager.interceptor;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import lombok.extern.slf4j.Slf4j;
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.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.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
/**
* @author lww
* @date 2020-09-01 00:13
*/
@Slf4j
@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})})
public class PrintSqlInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameter = null;
if (invocation.getArgs().length > 1) {
parameter = invocation.getArgs()[1];
}
String sqlId = mappedStatement.getId();
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
Configuration configuration = mappedStatement.getConfiguration();
long start = System.currentTimeMillis();
Object returnValue = invocation.proceed();
long time = System.currentTimeMillis() - start;
showSql(configuration, boundSql, time, sqlId);
return returnValue;
}
private static void showSql(Configuration configuration, BoundSql boundSql, long time, String sqlId) {
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
//替换空格、换行、tab缩进等
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (parameterMappings.size() > 0 && parameterObject != null) {
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
} else {
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("\\?", getParameterValue(obj));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", getParameterValue(obj));
}
}
}
}
logs(time, sql, sqlId);
}
private static String getParameterValue(Object obj) {
String value;
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.replace("$", "\\$");
}
private static void logs(long time, String sql, String sqlId) {
StringBuilder sb = new StringBuilder()
.append(" Time:").append(time)
.append(" ms - ID:").append(sqlId)
.append(StringPool.NEWLINE).append("Execute SQL:")
.append(sql).append(StringPool.NEWLINE);
log.info(sb.toString());
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties0) {
}
}
复制代码
intercept
,plugin
,setProperties
这三个方法是实现的接口Interceptor
中的方法。
invocation.getArgs()
的结果,一个是MappedStatement
,一个是参数信息。
showSql
里把?
替换为参数的真实值。
logs
拼装日志。
Mybatis
配置类里配置一下
表结构
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` int(11) DEFAULT NULL,
`location` geometry DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
复制代码
调试接口
@ApiOperation("添加")
@PostMapping(value = "/add", name = "添加")
public HttpResult add() {
User user = new User();
user.setName(123456);
user.setLocation("POINT(121.58623 31.150897)");
user.insert();
return HttpResult.success();
}
复制代码
效果如下:
可以看到显示的已经是完整的SQL,还有执行时间。完美!
欢迎大家关注小编,我们共同学习,一起进步。加油🤣