文章目录
mybatis-plus控制台打印完整带参数SQL语句
问题背景
通常我们开发的时候，需要联合控制台和Navicat/PLSQL等工具进行语句的拼接检查，如果只是输出了一堆？？？，那么将极大降低我们的效率。因此我们需要输出完整的SQL语句以便调试。
解决方案
1.Mybatis拦截器实现统计sql执行时间及打印完整sql语句
代码
import java.sql.Connection;
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;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
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.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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.stereotype.Component;
/**
* 拦截StatementHandler类中参数类型为Statement的 prepare 方法
* 即拦截 Statement prepare(Connection var1, Integer var2) 方法
*/
@Component
@Slf4j
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MybatisSqlLoggerInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;：MetaObject是Mybatis提供的一个用于方便、
//优雅访问对象属性的对象，通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。
MetaObject metaObject = MetaObject
.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
new DefaultReflectorFactory());
//先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
//id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser
String id = mappedStatement.getId();
log.info("id ==> " + id);
//sql语句类型 select、delete、insert、update
String sqlCommandType = mappedStatement.getSqlCommandType().toString();
log.info("类型 ==> " + sqlCommandType);
BoundSql boundSql = statementHandler.getBoundSql();
// 获取节点的配置
Configuration configuration = mappedStatement.getConfiguration();
// 获取到最终的sql语句
String newsql = getSql(configuration, boundSql, id);
log.info("拦截的sql ==>: " + newsql);
long start = System.currentTimeMillis();
Object returnValue = invocation.proceed();
long end = System.currentTimeMillis();
long time = (end - start);
log.info("sql耗时 ==>: " + time);
return returnValue;
//return null;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
/**
* 封装了一下sql语句，
* 使得结果返回完整xml路径下的sql语句节点id + sql语句
*
* @param configuration
* @param boundSql
* @param sqlId
* @return
*/
private String getSql(Configuration configuration, BoundSql boundSql, String sqlId) {
String sql = showSql(configuration, boundSql);
StringBuilder str = new StringBuilder(100);
str.append(sqlId);
str.append(":");
str.append(sql);
return str.toString();
}
/**
* 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号；
* 对参数是null和不是null的情况作了处理<br>
*
* @param obj
* @return
*/
private 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;
}
/**
* 进行？的替换
* @param configuration
* @param boundSql
* @return
*/
public String showSql(Configuration configuration, BoundSql boundSql) {
// 获取参数
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql
.getParameterMappings();
// sql语句中多个空格都用一个空格代替
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) {
// 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换
// 如果根据parameterObject.getClass(）可以找到对应的类型，则替换
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
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;
}
}
打印的sql
c.p.p.a.i.MybatisSqlLoggerInterceptor : 拦截的sql ==>: com.liu.test.admin.mapper.SysDictMapper.selectById:SELECT id,type,description,create_time,update_time,`system`,remarks,del_flag FROM sys_dict WHERE id=3 AND del_flag='0'
2021-07-06 16:00:46.463 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : sql耗时 ==>: 103
2021-07-06 16:00:46.788 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : id ==> com.liu.test.admin.mapper.SysDictMapper.updateById
2021-07-06 16:00:46.790 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : 类型 ==> UPDATE
2021-07-06 16:00:46.793 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : 拦截的sql ==>: com.liu.test.admin.mapper.SysDictMapper.updateById:UPDATE sys_dict SET type='22', description='ssfd55622', create_time=2021-07-06T13:52:55, update_time=2021-07-06T13:52:55, `system`='0', remarks='dsdfd5562247788' WHERE id=3 AND del_flag='0'
2021-07-06 16:00:46.795 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : sql耗时 ==>: 0
2021-07-06 16:00:47.830 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : id ==> com.liu.test.admin.mapper.SysLogMapper.insert
2021-07-06 16:00:47.836 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : 类型 ==> INSERT
2021-07-06 16:00:47.842 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : 拦截的sql ==>: com.liu.test.admin.mapper.SysLogMapper.insert:INSERT INTO sys_log ( type, title, create_by, remote_addr, user_agent, request_uri, method, params, time, service_id ) VALUES ( '0', '修改字典', 'admin', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36', '/dict', 'PUT', '', 1754, 'pig' )
2021-07-06 16:00:47.846 INFO 27608 --- [ XNIO-1 task-1] c.p.p.a.i.MybatisSqlLoggerInterceptor : sql耗时 ==>: 2
2.p6spy 方式
引入依赖
<!-- https://mvnrepository.com/artifact/p6spy/p6spy -->
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.0</version>
</dependency>
修改配置
# 数据源
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
username: root
password: root
url: jdbc:p6spy:mysql://pig-mysql:3307/pig?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
添加配置spy.properties
# SQl语句分析插件配置
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 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
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2
打印的日志
Consume Time：76 ms 2021-07-06 16:50:29
Execute SQL：INSERT INTO sys_dict ( type, description, `system`, remarks ) VALUES ( 'tt', 'tt', '0', 'ttt' )
Consume Time：46 ms 2021-07-06 16:50:30
Execute SQL：INSERT INTO sys_log ( type, title, create_by, remote_addr, user_agent, request_uri, method, params, time, service_id ) VALUES ( '0', '添加字典', 'admin', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36', '/dict', 'POST', '', 3023, 'pig' )
Consume Time：8 ms 2021-07-06 16:50:30
Execute SQL：SELECT COUNT(*) FROM sys_dict WHERE del_flag = '0'
Consume Time：6 ms 2021-07-06 16:50:30
Execute SQL：SELECT id,type,description,create_time,update_time,`system`,remarks,del_flag FROM sys_dict WHERE del_flag='0' LIMIT 20
3.修改MybatisParameterHandler 源码
配置
#mybatis-plus配置控制台打印完整带参数SQL语句
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
重写com.baomidou.mybatisplus.core.MybatisParameterHandler类的****setParameters**** 方法
源码如下
package com.baomidou.mybatisplus.core;
/*
* Copyright (c) 2011-2021, baomidou (jobob@qq.com).
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.core.toolkit.GlobalConfigUtils;
import com.baomidou.mybatisplus.core.toolkit.ReflectionKit;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
/**
* 自定义 ParameterHandler 重装构造函数，填充插入方法主键 ID
*
* @author nieqiuqiu 2020/6/5
* @since 3.4.0
*/
public class MybatisParameterHandler implements ParameterHandler {
private final TypeHandlerRegistry typeHandlerRegistry;
private final MappedStatement mappedStatement;
private final Object parameterObject;
private final BoundSql boundSql;
private final Configuration configuration;
private final SqlCommandType sqlCommandType;
public MybatisParameterHandler(MappedStatement mappedStatement, Object parameter, BoundSql boundSql) {
this.typeHandlerRegistry = mappedStatement.getConfiguration().getTypeHandlerRegistry();
this.mappedStatement = mappedStatement;
this.boundSql = boundSql;
this.configuration = mappedStatement.getConfiguration();
this.sqlCommandType = mappedStatement.getSqlCommandType();
this.parameterObject = processParameter(parameter);
}
public Object processParameter(Object parameter) {
/* 只处理插入或更新操作 */
if (parameter != null
&& (SqlCommandType.INSERT == this.sqlCommandType || SqlCommandType.UPDATE == this.sqlCommandType)) {
//检查 parameterObject
if (ReflectionKit.isPrimitiveOrWrapper(parameter.getClass())
|| parameter.getClass() == String.class) {
return parameter;
}
Collection<Object> parameters = getParameters(parameter);
if (null != parameters) {
// 感觉这里可以稍微优化一下，理论上都是同一个.
parameters.forEach(this::process);
} else {
process(parameter);
}
}
return parameter;
}
@Override
public Object getParameterObject() {
return this.parameterObject;
}
private void process(Object parameter) {
if (parameter != null) {
TableInfo tableInfo = null;
Object entity = parameter;
if (parameter instanceof Map) {
Map<?, ?> map = (Map<?, ?>) parameter;
if (map.containsKey(Constants.ENTITY)) {
Object et = map.get(Constants.ENTITY);
if (et != null) {
entity = et;
tableInfo = TableInfoHelper.getTableInfo(entity.getClass());
}
}
} else {
tableInfo = TableInfoHelper.getTableInfo(parameter.getClass());
}
if (tableInfo != null) {
//到这里就应该转换到实体参数对象了,因为填充和ID处理都是争对实体对象处理的,不用传递原参数对象下去.
MetaObject metaObject = this.configuration.newMetaObject(entity);
if (SqlCommandType.INSERT == this.sqlCommandType) {
populateKeys(tableInfo, metaObject, entity);
insertFill(metaObject, tableInfo);
} else {
updateFill(metaObject, tableInfo);
}
}
}
}
protected void populateKeys(TableInfo tableInfo, MetaObject metaObject, Object entity) {
final IdType idType = tableInfo.getIdType();
final String keyProperty = tableInfo.getKeyProperty();
if (StringUtils.isNotBlank(keyProperty) && null != idType && idType.getKey() >= 3) {
final IdentifierGenerator identifierGenerator = GlobalConfigUtils.getGlobalConfig(this.configuration).getIdentifierGenerator();
Object idValue = metaObject.getValue(keyProperty);
if (StringUtils.checkValNull(idValue)) {
if (idType.getKey() == IdType.ASSIGN_ID.getKey()) {
if (Number.class.isAssignableFrom(tableInfo.getKeyType())) {
metaObject.setValue(keyProperty, identifierGenerator.nextId(entity));
} else {
metaObject.setValue(keyProperty, identifierGenerator.nextId(entity).toString());
}
} else if (idType.getKey() == IdType.ASSIGN_UUID.getKey()) {
metaObject.setValue(keyProperty, identifierGenerator.nextUUID(entity));
}
}
}
}
protected void insertFill(MetaObject metaObject, TableInfo tableInfo) {
GlobalConfigUtils.getMetaObjectHandler(this.configuration).ifPresent(metaObjectHandler -> {
if (metaObjectHandler.openInsertFill() && tableInfo.isWithInsertFill()) {
metaObjectHandler.insertFill(metaObject);
}
});
}
protected void updateFill(MetaObject metaObject, TableInfo tableInfo) {
GlobalConfigUtils.getMetaObjectHandler(this.configuration).ifPresent(metaObjectHandler -> {
if (metaObjectHandler.openUpdateFill() && tableInfo.isWithUpdateFill()) {
metaObjectHandler.updateFill(metaObject);
}
});
}
/**
* 处理正常批量插入逻辑
* <p>
* org.apache.ibatis.session.defaults.DefaultSqlSession$StrictMap 该类方法
* wrapCollection 实现 StrictMap 封装逻辑
* </p>
*
* @return 集合参数
*/
@SuppressWarnings({"rawtypes", "unchecked"})
protected Collection<Object> getParameters(Object parameterObject) {
Collection<Object> parameters = null;
if (parameterObject instanceof Collection) {
parameters = (Collection) parameterObject;
} else if (parameterObject instanceof Map) {
Map parameterMap = (Map) parameterObject;
if (parameterMap.containsKey("collection")) {
parameters = (Collection) parameterMap.get("collection");
} else if (parameterMap.containsKey("list")) {
parameters = (List) parameterMap.get("list");
} else if (parameterMap.containsKey("array")) {
parameters = Arrays.asList((Object[]) parameterMap.get("array"));
}
}
return parameters;
}
@Override
@SuppressWarnings("unchecked")
public void setParameters(PreparedStatement ps) {
ErrorContext.instance().activity("setting parameters").object(this.mappedStatement.getParameterMap().getId());
List<ParameterMapping> parameterMappings = this.boundSql.getParameterMappings();
if (parameterMappings != null) {
String sql = boundSql.getSql();
List<Object> parameters = new ArrayList<>();
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
Object value;
String propertyName = parameterMapping.getProperty();
if (this.boundSql.hasAdditionalParameter(propertyName)) { // issue #448 ask first for additional params
value = this.boundSql.getAdditionalParameter(propertyName);
} else if (this.parameterObject == null) {
value = null;
} else if (this.typeHandlerRegistry.hasTypeHandler(this.parameterObject.getClass())) {
value = parameterObject;
} else {
MetaObject metaObject = this.configuration.newMetaObject(this.parameterObject);
value = metaObject.getValue(propertyName);
}
TypeHandler typeHandler = parameterMapping.getTypeHandler();
JdbcType jdbcType = parameterMapping.getJdbcType();
if (value == null && jdbcType == null) {
jdbcType = this.configuration.getJdbcTypeForNull();
}
try {
typeHandler.setParameter(ps, i + 1, value, jdbcType);
parameters.add(value);
} catch (TypeException | SQLException e) {
throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
}
}
}
try {
sql = handleListParameter(sql, parameters);
System.out.println("输出完整的sql-------------------->>>" + sql);
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 是否基本数据类型或者基本数据类型的包装类
*/
private boolean isPrimitiveOrPrimitiveWrapper(Class<?> parameterObjectClass) {
return parameterObjectClass.isPrimitive() ||
(parameterObjectClass.isAssignableFrom(BigDecimal.class) || parameterObjectClass.isAssignableFrom(Byte.class) || parameterObjectClass.isAssignableFrom(Short.class) ||
parameterObjectClass.isAssignableFrom(Integer.class) || parameterObjectClass.isAssignableFrom(Long.class) ||
parameterObjectClass.isAssignableFrom(Double.class) || parameterObjectClass.isAssignableFrom(Float.class) ||
parameterObjectClass.isAssignableFrom(Character.class) || parameterObjectClass.isAssignableFrom(Boolean.class));
}
/**
* 参数转换拼接替换
* @param sql
* @param col
* @return
*/
private String handleListParameter(String sql, Collection<?> col) {
if (col != null && col.size() != 0) {
for (Object obj : col) {
String value = null;
Class<?> objClass = obj.getClass();
// 类型匹配输出
if (isPrimitiveOrPrimitiveWrapper(objClass)) {
value = obj.toString();
} else if (objClass.isAssignableFrom(String.class)) {
value = "\'" + obj.toString() + "\'";
} else if (objClass.isAssignableFrom(Date.class)) {
try {
value = cn.hutool.core.date.DateUtil.format((Date) obj, "yyyy-MM-dd HH:mm:ss");
value = "to_date('" + value + "','yyyy-mm-dd hh24:mi:ss')";
} catch (Exception e) {
e.printStackTrace();
value = "\'" + obj.toString() + "\'";
}
} else {
//暂定吧
value = "\'" + obj.toString() + "\'";
}
sql = sql.replaceFirst("\\?", value);
}
}
return sql;
}
}
打印日志
输出完整的sql-------------------->>>INSERT INTO sys_dict ( type,
description,
system,
remarks ) VALUES ( ‘rr’,
‘ggg’,
‘0’,
‘rrr’ )
1116
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
