SpringBoot中扩展Druid的过滤器实现完整的SQL打印

前言

之前通过Mybatis 、Mybatis Plus 的拦截器扩展，实现自定义的Handler，拼接了完整的SQL。
本次使用 Druid 的过滤器来实现这一功能。输出一个完整的sql，并且给出执行的时间。

对Mybatis Plus 拦截器感兴趣的朋友可以移步：https://blog.csdn.net/FBB360JAVA/article/details/132513180

正文

环境说明

基于 Druid 的过滤器，必须先引入 数据库驱动，Druid的依赖。如果你使用的是Mybatis Plus 也需要引入对应的包。

本文基于SpringBoot 3版本，Java 17 ！！！

<!--mysql驱动--><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><version>8.3.0</version><exclusions><exclusion><groupId>com.google.protobuf</groupId><artifactId>protobuf-java</artifactId></exclusion></exclusions></dependency>
<!-- 阿里druid依赖 --><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.22</version></dependency><!-- mybatis-plus整合 --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.6</version><!-- 处理依赖错误，mybatis-spring版本太低 Invalid value type for attribute 'factoryBeanObjectType': java.lang.String--><exclusions><exclusion><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId></exclusion></exclusions></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId><version>3.0.3</version></dependency>

过滤器扩展

package com.pine.common.database.filter;import cn.hutool.core.text.StrPool;
import com.alibaba.druid.DbType;
import com.alibaba.druid.proxy.jdbc.*;
import com.alibaba.druid.sql.SQLUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;import java.util.ArrayList;
import java.util.List;/*** 继承自Slf4jLogFilter的Druid SQL日志过滤器类* 用于自定义SQL日志的输出格式和行为，基于阿里巴巴的Druid数据库连接池* 主要目的是增强日志输出的灵活性和可读性，以及可能的性能优化** @author pine manage* @since 2024-11-01*/
public class DruildSqlSlf4jLogFilter extends com.alibaba.druid.filter.logging.Slf4jLogFilter {private final static Logger logger = LoggerFactory.getLogger(DruildSqlSlf4jLogFilter.class);private static final String PREPARED_STATEMENT_PREFIX = "pstmt-";private final static String CALLABLE_STATEMENT_PREFIX = "cstmt-";private final static String STATEMENT_PREFIX = "stmt-";private final static String CONNECTION_PREFIX = "conn-";public DruildSqlSlf4jLogFilter() {super();setStatementSqlFormatOption(new SQLUtils.FormatOption(false, false));}/*** 当Statement执行出错后调用此方法** @param statement 执行SQL的Statement代理对象* @param sql       执行的SQL语句* @param error     执行过程中捕获的异常*/@SuppressWarnings("PMD")@Overrideprotected void statement_executeErrorAfter(StatementProxy statement, String sql, Throwable error) {if (!isStatementLogErrorEnabled()) {return;}String formattedSql = getFormattedSql(statement, sql);logger.error("[({}{}, {}) executed error.] SQL:{}", CONNECTION_PREFIX, statement.getConnectionProxy().getId(), stmtId(statement), removeBreakingWhitespace(formattedSql), error);}@Overrideprotected void statementPrepareAfter(PreparedStatementProxy statement) {if (isStatementPrepareAfterLogEnabled() && isStatementLogEnabled()) {statementLog("{conn-" + statement.getConnectionProxy().getId() + ", pstmt-" + statement.getId() + "} created. " + removeBreakingWhitespace(statement.getSql()));}}@Overrideprotected void statementCreateAfter(StatementProxy statement) {if (isStatementCreateAfterLogEnabled() && isStatementLogEnabled()) {statementLog("{conn-" + statement.getConnectionProxy().getId() + ", stmt-" + statement.getId() + "} created");}}@Overrideprotected void statementPrepareCallAfter(CallableStatementProxy statement) {if (isStatementPrepareCallAfterLogEnabled() && isStatementLogEnabled()) {statementLog("{conn-" + statement.getConnectionProxy().getId() + ", cstmt-" + statement.getId() + "} created. " + statement.getSql());}}@Overrideprotected void statementExecuteAfter(StatementProxy statement, String sql, boolean firstResult) {logExecutableSql(statement, sql);}@Overrideprotected void statementExecuteBatchAfter(StatementProxy statement, int[] result) {String sql;if (statement instanceof PreparedStatementProxy) {sql = ((PreparedStatementProxy) statement).getSql();} else {sql = statement.getBatchSql();}logExecutableSql(statement, sql);}@Overrideprotected void statementExecuteQueryAfter(StatementProxy statement, String sql, ResultSetProxy resultSet) {logExecutableSql(statement, sql);}@Overrideprotected void statementExecuteUpdateAfter(StatementProxy statement, String sql, int updateCount) {logExecutableSql(statement, sql);}private String buildMessage(String connectionId, String statementPrefix, String statementId, String sql) {return "{" + CONNECTION_PREFIX + connectionId + ", " + statementPrefix + statementId + "} created. SQL:" + sql;}private void logExecutableSql(StatementProxy statement, String sql) {statement.setLastExecuteTimeNano();double nanos = statement.getLastExecuteTimeNano();double millis = nanos / (1000 * 1000);String formattedSql = getFormattedSql(statement, sql);logger.info("[({}{}, {}) executed. cost {} millis.] SQL:{}  ", CONNECTION_PREFIX, statement.getConnectionProxy().getId(), stmtId(statement), millis, removeBreakingWhitespace(formattedSql));}private String stmtId(StatementProxy statement) {StringBuilder buf = new StringBuilder();if (statement instanceof CallableStatementProxy) {buf.append(CALLABLE_STATEMENT_PREFIX);} else if (statement instanceof PreparedStatementProxy) {buf.append(PREPARED_STATEMENT_PREFIX);} else {buf.append(STATEMENT_PREFIX);}buf.append(statement.getId());return buf.toString();}/*** 格式化SQL语句* 此方法旨在将给定的SQL语句进行格式化，以便在日志输出或者调试时更加清晰易读* 它通过移除多余的空格和换行符，同时在关键字和操作符周围保持适当的空格，以达到格式化的目的** @param statement 代理声明对象，用于执行SQL语句的对象，此处未使用，但可能在将来或特定情况下需要* @param sql       待格式化处理的原始SQL字符串* @return 格式化后的SQL字符串*/private String getFormattedSql(StatementProxy statement, String sql) {int parametersSize = statement.getParametersSize();// 当前sql无参数if (parametersSize == 0) {return sql;}List<Object> parameters = new ArrayList<>(parametersSize);for (int i = 0; i < parametersSize; ++i) {JdbcParameter jdbcParam = statement.getParameter(i);parameters.add(jdbcParam != null ? jdbcParam.getValue() : null);}String dbType = statement.getConnectionProxy().getDirectDataSource().getDbType();return SQLUtils.format(sql, DbType.of(dbType), parameters, this.getStatementSqlFormatOption());}/*** 将所有空白符号替换成空格** @param original 原始字符串* @return 转换之后的字符串*/protected String removeBreakingWhitespace(String original) {if (ObjectUtils.isEmpty(original)) {return original;}StringBuilder builder = new StringBuilder(original.length());for (char c : original.toCharArray()) {if (!Character.isWhitespace(c)) {builder.append(c);} else {builder.append(StrPool.C_SPACE);}}return builder.toString();}}

配置数据源和过滤器

在你自己的配置类中进行定义。

@Beanpublic DruildSqlSlf4jLogFilter slf4jLogFilter() {return new DruildSqlSlf4jLogFilter();}@Bean@Primary@ConfigurationProperties("spring.datasource.druid")public DataSource masterDataSource(@Autowired DataSourceProperties dataSourceProperties) {DruidDataSource dataSource = new DruidDataSource();dataSource.setUrl(dataSourceProperties.getUrl());dataSource.setUsername(dataSourceProperties.getUsername());dataSource.setPassword(dataSourceProperties.getPassword());dataSource.setDriverClassName(dataSourceProperties.getDriverClassName());List<Filter> proxyFilters = dataSource.getProxyFilters();proxyFilters.add(slf4jLogFilter());return dataSource;}

数据库配置信息

在你的配置文件中配置如下内容

# 数据库配置
spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/pine_manage?useUnicode=true&serverTimezone=UTC&useServerPrepStmts=true&rewriteBatchedStatements=trueusername: rootpassword: root123456type: com.alibaba.druid.pool.DruidDataSource

打印结果

可以看到连接器ID，statement ID，执行耗时（单位：毫秒），以及填充了参数的sql语句。

 [(conn-10001, pstmt-20002) executed. cost 14.564691 millis.] SQL:select id, name, code, status, remark , create_time, update_time, deleted from sys_dict where deleted = 0 and code = 'desensitized_field_value_in_json'  [(conn-10001, pstmt-20001) executed. cost 11.126154 millis.] SQL:select id, dict_id, name, value, status , sort, remark, create_time, update_time, deleted from sys_dict_item where deleted = 0 and dict_id = 2 order by id desc, sort asc  

有兴趣的朋友可以调整为按配置生效，配置这个sql过滤器是否注入。更加灵活些。