SpringBoot 整合 Druid 数据库连接池

最新推荐文章于 2023-08-11 23:50:20 发布

Jaemon

最新推荐文章于 2023-08-11 23:50:20 发布

阅读量890

点赞数

分类专栏： Mybatis 开发笔记

本文链接：https://blog.csdn.net/u010979642/article/details/89029846

版权

开发笔记同时被 2 个专栏收录

69 篇文章 2 订阅

订阅专栏

Mybatis

15 篇文章 1 订阅

订阅专栏

SpringBoot 整合 Druid 数据库连接池

文章目录

SpringBoot 整合 Druid 数据库连接池

1. 添加 jar 包依赖

1.1 Maven 方式之 pom.xml 文件配置

<dependencies>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid</artifactId>
   <version>1.1.12</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.1</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
    <optional>true</optional>
</dependency>
</dependencies>

1.2 Gradle 方式之 build.gradle 文件配置

dependencies {
    compile("com.alibaba:druid:1.1.12")
    runtimeOnly 'mysql:mysql-connector-java'
    
    // 为 @ConfigurationProperties 属性生成 spring-configuration-metadata.json, 需要配合compileJava.dependsOn(processResources)
	compileOnly("org.springframework.boot:spring-boot-configuration-processor")
}

// 没有这句，不会生成 additional-spring-configuration-metadata.json
compileJava.dependsOn(processResources)

2. application.properties

server.port=8888
server.servlet.context-path=/aal
spring.application.name=aal

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://${MYSQL_IP:127.0.0.1}:${MYSQL_PORT:3306}/${MYSQL_DB:xsi}?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=UTC
spring.datasource.username=${MYSQL_UNAME:answer}
spring.datasource.password=${MYSQL_PASSWD:7654321}

#########################################################################################
#                      下面为连接池的补充设置, 应用到上面所有数据源中
#########################################################################################
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 配置访问监控信息的用户名密码
spring.datasource.login-user-name=answer
spring.datasource.login-password=123456
# 初始化连接大小, 连接池建立时创建的初始化连接数
spring.datasource.initial-size=5
# 最小空闲连接数, 连接池中最小的活跃连接数
spring.datasource.min-idle=5
# 最大连接数, 连接池中最大的活跃连接数
spring.datasource.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.max-wait=60000
# 配置间隔多久才进行一次检测, 检测需要关闭的空闲连接, 单位是毫秒
spring.datasource.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间, 单位是毫秒
spring.datasource.min-evictable-idle-time-millis=300000
# 校验SQL, Oracle配置 spring.datasource.validationQuery=SELECT 1 FROM DUAL, 如果不配validationQuery项, 则下面三项配置无用
spring.datasource.validation-query=SELECT 'x'
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=false
spring.datasource.test-on-return=false
# 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
spring.datasource.filters=stat,wall
# spring.datasource.log-slow-sql=true
# 通过 connectProperties 属性来打开mergeSql功能; 慢SQL记录
spring.datasource.connection-properties=druid.stat.mergeSql=true;druid.stat.logSlowSql=true;druid.stat.slowSqlMillis=5000

# 打开PSCache, 并且指定每个连接上 PSCache 的大小
spring.datasource.pool-prepared-statements=true
spring.datasource.max-pool-prepared-statement-per-connection-size=20
# 合并多个 DruidDataSource 的监控数据
spring.datasource.use-global-data-source-stat=true


mybatis.mapper-locations=classpath*:/mybatis/mapper/**.xml
mybatis.type-aliases-package=com.answer.aal.entity
mybatis.config-location=classpath:/mybatis/config/mybatis-config.xml

3. DruidConfig.java

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author Answer.AI.L
 * @date 2019-04-04
 *
 * http://127.0.0.1:8888/aal/druid/login.html
 * loginUserName & loginPassword
 */
@Configuration
@Slf4j
@Data
@ConfigurationProperties(prefix="spring.datasource")
@SuppressWarnings("unchecked")
public class DruidConfig {
    /**
     * spring.datasource.url
     * */
    @Value("${spring.datasource.url}")
    private String dbUrl;

    /**
     * spring.datasource.url
     * */
    @Value("${spring.datasource.username}")
    private String username;

    /**
     * spring.datasource.url
     * */
    @Value("${spring.datasource.password}")
    private String password;

    /**
     * spring.datasource.url
     * */
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    /**
     * spring.datasource.login-user-name
     * */
    @Value("${spring.datasource.login-user-name}")
    private String loginUserName;

    /**
     * spring.datasource.login-password
     * */
    @Value("${spring.datasource.login-password}")
    private String loginPassword;

    /**
     * spring.datasource.initial-size
     * */
    @Value("${spring.datasource.initial-size}")
    private int initialSize;

    /**
     * spring.datasource.datasource.min-idle
     * */
    @Value("${spring.datasource.min-idle}")
    private int minIdle;

    /**
     * spring.datasource.max-active
     * */
    @Value("${spring.datasource.max-active}")
    private int maxActive;

    /**
     * spring.datasource.max-wait
     * */
    @Value("${spring.datasource.max-wait}")
    private int maxWait;

    /**
     * spring.datasource.time-between-eviction-runs-millis
     * */
    @Value("${spring.datasource.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;

    /**
     * spring.datasource.min-evictable-idle-time-millis
     * */
    @Value("${spring.datasource.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;

    /**
     * spring.datasource.validation-query
     * */
    @Value("${spring.datasource.validation-query}")
    private String validationQuery;

    /**
     * spring.datasource.test-while-idle
     * */
    @Value("${spring.datasource.test-while-idle}")
    private boolean testWhileIdle;

    /**
     * spring.datasource.test-on-borrow
     * */
    @Value("${spring.datasource.test-on-borrow}")
    private boolean testOnBorrow;

    /**
     * spring.datasource.test-on-return
     * */
    @Value("${spring.datasource.test-on-return}")
    private boolean testOnReturn;

    /**
     * spring.datasource.filters
     * */
    @Value("${spring.datasource.filters}")
    private String filters;

    /**
     * spring.datasource.log-slow-sql
     * */
//    @Value("${spring.datasource.log-slow-sql}")
//    private String logSlowSql;

    /**
     * spring.datasource.pool-prepared-statements
     * */
    @Value("${spring.datasource.pool-prepared-statements}")
    private boolean poolPreparedStatements;

    /**
     * spring.datasource.max-pool-prepared-statement-per-connection-size
     * */
    @Value("${spring.datasource.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;

    /**
     * spring.datasource.connection-properties
     * */
    @Value("${spring.datasource.connection-properties}")
    private String connectionProperties;

    /**
     * spring.datasource.use-global-data-source-stat
     * */
    @Value("${spring.datasource.use-global-data-source-stat}")
    private boolean useGlobalDataSourceStat;

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        // 登录URL http://127.0.0.1:8888/aal/druid/login.html
        reg.addUrlMappings("/druid/*");

        // 设置白名单
        reg.addInitParameter("allow", "127.0.0.1");
        // 设置黑名单
        reg.addInitParameter("deny", "");

        // 设置登录查看信息的账号密码
        reg.addInitParameter("loginUsername", loginUserName);
        reg.addInitParameter("loginPassword", loginPassword);
//        reg.addInitParameter("logSlowSql", logSlowSql);
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

    @Bean("dataSource")
    public DataSource druidDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            log.error("druid configuration initialization filter", e);
        }
        // connectionProperties 属性写入方式1
        Properties properties = new Properties();
        String[] cps = connectionProperties.split(";");
        for (String connectionPropertie : cps) {
            String[] attr = connectionPropertie.split("=");
            properties.put(attr[0].trim(), attr[1].trim());
        }
        datasource.setConnectProperties(properties);
        // connectionProperties 属性写入方式2
        // datasource.setConnectionProperties(connectionProperties);
        return datasource;
    }


    /**
     * 配置事务管理 当事务类型为 {@link org.springframework.transaction.annotation.Propagation#NESTED} 时需要修改为 {@link DataSourceTransactionManager}
     * */
    @Bean("transactionManager")
    public PlatformTransactionManager annotationDrivenTransactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

4. 验证

http://localhost:8888/aal/druid/login.html

5. Druid 适配数据库密码加密

5.1 加密数据库密码-方式1

# 进入 druid-1.1.12.jar 所在目录并执行以下加密指令, 其中 answerail 为数据库密码
java -cp druid-1.1.12.jar com.alibaba.druid.filter.config.ConfigTools answerail

# 得到如下结果
privateKey:MIIBUwIBADANBgkqhkiG9w0BAQEFAASCAT0wggE5AgEAAkEA02fDk9LDmrc/tMf3GrbvioDKmKWs4ZLQ+i5dTT+ibpJuurfHPv6RBaMOpsFyFUNXw++kIcz5qf1opkF0sV+dpwIDAQABAkBcmsONaXHNwB/j7QRNa30ayrf0jf5hAZMRRGYShKF+lVJUjfgV0n1YEKGMdP1A2LrrFq3v6TdK+KvwdV30FIXBAiEA9dOv9sjrBr//FOFh+iQl1N5XMihoJGYtjWwti/6bbMkCIQDcJ2lLGEc7j/VJqTUBrlwmKGPlF50OJ/RR/wh5JZme7wIgc3wTOliBMRaZevG22ZCJxUr+o62f3xmxZLLhYtclpSECIERbZGExx6Rh3ZqilFfSSMB8WBAhFd34X+85zeq31ww3AiAjftiuPZ4X+3aTY8H/Pv9uCComUqguoZtbPOUwkamLaA==
publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBANNnw5PSw5q3P7TH9xq274qAypilrOGS0PouXU0/om6Sbrq3xz7+kQWjDqbBchVDV8PvpCHM+an9aKZBdLFfnacCAwEAAQ==
password:gbtlFF+zO5POmoGcddnEFYF2AJxQYidHxUPeOfaSVu6ae5y8J42KpI3RF6qawvQjPAJPaWCCrv6F2ZP4sa//TA==

5.2 加密数据库密码-方式2

public class DbPwdTools {

    public static void main(String[] args) throws Exception {
        String password = "answer_db";
        encryption(password);

        System.out.println();

        String publicKey = "MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAI4w/XUNu6c5h7Kcxn17rwC2udNZu+xYDBkjj+cs0OaK61QcWxQX2Pj04vbFo6EHr2rqrYfEi/ppQJfgDxPwsYUCAwEAAQ==";
        String ciphertext = "NpvBsE051GkZXkUjKCUF0N/naX9UrvLcFJVJ2wjwKYhVXFcFhUTKm6tuYXJlBhWgTyZOXeWNGP84u9Xo3LLQXA==";
        decrypt(publicKey, ciphertext);
    }

    /**
     * 加密
     *
     * @param pwd 密码
     * */
    private static void encryption(String pwd) throws Exception {
        String[] arr = ConfigTools.genKeyPair(512);
        System.out.println("私钥: " + arr[0]);
        System.out.println("公钥: " + arr[1]);
        System.out.println("密码密文: " + encrypt(arr[0], pwd));
    }

    /**
     * 解密
     *
     * @param publicKey 公钥
     * @param ciphertext 密文
     * */
    private static void decrypt(String publicKey, String ciphertext) throws Exception {
        String pwd = ConfigTools.decrypt(publicKey, ciphertext);

        System.out.println("密码为: " + pwd);
    }

}

5.3 application.properties 文件修改

# 填写加密后的密码
spring.datasource.password=${MYSQL_PASSWD:gbtlFF+zO5POmoGcddnEFYF2AJxQYidHxUPeOfaSVu6ae5y8J42KpI3RF6qawvQjPAJPaWCCrv6F2ZP4sa//TA==}

# 新增 config 值(必须)
spring.datasource.filters=stat,wall,config

# 新增属性 config.decrypt=true;config.decrypt.key=${publicKey}
spring.datasource.connection-spring.datasource.connection-properties=druid.stat.mergeSql=true;druid.stat.logSlowSql=true;druid.stat.slowSqlMillis=5000;config.decrypt=true;config.decrypt.key=${publicKey}

publicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBANNnw5PSw5q3P7TH9xq274qAypilrOGS0PouXU0/om6Sbrq3xz7+kQWjDqbBchVDV8PvpCHM+an9aKZBdLFfnacCAwEAAQ==

方式2

<dependencies>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid</artifactId>
   <version>1.1.18</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.1</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.18</version>
</dependency>

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://${MYSQL_IP:127.0.0.1}:${MYSQL_PORT:3306}/${MYSQL_DB:answer}?useUnicode=true&characterEncoding=utf-8
    username: root
    password: 123456
    druid:
      # 初始化时建立物理连接的个数
      initial-size: 10
      # 最小连接池数量
      min-idle: 10
      # 最大连接池数量 maxIdle已经不再使用
      max-active: 20
      # 获取连接时最大等待时间，单位毫秒
      max-wait: 60000
      # 配置间隔多久才进行一次检测, 检测需要关闭的空闲连接, 也是作为testWhileIdel执行的依据. 单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间, 单位是毫秒
      min-evictable-idle-time-millis: 30000
      # 校验SQL(必须是一个查询语句), Oracle配置 SELECT 1 FROM DUAL, 如果不配validationQuery项, 则下面三项配置无用
      validation-query: select 'x'
      # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      test-while-idle: true
      # 申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-borrow: false
      # 归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-return: false
      # 当数据库抛出不可恢复的异常时,抛弃该连接
      exception-sorter: true
      # 是否缓存preparedStatement,mysql5.5+建议开启
      pool-prepared-statements: true
      # 当值大于0时poolPreparedStatements会自动修改为true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置扩展插件, 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
      filters: stat,wall
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.logSlowSql=true;druid.stat.slowSqlMillis=5000
      # 合并多个DruidDataSource的监控数据
      use-global-data-source-stat: true
      # 设置访问druid监控页的账号和密码,默认没有
      stat-view-servlet:
        login-username: answer
        login-password: 123456

mybatis:
  config-location: classpath:mybatis/config/mybatis-config.xml
  mapper-locations: classpath*:mybatis/mapper/**.xml
  configuration:
    map-underscore-to-camel-case: true