使用Druid优化项目

最新推荐文章于 2024-03-31 12:32:21 发布

GuaGea

最新推荐文章于 2024-03-31 12:32:21 发布

阅读量212

点赞数

文章标签： java 数据库开发语言

本文链接：https://blog.csdn.net/GuaGea/article/details/134295442

版权

前言：

项目体量大了总需要优化接口，优化sql查询等，使用druid可以方便的来查找运行慢，次数多的接口sql，配置druid的步骤也相对简单

一、pom包添加依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.9</version>
        </dependency>

二、添加druid配置的工具类

package com.cgy.template.db.config;

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.springframework.beans.factory.annotation.Autowired;
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.jdbc.core.JdbcTemplate;

/**
 * 数据库连接池配置
 * @author cgy
 */
@Slf4j
@Configuration
public class DruidConfig {
    @Value("${spring.datasource.url}")
    private String dbUrl;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;
    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;
    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;
    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;
    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;
    @Value("${spring.datasource.druid.test-while-idle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.druid.test-on-borrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.druid.test-on-return}")
    private boolean testOnReturn;
    @Value("${spring.datasource.druid.filters}")
    private String filters;
    @Value("${spring.datasource.druid.connection-properties}")
    private String connectionProperties;
    @Value("${spring.datasource.druid.stat-view-servlet.enabled}")
    private boolean enabled;
    @Value("${spring.datasource.druid.stat-view-servlet.url-pattern}")
    private String urlMappings;
    @Value("${spring.datasource.druid.stat-view-servlet.login-username}")
    private String loginUsername;
    @Value("${spring.datasource.druid.stat-view-servlet.login-password}")
    private String loginPassword;
    @Value("${spring.datasource.druid.stat-view-servlet.reset-enable}")
    private String resetEnable;
    @Value("${spring.datasource.druid.stat-view-servlet.allow}")
    private String allow;
    @Value("${spring.datasource.druid.stat-view-servlet.deny}")
    private String deny;
    @Value("${spring.datasource.druid.web-stat-filter.enabled}")
    private boolean filterEnabled;
    @Value("${spring.datasource.druid.web-stat-filter.exclusions}")
    private String exclusions;
    @Value("${spring.datasource.druid.web-stat-filter.url-pattern}")
    private String filterUrlPattern;


    /**
     * Druid 连接池配置
     */
    @Bean
    public DruidDataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (Exception e) {
            log.error("druid configuration initialization filter", e);
        }
        datasource.setConnectionProperties(connectionProperties);
        return datasource;
    }

    /**
     * JDBC操作配置
     */
    @Bean(name = "dataOneTemplate")
    public JdbcTemplate jdbcTemplate (@Autowired DruidDataSource dataSource) {
        return new JdbcTemplate(dataSource) ;
    }

    /**
     * 配置 Druid 监控界面
     */
    @Bean
    @SuppressWarnings("rawtypes")
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean srb = new ServletRegistrationBean<>(new StatViewServlet(), urlMappings);
        srb.setEnabled(enabled);
        // 设置控制台管理用户
        srb.addInitParameter("loginUsername", loginUsername);
        srb.addInitParameter("loginPassword", loginPassword);
        // 是否可以重置数据
        srb.addInitParameter("resetEnable", resetEnable);
        srb.addInitParameter("allow", allow);
        srb.addInitParameter("deny", deny);
        return srb;
    }

    @Bean
    @SuppressWarnings("rawtypes")
    public FilterRegistrationBean statFilter() {
        // 创建过滤器
        FilterRegistrationBean frb = new FilterRegistrationBean<>(new WebStatFilter());
        frb.setEnabled(filterEnabled);
        // 设置过滤器过滤路径
        frb.addUrlPatterns(filterUrlPattern);
        // 忽略过滤的形式
        frb.addInitParameter("exclusions", exclusions);
        return frb;
    }
}

三、配置文件添加

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/xxxxxxxx
    username: xxxxx
    password: xxxxx
    // 添加配置
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存时间
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT VERSION()
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开 PSCache，并且指定每个连接上 PSCache 的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的 Filter，去掉后监控界面 SQL 无法统计，wall 用于防火墙
      filters: stat,wall,slf4j
      # 通过 connection-properties 属性打开 mergeSql 功能；慢 SQL 记录
      connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      # 配置 DruidStatFilter
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
      # 配置 DruidStatViewServlet
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        # IP 白名单，没有配置或者为空，则允许所有访问
        allow: 127.0.0.1,localhost
        # IP 黑名单，若白名单也存在，则优先使用
        deny: 192.168.1.100
        # 禁用 HTML 中 Reset All 按钮
        reset-enable: false
        # 登录用户名/密码
        login-username: test
        login-password: test