⁷

Mybatis-plus完整sql打印

归寻

2023-02-20 10:56

阅读999

归寻

2023-02-20 10:56

阅读 999

之前做伙伴匹配系统的时候,在队伍查询哪里遇到了bug,需要显示完整的sql,但是idea上的插件需要付费,在网上搜索之后找到了几种可以免费使用的方法

第一种使用Mybatis拦截器

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.*;

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;

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;

/**

* 拦截StatementHandler类中参数类型为Statement的 prepare 方法

* 即拦截 Statement prepare(Connection var1, Integer var2) 方法

* @author lkx

@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 boundSql

* @param sqlId sqlId

* @return 返回处理的sql

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 原SQL

* @return 返回替换后的sql

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

2023-02-20 10:04:59.003 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : id ==> com.luo.usercenter.mapper.TeamMapper.updateById

2023-02-20 10:04:59.004 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : 类型 ==> UPDATE

2023-02-20 10:04:59.004 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : 拦截的sql ==>: com.luo.usercenter.mapper.TeamMapper.updateById:UPDATE team SET name='testName', description='测试', status=0, password='' WHERE id=34 AND isDelete=0

==> Preparing: UPDATE team SET name=?, description=?, status=?, password=? WHERE id=? AND isDelete=0

2023-02-20 10:04:59.004 INFO 15092 --- [nio-8080-exec-8] c.l.u.c.MybatisSqlLoggerInterceptor : sql耗时 ==>: 0

第二种 P6spy

</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

拦截到的sql

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