druid定义 java使用_druid 配置打印完整可执行的SQL

druid 是支持 打印完整可以执行SQL的，只是 druid 默认不会打印。这点我觉得做得不够友好。这么好用的功能，居然默认隐藏 ？

spring boot 配置

引入依赖

com.alibaba

druid-spring-boot-starter

1.2.4

配置文件配置

logging.level.druid.sql.Statement=debug

spring.datasource.druid.filter.slf4j.enabled=true

spring.datasource.druid.filter.slf4j.statementPrepareAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementCreateAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementExecuteQueryAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementExecuteAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementParameterSetLogEnabled=false

spring.datasource.druid.filter.slf4j.statementCloseAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementExecuteBatchAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementExecuteUpdateAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementPrepareCallAfterLogEnabled=false

spring.datasource.druid.filter.slf4j.statementExecutableSqlLogEnable=true

spring.datasource.druid.filter.slf4j.statementLogEnabled=true

其实关键是 下面的两个配置

spring.datasource.druid.filter.slf4j.statementExecutableSqlLogEnable=true

spring.datasource.druid.filter.slf4j.statementLogEnabled=true

其他也设置为 false 是因为 会 打印其他 SQL log 太多了。

参考 https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

druid 自定义 配置

引入依赖

com.alibaba

druid

${druid.version}

log2 文件配置

配置类

package com.door.config;

import com.alibaba.druid.filter.Filter;

import com.alibaba.druid.filter.logging.Log4j2Filter;

import com.alibaba.druid.filter.stat.StatFilter;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.support.http.StatViewServlet;

import com.alibaba.druid.support.http.WebStatFilter;

import com.alibaba.druid.wall.WallConfig;

import com.alibaba.druid.wall.WallFilter;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.boot.web.servlet.FilterRegistrationBean;

import org.springframework.boot.web.servlet.ServletRegistrationBean;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

import java.util.ArrayList;

import java.util.List;

/**

* @author Cheng JiYe

* @description:

* @date 2020/9/9 16:51

*/

@Configuration

public class DruidConfiguration {

private Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);

@Value("${spring.datasource.url}")

private String dbUrl;

@Value("${spring.datasource.username}")

private String username;

@Value("${spring.datasource.password}")

private String password;

@Value("${spring.datasource.driver-class-name}")

private String driverClassName;

@Value("${spring.datasource.initialSize}")

private int initialSize;

@Value("${spring.datasource.minIdle}")

private int minIdle;

@Value("${spring.datasource.maxActive}")

private int maxActive;

@Value("${spring.datasource.maxWait}")

private int maxWait;

@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")

private int timeBetweenEvictionRunsMillis;

@Value("${spring.datasource.minEvictableIdleTimeMillis}")

private int minEvictableIdleTimeMillis;

@Value("${spring.datasource.validationQuery}")

private String validationQuery;

@Value("${spring.datasource.testWhileIdle}")

private boolean testWhileIdle;

@Value("${spring.datasource.testOnBorrow}")

private boolean testOnBorrow;

@Value("${spring.datasource.testOnReturn}")

private boolean testOnReturn;

@Value("${spring.datasource.poolPreparedStatements}")

private boolean poolPreparedStatements;

@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")

private int maxPoolPreparedStatementPerConnectionSize;

// @Value("${spring.datasource.filters}")

//private String filters;

// @Value("${spring.datasource.connectionProperties}")

//private String connectionProperties;

@Bean // 声明其为Bean实例

@Primary // 在同样的DataSource中，首先使用被标注的DataSource

public DataSource dataSource() {

DruidDataSource datasource = new DruidDataSource();

datasource.setUrl(this.dbUrl);

datasource.setUsername(this.username);

datasource.setPassword(this.password);

datasource.setDriverClassName(this.driverClassName);

// configuration

datasource.setInitialSize(this.initialSize);

datasource.setMinIdle(this.minIdle);

datasource.setMaxActive(this.maxActive);

datasource.setMaxWait(this.maxWait);

datasource.setTimeBetweenEvictionRunsMillis(this.timeBetweenEvictionRunsMillis);

datasource.setMinEvictableIdleTimeMillis(this.minEvictableIdleTimeMillis);

datasource.setValidationQuery(this.validationQuery);

datasource.setTestWhileIdle(this.testWhileIdle);

datasource.setTestOnBorrow(this.testOnBorrow);

datasource.setTestOnReturn(this.testOnReturn);

datasource.setPoolPreparedStatements(this.poolPreparedStatements);

datasource.setMaxPoolPreparedStatementPerConnectionSize(

this.maxPoolPreparedStatementPerConnectionSize);

// 添加自定义的filter

datasource.setProxyFilters(this.proxyFilters());

// 可以通过一串内容来配置数据源属性，这里不需要了.不要用错了

// datasource.setConnectionProperties(this.connectionProperties);

// #druid recycle Druid的连接回收机制 . 别乱加，一般出问题的时候，调试用

// datasource.setRemoveAbandoned(true);// 超过时间是否回收

// datasource.setRemoveAbandonedTimeout(120); // 秒

// datasource.setLogAbandoned(true);

// 连接时指定编码，防止中文乱码，与特殊字符和表情不能存入数据库

List initSql = new ArrayList<>();

initSql.add("set names utf8mb4;");

datasource.setConnectionInitSqls(initSql);

return datasource;

}

private List proxyFilters() {

List filters = new ArrayList<>(3);

filters.add(this.logFilter());

filters.add(this.statFilter());

filters.add(this.wallFilter());

return filters;

}

/**

* http://localhost:8080/druid/login.html 注册一个StatViewServlet

*

* @return

*/

@Bean

public ServletRegistrationBean DruidStatViewServle() {

// org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.

ServletRegistrationBean servletRegistrationBean =

new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

// 添加初始化参数：initParams

// 白名单：

// servletRegistrationBean.addInitParameter("allow","127.0.0.1");

// IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.

// servletRegistrationBean.addInitParameter("deny","192.168.1.73");

// 登录查看信息的账号密码.

servletRegistrationBean.addInitParameter("loginUsername", "admin");

servletRegistrationBean.addInitParameter("loginPassword", "123456Auth");

// 是否能够重置数据.

servletRegistrationBean.addInitParameter("resetEnable", "false");

return servletRegistrationBean;

}

/**

* 注册一个：filterRegistrationBean

*

* @return

*/

@Bean

public FilterRegistrationBean druidStatFilter() {

FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());

// 添加过滤规则.

filterRegistrationBean.addUrlPatterns("/*");

// 添加不需要忽略的格式信息.

filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,");

return filterRegistrationBean;

}

/**

* 定义 log 过滤

*

* @return

*/

private Log4j2Filter logFilter() {

Log4j2Filter logFilter = new Log4j2Filter();

logFilter.setStatementLogEnabled(true);

// 不要创建后 log

logFilter.setStatementCreateAfterLogEnabled(false);

// 不要 关闭后 log

logFilter.setStatementCloseAfterLogEnabled(false);

// 不要参数 log

logFilter.setStatementParameterClearLogEnable(false);

logFilter.setStatementParameterSetLogEnabled(false);

logFilter.setStatementPrepareAfterLogEnabled(false);

logFilter.setStatementPrepareCallAfterLogEnabled(false);

// 不要执行之后的log

logFilter.setStatementExecuteAfterLogEnabled(false);

logFilter.setStatementExecuteBatchAfterLogEnabled(false);

logFilter.setStatementExecuteQueryAfterLogEnabled(false);

logFilter.setStatementExecuteUpdateAfterLogEnabled(false);

// 可以打印完整的SQL

logFilter.setStatementExecutableSqlLogEnable(true);

return logFilter;

}

/**

* 自定义统计 SQL

*

* @return

*/

private StatFilter statFilter() {

StatFilter statFilter = new StatFilter();

// 统计慢查询

statFilter.setSlowSqlMillis(5000);

statFilter.setLogSlowSql(true);

statFilter.setMergeSql(true);

return statFilter;

}

/**

* sql防火墙过滤器配置

*

* @return

*/

private WallFilter wallFilter() {

WallFilter wallFilter = new WallFilter();

wallFilter.setConfig(this.wallConfig());

wallFilter.setLogViolation(true); // 对被认为是攻击的SQL进行LOG.error输出

wallFilter.setThrowException(false); // 对被认为是攻击的SQL抛出SQLException

return wallFilter;

}

/**

* sql防火墙配置

*

* @return

*/

private WallConfig wallConfig() {

WallConfig wallConfig = new WallConfig();

// wallConfig.setDeleteAllow(false); // 不可以删除全部

return wallConfig;

}

}

关闭 mybatis log 打印

以上 druid 配置好了之后，其实也就不需要 mybtis 配置打印 SQL了

也不需要 使用 啥 mybatis log plugin 等等 此类方式来获取完整的可以执行SQL了

configuration:

map-underscore-to-camel-case: true # 是否开启自动驼峰命名规则(camel case)映射

cache-enabled: false #配置的缓存的全局开关

#配置JdbcTypeForNull

jdbc-type-for-null: 'null'

lazy-loading-enabled: true #延时加载的开关. 默认false

# 不需要mybatis记录 sql log

log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl

参考资料

https://www.bookstack.cn/read/Druid/4e582ac4d22e5709.md

https://www.bookstack.cn/read/Druid/f8a2f69d3df5ead2.md