springboot整合mybatis使用druid连接池无法打印sql日志

最新推荐文章于 2023-10-05 15:06:34 发布

小安灬

最新推荐文章于 2023-10-05 15:06:34 发布

阅读量4.1k

点赞数 1

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/qq_17522211/article/details/108355753

版权

java 专栏收录该内容

44 篇文章 1 订阅

订阅专栏

1. properties

*********注意

#mybatis(若druid自定义SqlSessionFactory 则此配置会失效，解决方案在DuridDataSourceFactory将会说明)
#mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

# 最大建立连接等待时间。
druid.maxWait=60000
# Destroy线程会检测连接的间隔时间,检测需要关闭的空闲连接testWhileIdle单位是毫秒 
druid.timeBetweenEvictionRunsMillis=50000
# Destory线程中如果检测到当前连接的最后活跃时间和当前时间的差值大于minEvictableIdleTimeMillis，则关闭当前连接
druid.minEvictableIdleTimeMillis=120000
# 申请连接的时候检测，如果空闲时间大于 timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。建议配置为true，不影响性能，并且保证安全性。
druid.testWhileIdle=true
druid.validationQuery=SELECT 'x'
# 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
druid.testOnBorrow=false
# 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
druid.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小 
druid.poolPreparedStatements=false
druid.maxPoolPreparedStatementPerConnectionSize=-1
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙（防止SQL注入）
druid.filters=stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000
# 合并多个DruidDataSource的监控数据
druid.useGlobalDataSourceStat=true

2. DruidDataSourceConfig(读取properties配置信息类)

import lombok.*;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import java.io.Serializable;

@Configuration
@ConfigurationProperties(prefix = "druid")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class DruidDataSourceConfig implements Serializable{
	public String name;
	public String url;
	public String username;
	public String password;
	public String type;
	public String driverClassName;
	public String filters;
	public int maxActive;
	public int initialSize;
	public int maxWait;
	public int minIdle;
	public int timeBetweenEvictionRunsMillis;
	public int minEvictableIdleTimeMillis;
	public boolean testWhileIdle;
	public String validationQuery;
	public boolean testOnBorrow;
	public boolean testOnReturn;
	public boolean poolPreparedStatements;
	public int maxPoolPreparedStatementPerConnectionSize;
	public int maxOpenPreparedStatements;
	public String connectionProperties;
	public boolean useGlobalDataSourceStat;
}

3. DuridDataSourceFactory(配置druid类)

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collections;
import java.util.StringTokenizer;

@Slf4j
@Configuration
// 配置扫描包
@MapperScan(basePackages = {DuridDataSourceFactory.BASE_PACKAGE}, sqlSessionFactoryRef = DuridDataSourceFactory.SQL_SESSION_FACTORY)
public class DuridDataSourceFactory {
    /**
     * 配置文件
     */
    @Autowired
    private DruidDataSourceConfig druidDataSourceConfig;
    @Autowired
    private MybatisProperties mybatisProperties;
    /***
     * 数据源bean的名称
     */
    private final static String DATASOURCE = "dataSource";
    /**
     * mapper注解扫描包路径
     */
    final static String BASE_PACKAGE = "com.mybatis.mapper";
    /**
     * 初始化表情符号
     **/
    private final static String INIT_SQL_UTF8MB4 = "SET NAMES utf8mb4;";
    /**
     * SQL_SESSION_FACTORY
     */
    final static String SQL_SESSION_FACTORY = "sqlSessionFactory";
    private static final ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();

    /**
     * 配置数据源
     */
    @Bean(name = DATASOURCE, initMethod = "init", destroyMethod = "close")
    @Primary
    public DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(druidDataSourceConfig.driverClassName);
        dataSource.setUrl(druidDataSourceConfig.url);
        dataSource.setUsername(druidDataSourceConfig.username);
        dataSource.setPassword(druidDataSourceConfig.password);
        try {
            dataSource.setFilters(druidDataSourceConfig.filters);
        } catch (SQLException e) {
            log.error("error : ", e);
        }
        dataSource.setMaxActive(druidDataSourceConfig.maxActive);
        dataSource.setInitialSize(druidDataSourceConfig.initialSize);
        dataSource.setMaxWait(druidDataSourceConfig.maxWait);
        dataSource.setMinIdle(druidDataSourceConfig.minIdle);
        dataSource.setTimeBetweenConnectErrorMillis(druidDataSourceConfig.timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(druidDataSourceConfig.minEvictableIdleTimeMillis);
        // 申请连接的时候检测，如果空闲时间大于
        // timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。建议配置为true，不影响性能，并且保证安全性。
        dataSource.setTestWhileIdle(druidDataSourceConfig.testWhileIdle);
        dataSource.setValidationQuery(druidDataSourceConfig.validationQuery);
        dataSource.setTestOnBorrow(druidDataSourceConfig.testOnBorrow);
        dataSource.setTestOnReturn(druidDataSourceConfig.testOnReturn);
        dataSource.setPoolPreparedStatements(druidDataSourceConfig.poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(druidDataSourceConfig.getMaxPoolPreparedStatementPerConnectionSize());
        dataSource.setMaxOpenPreparedStatements(druidDataSourceConfig.maxOpenPreparedStatements);
        // 设置支持表情符号
        dataSource.setConnectionInitSqls(Collections.list(new StringTokenizer(INIT_SQL_UTF8MB4, ";")));
        //慢sql
        dataSource.setConnectionProperties(druidDataSourceConfig.getConnectionProperties());
        dataSource.setUseGlobalDataSourceStat(druidDataSourceConfig.isUseGlobalDataSourceStat());
        dataSource.setUseLocalSessionState(false);
        return dataSource;
    }

    @Bean
    public ServletRegistrationBean<StatViewServlet> druidServlet() {
        log.info("********************************************************");
        log.info("加载druid servlet");
        log.info("********************************************************");
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //设置ip白名单
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        //设置ip黑名单
        servletRegistrationBean.addInitParameter("deny", druidDataSourceConfig.getDruidBlackList());
        servletRegistrationBean.addInitParameter("sessionStatEnable", "false");
        //设置控制台管理用户__登录用户名和密码
        servletRegistrationBean.addInitParameter("loginUsername", druidDataSourceConfig.getDruidUserName());
        servletRegistrationBean.addInitParameter("loginPassword", druidDataSourceConfig.getDruidPwd());
        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid2/*,/druid/*，" +
                "/swagger-resources/*,/loginTest/*,/api/*,/webjars/*./webSocketServer/*,/webSocketTest/*,*.html,*.json");
        return filterRegistrationBean;
    }

    @Bean(name = SQL_SESSION_FACTORY)
    @Primary
    public SqlSessionFactory flowSqlSessionFactory(@Qualifier(DATASOURCE) DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();

        // 配置mapperLocations (如果这里配置了，则properties里面配置则会失效)
        // 解决方案 : 其它属性同理
        // 1. 重新配置 (properties无需添加 mybatis.mapper-locations=classpath:mappers/*.xml )
        sessionFactory.setMapperLocations(resourceResolver.getResources("classpath:mappers/*.xml"));
        // 2. properties添加 mybatis.mapper-locations=classpath:mappers/*.xml ， 从mybatisproperties获取
//        if (!ObjectUtils.isEmpty(mybatisProperties.getMapperLocations())) {
//            sessionFactory.setMapperLocations(mybatisProperties.resolveMapperLocations());
//        }
        // 配置 typeAliasesPackage
        sessionFactory.setTypeAliasesPackage("com.mybatis.entity");

        // 配置数据源
        sessionFactory.setDataSource(dataSource);

        // 配置mybatis.configuration信息
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 是否开启自动驼峰命名规则（camel case）映射，即从经典数据库列名 A_COLUMN 到经典 Java 属性名 aColumn 的类似映射。
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setLogPrefix(BASE_PACKAGE);
//        configuration.setLogImpl(StdOutImpl.class);

        sessionFactory.setConfiguration(configuration);

        return sessionFactory.getObject();
    }

}

4. 日志无法打印

4.1 文件中无法打印

logback.xml

<?xml version="1.0" encoding="UTF-8" ?>
<configuration scan="true" scanPeriod="60 seconds" debug="false">
    <property name="app.name" value="mybatis-druid"/>
    <property name="app.log.dir" value="/app/logs/${app.name}/"/>

    <appender name="sql" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <File>${app.log.dir}/sql.log</File>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>[%d{yyyy-MM-dd HH:mm:ss} [%t] [%X{traceId}]  %5p %c:%L] %m%n</pattern>
            <charset>UTF-8</charset>
        </encoder>
        <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
            <level>debug</level>
        </filter>
        <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
            <FileNamePattern>${app.log.dir}/%d{yyyyMMdd}/sql.log.%d{yyyyMMdd}_%i</FileNamePattern>
            <!-- 日志文件最大尺寸 -->
            <maxFileSize>200MB</maxFileSize>
            <!--日志文件保留天数-->
            <MaxHistory>180</MaxHistory>
        </rollingPolicy>
    </appender>
    <logger name="druid.sql.Statement" level="debug" additivity="false">
        <appender-ref ref="sql" />
    </logger>
    <logger name="druid.sql.ResultSet" level="debug" additivity="false">
        <appender-ref ref="sql" />
    </logger>

    <root level="info">
        <appender-ref ref="sql"/>
    </root>

</configuration>

4.2 控制台无法打印

DuridDataSourceFactory.java

configuration.setLogImpl(StdOutImpl.class);

小安灬

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
springboot整合mybatis使用druid连接池无法打印sql日志

1. properties*********注意#mybatis(若druid自定义SqlSessionFactory 则此配置会失效，解决方案在DuridDataSourceFactory将会说明)#mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl# 最大建立连接等待时间。druid.maxWait=60000# Destroy线程会检测连接的间隔时间,检测需要关闭的空闲连接testWhil
复制链接

扫一扫