【第5章】SpringBoot整合Druid

文章目錄

前言
一、啟動器
二、配置
- 1.JDBC 配置
- 2.連接池配置
- 3. 監控配置
三、配置多數據源
- 1. 添加配置
- 2. 創建數據源
四、配置 Filter
- 1. 配置Filter
- 2. 可配置的Filter
五、獲取 Druid 的監控數據
六、案例
- 1. 問題
- 2. 引入庫
- 3. 配置
- 4. 配置類
- 5. 測試類
- 6. 測試結果
七、案例 $\color{#00FF00}{(推薦)}$
- 1. 引入庫
- 2. 配置
- 3. 測試類
- 4. 測試結果
總結

前言

Druid是Java語言中最好的數據庫連接池。Druid能夠提供強大的監控和擴展功能。

一、啟動器

Druid Spring Boot Starter 用于幫助你在Spring Boot項目中輕松集成Druid數據庫連接池和監控。

<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.22</version>
</dependency>

二、配置

1.JDBC 配置

spring.datasource.druid.url= # 或spring.datasource.url= 
spring.datasource.druid.username= # 或spring.datasource.username=
spring.datasource.druid.password= # 或spring.datasource.password=
spring.datasource.druid.driver-class-name= #或 spring.datasource.driver-class-name=

2.連接池配置

spring.datasource.druid.initial-size=
spring.datasource.druid.max-active=
spring.datasource.druid.min-idle=
spring.datasource.druid.max-wait=
spring.datasource.druid.pool-prepared-statements=
spring.datasource.druid.max-pool-prepared-statement-per-connection-size= 
spring.datasource.druid.max-open-prepared-statements= #和上面的等價
spring.datasource.druid.validation-query=
spring.datasource.druid.validation-query-timeout=
spring.datasource.druid.test-on-borrow=
spring.datasource.druid.test-on-return=
spring.datasource.druid.test-while-idle=
spring.datasource.druid.time-between-eviction-runs-millis=
spring.datasource.druid.min-evictable-idle-time-millis=
spring.datasource.druid.max-evictable-idle-time-millis=
spring.datasource.druid.filters= #配置多個英文逗號分隔
....//more

3. 監控配置

# WebStatFilter配置，說明請參考Druid Wiki，配置_配置WebStatFilter
spring.datasource.druid.web-stat-filter.enabled= #是否啟用StatFilter默認值false
spring.datasource.druid.web-stat-filter.url-pattern=
spring.datasource.druid.web-stat-filter.exclusions=
spring.datasource.druid.web-stat-filter.session-stat-enable=
spring.datasource.druid.web-stat-filter.session-stat-max-count=
spring.datasource.druid.web-stat-filter.principal-session-name=
spring.datasource.druid.web-stat-filter.principal-cookie-name=
spring.datasource.druid.web-stat-filter.profile-enable=# StatViewServlet配置，說明請參考Druid Wiki，配置_StatViewServlet配置
spring.datasource.druid.stat-view-servlet.enabled= #是否啟用StatViewServlet（監控頁面）默認值為false（考慮到安全問題默認并未啟動，如需啟用建議設置密碼或白名單以保障安全）
spring.datasource.druid.stat-view-servlet.url-pattern=
spring.datasource.druid.stat-view-servlet.reset-enable=
spring.datasource.druid.stat-view-servlet.login-username=
spring.datasource.druid.stat-view-servlet.login-password=
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.deny=# Spring監控配置，說明請參考Druid Github Wiki，配置_Druid和Spring關聯監控配置
spring.datasource.druid.aop-patterns= # Spring監控AOP切入點，如x.y.z.service.*,配置多個英文逗號分隔

三、配置多數據源

1. 添加配置

spring.datasource.url=
spring.datasource.username=
spring.datasource.password=# Druid 數據源配置，繼承spring.datasource.* 配置，相同則覆蓋
...
spring.datasource.druid.initial-size=5
spring.datasource.druid.max-active=5
...# Druid 數據源 1 配置，繼承spring.datasource.druid.* 配置，相同則覆蓋
...
spring.datasource.druid.one.max-active=10
spring.datasource.druid.one.max-wait=10000
...# Druid 數據源 2 配置，繼承spring.datasource.druid.* 配置，相同則覆蓋
...
spring.datasource.druid.two.max-active=20
spring.datasource.druid.two.max-wait=20000
...

2. 創建數據源

@Primary
@Bean
@ConfigurationProperties("spring.datasource.druid.one")
public DataSource dataSourceOne(){return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.druid.two")
public DataSource dataSourceTwo(){return DruidDataSourceBuilder.create().build();
}

四、配置 Filter

1. 配置Filter

你可以通過 spring.datasource.druid.filters=stat,wall,log4j … 的方式來啟用相應的內置Filter，不過這些Filter都是默認配置。如果默認配置不能滿足你的需求，你可以放棄這種方式，通過配置文件來配置Filter，下面是例子。

# 配置StatFilter 
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.stat.db-type=h2
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000# 配置WallFilter 
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=h2
spring.datasource.druid.filter.wall.config.delete-allow=false
spring.datasource.druid.filter.wall.config.drop-table-allow=false# 其他 Filter 配置不再演示

2. 可配置的Filter

目前為以下 Filter 提供了配置支持，請參考文檔或者根據IDE提示（spring.datasource.druid.filter.*）進行配置。

StatFilter
WallFilter
ConfigFilter
EncodingConvertFilter
Slf4jLogFilter
Log4jFilter
Log4j2Filter
CommonsLogFilter

要想使自定義 Filter 配置生效需要將對應 Filter 的 enabled 設置為 true ，Druid Spring Boot Starter 默認禁用 StatFilter，你也可以將其 enabled 設置為 true 來啟用它。

五、獲取 Druid 的監控數據

Druid 的監控數據可以在開啟 StatFilter 后通過 DruidStatManagerFacade 進行獲取，獲取到監控數據之后你可以將其暴露給你的監控系統進行使用。Druid 默認的監控系統數據也來源于此。下面給做一個簡單的演示，在 Spring Boot 中如何通過 HTTP 接口將 Druid 監控數據以 JSON 的形式暴露出去，實際使用中你可以根據你的需要自由地對監控數據、暴露方式進行擴展。

@RestController
public class DruidStatController {@GetMapping("/druid/stat")public Object druidStat(){// DruidStatManagerFacade#getDataSourceStatDataList 該方法可以獲取所有數據源的監控數據，除此之外 DruidStatManagerFacade 還提供了一些其他方法，你可以按需選擇使用。return DruidStatManagerFacade.getInstance().getDataSourceStatDataList();}
}

[{"Identity": 1583082378,"Name": "DataSource-1583082378","DbType": "h2","DriverClassName": "org.h2.Driver","URL": "jdbc:h2:file:./demo-db","UserName": "sa","FilterClassNames": ["com.alibaba.druid.filter.stat.StatFilter"],"WaitThreadCount": 0,"NotEmptyWaitCount": 0,"NotEmptyWaitMillis": 0,"PoolingCount": 2,"PoolingPeak": 2,"PoolingPeakTime": 1533782955104,"ActiveCount": 0,"ActivePeak": 1,"ActivePeakTime": 1533782955178,"InitialSize": 2,"MinIdle": 2,"MaxActive": 30,"QueryTimeout": 0,"TransactionQueryTimeout": 0,"LoginTimeout": 0,"ValidConnectionCheckerClassName": null,"ExceptionSorterClassName": null,"TestOnBorrow": true,"TestOnReturn": true,"TestWhileIdle": true,"DefaultAutoCommit": true,"DefaultReadOnly": null,"DefaultTransactionIsolation": null,"LogicConnectCount": 103,"LogicCloseCount": 103,"LogicConnectErrorCount": 0,"PhysicalConnectCount": 2,"PhysicalCloseCount": 0,"PhysicalConnectErrorCount": 0,"ExecuteCount": 102,"ErrorCount": 0,"CommitCount": 100,"RollbackCount": 0,"PSCacheAccessCount": 100,"PSCacheHitCount": 99,"PSCacheMissCount": 1,"StartTransactionCount": 100,"TransactionHistogram": [55,44,1,0,0,0,0],"ConnectionHoldTimeHistogram": [53,47,3,0,0,0,0,0],"RemoveAbandoned": false,"ClobOpenCount": 0,"BlobOpenCount": 0,"KeepAliveCheckCount": 0,"KeepAlive": false,"FailFast": false,"MaxWait": 1234,"MaxWaitThreadCount": -1,"PoolPreparedStatements": true,"MaxPoolPreparedStatementPerConnectionSize": 5,"MinEvictableIdleTimeMillis": 30001,"MaxEvictableIdleTimeMillis": 25200000,"LogDifferentThread": true,"RecycleErrorCount": 0,"PreparedStatementOpenCount": 1,"PreparedStatementClosedCount": 0,"UseUnfairLock": true,"InitGlobalVariants": false,"InitVariants": false}
]

六、案例

1. 問題

在這里插入圖片描述

在1.2.13快照版及之后的版本，都會去查找一個${project.version}的屬性導致我druid的包一直沒有下載下來，
就算切換到之前的版本自動注入也無法生效，SpringBoot3是已經確定的大版本，所以我們只能放棄對start的使用。

<parent><groupId>com.alibaba</groupId><artifactId>druid-parent</artifactId><version>1.2.23-SNAPSHOT</version><relativePath>../pom.xml</relativePath>
</parent>
<artifactId>druid-spring-boot-3-starter</artifactId>

也可能是版本問題，官方的下一個版本1.2.23-SNAPSHOT，才能匹配springboot3版本。

2. 引入庫

由于上面的問題，我們只能使用spring常規的使用方式，但是和mvc中可能會略有區別。

<properties><java.version>17</java.version><spring.version>6.1.6</spring.version><springboo.version>3.2.5</springboo.version>
</properties><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>${spring.version}</version>
</dependency>
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.19</version>
</dependency>
<dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.22</version>
</dependency>

3. 配置

spring:application:name: spring-boot3#druiddatasource:#type: com.alibaba.druid.pool.DruidDataSourcedruid:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=trueusername: rootpassword: 123456a?initial-size: 5max-active: 20max-wait: 60000min-idle: 3

4. 配置類

package org.example.springboot3.druid.config;import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;/*** Create by zjg on 2024/5/19*/
@Configuration
public class DruidConfig {@Bean@ConfigurationProperties("spring.datasource.druid")public DataSource dataSource(){return new DruidDataSource();}
}

5. 測試類

package org.example.springboot3.druid.controller;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/*** Create by zjg on 2024/5/19*/
@RestController
@RequestMapping("/druid/")
public class DruidController {@AutowiredDataSource dataSource;@RequestMapping("001")public String druid001() throws SQLException {Connection connection = dataSource.getConnection();Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery("select 1 from dual");String result = null;if(resultSet.next()){result=resultSet.getString(1);}statement.close();connection.close();return result;}
}

6. 測試結果

單獨使用最新的1.2.22版本是沒有問題的。

七、案例 $\color{#00FF00}{(推薦)}$

昨天寫了這篇文章之后，感覺沒有把最好的帶給家人們，經過一晚上的不斷閱讀諸佬的經典，總算找到了druid對springboot支持的starter，可能是阿里的哥哥們太忙了，官方文檔是一點介紹沒有啊！

1. 引入庫

<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.19</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-3-starter -->
<dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-3-starter</artifactId><version>1.2.22</version>
</dependency>

2. 配置

spring:application:name: spring-boot3#druiddatasource:#type: com.alibaba.druid.pool.DruidDataSourcedruid:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=trueusername: rootpassword: 123456a?initial-size: 5max-active: 20max-wait: 60000min-idle: 3

3. 測試類

package org.example.springboot3.druid.controller;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/*** Create by zjg on 2024/5/19*/
@RestController
@RequestMapping("/druid/")
public class DruidController {@AutowiredDataSource dataSource;@RequestMapping("001")public String druid001() throws SQLException {Connection connection = dataSource.getConnection();Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery("select 1 from dual");String result = null;if(resultSet.next()){result=resultSet.getString(1);}statement.close();connection.close();return result;}
}