【springboot系】如何打印完整的执行sql语句？

奔跑吧，小子

已于 2025-07-02 10:11:25 修改

阅读量319

点赞数 5

CC 4.0 BY-SA版权

分类专栏：开源框架文章标签： spring boot sql java

于 2025-07-02 10:09:33 首次发布

本文链接：https://blog.csdn.net/run_boy_2022/article/details/148759248

开源框架专栏收录该内容

5 篇文章

订阅专栏

在项目开发阶段，执行sql语句有利于我们直观排查问题以方便解决问题。但是mybatis默认的方式都是?参数，不方便我们，这个就是默认的方式，需要开启配置

logging:
  level:
    com.baomidou.mybatisplus: debug # MP核心包日志
    com.xxx.scct.dao: debug # 你的Mapper包路径
    org.apache.ibatis: trace # MyBatis原生日志

默认打印的sql，效果如下

那有没有让我们可以直接看到完整拼接好的sql语句输出打印呢?

第一种方式：p6spy

pom文件添加依赖

  <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.9.1</version>
        </dependency>

resource目录添加spy.properties

module.log=com.p6spy.engine.logging.P6LogFactory,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,batch,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 真实JDBC driver , 多个以 逗号 分割 默认为空
#driverlist=org.h2.Driver
driverlist=com.mysql.cj.jdbc.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

nacos或项目配置文件配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:mysql://192.168.6.167:3306/scct?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8

logback-spring.xml配置添加

    <logger name="com.p6spy" level="debug" additivity="false">
        <appender-ref ref="console"/>    <!-- 这里根据自己文件定义控制台的appender名称替换 -->
        <appender-ref ref="flatfile"/>  <!-- 这里根据自己文件定义日志文件的appender名称替换 -->
    </logger>

    <!-- 防止太多日志 -->
    <logger name="com.p6spy.engine.logging" level="INFO"/>
    <logger name="com.p6spy.engine.spy" level="INFO"/>

执行业务代码，控制台打印效果如下

第二种方式：log4jdbc

pom文件添加依赖

   <dependency>
            <groupId>com.googlecode.log4jdbc</groupId>
            <artifactId>log4jdbc4</artifactId>
        </dependency>

nacos或项目配置文件配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=net.sf.log4jdbc.DriverSpy
spring.datasource.url=jdbc:log4jdbc:mysql://192.168.6.167:3306/scct?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8

resource目录添加log4jdbc.properties

# 基本配置
log4jdbc.auto.load.popular.drivers=true
log4jdbc.drivers=com.mysql.cj.jdbc.Driver

# 慢查询相关配置
log4jdbc.sqltiming.enable=true
# 在日志中添加执行时间
log4jdbc.sqltiming.addTime=true
# 警告阈值 (毫秒)
log4jdbc.sqltiming.warn.threshold=300
# 错误阈值 (毫秒)
log4jdbc.sqltiming.error.threshold=2000

# SQL 执行时间显示格式 # "executed in XX ms"
log4jdbc.sqltiming.timeFormat=enabled
# 包括参数值
log4jdbc.sqltiming.logParameter=true

# 结果集限制（防止大结果集占用过多内存）
log4jdbc.dump.sql.datalimit=1000

logback-spring.xml配置添加

  <logger name="jdbc.resultset" additivity="false">
        <level value="error"/>
        <appender-ref ref="console"/>
        <appender-ref ref="flatfile"/>
    </logger>

    <logger name="jdbc.connection" additivity="false">
        <level value="error"/>
        <appender-ref ref="console"/>
        <appender-ref ref="flatfile"/>
    </logger>

    <logger name="jdbc.audit" additivity="false">
        <level value="error"/>
        <appender-ref ref="console"/>
        <appender-ref ref="flatfile"/>
    </logger>

    <!-- log SQL with timing information, post execution -->
    <logger name="jdbc.sqlonly" additivity="false">
        <level value="off"/> <!--这里设置off来禁用sqlonly打印通过，不然就会导致sql重复打印-->
        <appender-ref ref="console"/>
        <appender-ref ref="flatfile"/>
    </logger>

   <logger name="jdbc.sqltiming" additivity="false">
        <level value="info"/> <!--生产环境一般设置warn,info这里为了调试-->
        <appender-ref ref="console"/>
        <appender-ref ref="flatfile"/>
    </logger>

查看执行效果

第三种方式：代码编写sql拦截器

相关拦截器代码

@Intercepts({
    @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
public class SqlPrinterInterceptor implements Interceptor {

    private static final Logger logger = LoggerFactory.getLogger("SQL_LOGGER");
    private static final Pattern REPLACE_PATTERN = Pattern.compile("\\?");

    // 配置开关（可通过配置中心动态更新）
    private volatile boolean enabled = true;
    
    // SQL模板缓存（提升性能）
    private final Map<String, String> sqlCache = new ConcurrentHashMap<>(256);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (!enabled || !logger.isDebugEnabled()) {
            return invocation.proceed(); // 修正方法名：proceed()
        }
        
        StatementHandler handler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = handler.getBoundSql();
        MappedStatement mappedStatement = getMappedStatement(handler);
        
        try {
            // 获取原始SQL模板
            String originalSql = boundSql.getSql();
            String cacheKey = mappedStatement.getId() + "@" + originalSql.hashCode();
            String formattedSql = sqlCache.computeIfAbsent(cacheKey, k -> 
                originalSql.replaceAll("[\\s\n]+", " ")
            );
            
            // 获取实际参数值
            Object paramObj = boundSql.getParameterObject();
            
            // 处理批量操作场景
            if (isBatchOperation(boundSql)) {
                handleBatchOperation(invocation, handler, boundSql, formattedSql);
                return invocation.proceed(); // 确保调用继续
            }
            
            // 生成完整SQL
            String completeSql = formatSql(formattedSql, boundSql, getConfiguration(handler));
            logger.debug("执行SQL: {}", completeSql);
        } catch (Exception e) {
            logger.warn("SQL打印失败，不中断操作", e);
        }
        return invocation.proceed();
    }

    private String formatSql(String sql, BoundSql boundSql, Configuration configuration) {
        if (boundSql.getParameterObject() == null) {
            return sql;
        }
        
        List<ParameterMapping> mappings = boundSql.getParameterMappings();
        if (mappings == null || mappings.isEmpty()) {
            return sql;
        }

        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        Object paramObject = boundSql.getParameterObject();
        String preSql = sql;
        
        // 使用更精确的替换方式避免误替换
        StringBuffer result = new StringBuffer();
        Matcher matcher = REPLACE_PATTERN.matcher(sql);
        int index = 0;
        
        while (matcher.find()) {
            String replacement = "?";
            if (index < mappings.size()) {
                ParameterMapping mapping = mappings.get(index++);
                String property = mapping.getProperty();
                Object value = getParamValue(property, paramObject, boundSql, typeHandlerRegistry);
                replacement = formatValue(value);
            }
            matcher.appendReplacement(result, Matcher.quoteReplacement(replacement));
        }
        matcher.appendTail(result);
        
        return result.toString();
    }

    private Object getParamValue(String property, Object paramObject, BoundSql boundSql, 
                                TypeHandlerRegistry registry) {
        if (paramObject == null) {
            return null;
        }
        
        if (registry.hasTypeHandler(paramObject.getClass())) {
            return paramObject;
        }
        
        MetaObject metaObject = SystemMetaObject.forObject(paramObject);
        if (metaObject.hasGetter(property)) {
            return metaObject.getValue(property);
        }
        
        // 处理Map类型的参数
        if (paramObject instanceof Map) {
            return ((Map<?, ?>) paramObject).get(property);
        }
        
        // 处理额外参数
        if (boundSql.hasAdditionalParameter(property)) {
            return boundSql.getAdditionalParameter(property);
        }
        
        return null;
    }

    private String formatValue(Object value) {
        if (value == null) return "NULL";
        
        if (value instanceof String || value instanceof Date) {
            return "'" + value.toString() + "'";
        }
        if (value instanceof Iterable) {
            return formatIterable((Iterable<?>) value);
        }
        return value.toString();
    }

    private String formatIterable(Iterable<?> iterable) {
        StringBuilder sb = new StringBuilder("(");
        Iterator<?> it = iterable.iterator();
        while (it.hasNext()) {
            Object item = it.next();
            sb.append(formatValue(item));
            if (it.hasNext()) sb.append(",");
        }
        return sb.append(")").toString();
    }

    private boolean isBatchOperation(BoundSql boundSql) {
        Object paramObj = boundSql.getParameterObject();
        return paramObj instanceof Collection && boundSql.getSql().toUpperCase().contains("IN"); 
    }

    private void handleBatchOperation(Invocation invocation, StatementHandler handler, 
                                    BoundSql boundSql, String formattedSql) throws Throwable {
        Collection<?> batchParams = (Collection<?>) boundSql.getParameterObject();
        Configuration configuration = getConfiguration(handler);
        
        if (!batchParams.isEmpty()) {
            int batchCount = 0;
            for (Object param : batchParams) {
                String completeSql = formatSql(formattedSql, boundSql, configuration);
                logger.debug("批量操作[{}] SQL: {}", ++batchCount, completeSql);
                // 只打印前3条避免日志过大
                if (batchCount >= 3) break;
            }
            if (batchParams.size() > 3) {
                logger.debug("...省略批量操作中的{}条记录", batchParams.size() - 3);
            }
        }
    }

    // 获取Configuration的正确方式
    private Configuration getConfiguration(StatementHandler handler) {
        MetaObject metaObject = SystemMetaObject.forObject(handler);
        StatementHandler delegate = (StatementHandler) metaObject.getValue("delegate");
        if (delegate != null) {
            metaObject = SystemMetaObject.forObject(delegate);
        }
        return (Configuration) metaObject.getValue("configuration");
    }
    
    private MappedStatement getMappedStatement(StatementHandler handler) {
        MetaObject metaObject = SystemMetaObject.forObject(handler);
        StatementHandler delegate = (StatementHandler) metaObject.getValue("delegate");
        if (delegate != null) {
            metaObject = SystemMetaObject.forObject(delegate);
        }
        return (MappedStatement) metaObject.getValue("mappedStatement");
    }

    // 动态开关
    public void setEnabled(boolean enabled) {
        this.enabled = enabled;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        // 从配置初始化
    }
}

配置拦截器bean注册

@Configuration
public class MyBatisConfig {


    @Bean
    public SqlPrinterInterceptor sqlPrinterInterceptor() {
        SqlPrinterInterceptor interceptor = new SqlPrinterInterceptor();
        interceptor.setEnabled(true); // 默认开启，生产环境建议默认关闭
        return interceptor;
    }


}

开启日志配置

logging:
  level:
    SQL_LOGGER: DEBUG # 控制SQL打印

执行效果