1. Introduction
Druid connection pool is an open source database connection pool project of Alibaba. Druid connection pool is born for monitoring. It has built-in powerful monitoring function. The monitoring feature does not affect the performance. Powerful, can prevent SQL injection, and built-in login can diagnose Hack application behavior
2. Download
git address: https://github.com/alibaba/druid
http://repo1.maven.org/maven2/com/alibaba/druid/
maven dependency:
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.5</version> </dependency>
3. Configuration details
to configure | Default value | explain |
---|---|---|
name | The significance of configuring this attribute is that if there are multiple data sources, they can be distinguished by name during monitoring. If it is not configured, a name will be generated in the format of "DataSource -" + System.identityHashCode(this). In addition, configuring this property does not work in version 1.0.5 at least, and forcing the setting of name will make an error | |
url | The url to connect to the database is different from database to database. For example: MySQL: JDBC: mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto | |
username | User name to connect to the database | |
password | Password to connect to the database. If you don't want the password written directly in the configuration file, you can use configfilter. Look here in detail https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter | |
driverClassName | Automatic identification according to url | This item can be configured or not. If druid is not configured, it will automatically identify the dbType according to the url, and then select the corresponding driverClassName |
initialSize | 0 | The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection |
maxActive | 8 | Maximum number of connection pools |
maxIdle | 8 | It is no longer used, and the configuration has no effect |
minIdle | Minimum number of connection pools | |
maxWait | Maximum wait time to get a connection, in milliseconds. After maxWait is configured, the fair lock is enabled by default, and the concurrency efficiency will be reduced. If necessary, you can use the unfair lock by configuring the useUnfairLock attribute to true | |
poolPreparedStatements | false | Whether to cache preparedStatement, that is, PSCache. PSCache greatly improves the performance of databases that support cursors, such as oracle. It is recommended to close under mysql |
maxPoolPreparedStatementPerConnectionSize | -1 | To enable PSCache, it must be configured to be greater than 0. When greater than 0, poolPreparedStatements is automatically triggered and modified to true. In Druid, there will be no problem that PSCache in Oracle occupies too much memory. You can configure this value to be larger, such as 100 |
validationQuery | The sql used to check whether the connection is valid requires a query statement, usually select 'x'. If validationQuery is null, testonmirror, testOnReturn and testwhiteidle will not work | |
validationQueryTimeout | Unit: second, the timeout for detecting whether the connection is valid. The underlying layer calls the void setQueryTimeout(int seconds) method of the jdbc Statement object | |
testOnBorrow | true | When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce performance |
testOnReturn | false | When returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance |
testWhileIdle | false | It is recommended to configure to true, which will not affect performance and ensure security. Check when applying for a connection. If the idle time is greater than timebetween evictionrunsmillis, run validationQuery to check whether the connection is valid |
keepAlive | false | The keepAlive operation will be executed if the idle time of connections within the number of minidles in the connection pool exceeds minEvictableIdleTimeMillis |
timeBetweenEvictionRunsMillis | 1 minute | There are two meanings: 1) the destroy thread will detect the connection interval. If the connection idle time is greater than or equal to minEvictableIdleTimeMillis, the physical connection will be closed. 2) For the judgment basis of testwhiteidle, see the description of testwhiteidle attribute in detail |
numTestsPerEvictionRun | 30 Minutes | No longer used, a DruidDataSource only supports one EvictionRun |
minEvictableIdleTimeMillis | The minimum time a connection remains idle without being evicted | |
connectionInitSqls | sql executed during physical connection initialization | |
exceptionSorter | When the database throws some unrecoverable exceptions, the connection is discarded | |
filters | The attribute type is string. The extension plug-ins are configured by alias. The commonly used plug-ins are: filter for monitoring statistics: stat, filter for log: log4j, filter for defending sql injection: wall | |
proxyFilters | The type is list < com. Alibaba. Druid. Filter. Filter >. If both filters and proxyFilters are configured, it is a combination relationship, not a replacement relationship |
4. Configure StatFilter for monitoring
https://github.com/alibaba/druid/wiki/%E9%85%8D%E7%BD%AE_StatFilter
5.spring boot integration druid
maven configuration
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.spring.druid</groupId> <artifactId>spring-boot-druid</artifactId> <version>0.0.1-SNAPSHOT</version> <properties> <spring-boot.version>2.5.2</spring-boot.version> <druid.version>1.2.5</druid.version> </properties> <dependencies> <!-- druid Connection pool --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>${druid.version}</version> </dependency> <!--database --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!--web modular --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> </dependencies> <dependencyManagement> <dependencies> <!--spring boot Public version definition --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-dependencies</artifactId> <version>${spring-boot.version}</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement> </project>
application.yml configuration:
server: port: 8888 spring: application: name: spring-boot-druid # data source datasource: type: com.alibaba.druid.pool.DruidDataSource druid: driver-class-name: com.mysql.cj.jdbc.Driver username: root password: 123456 url: jdbc:mysql://xxxx/test?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true initialSize: 5 maxActive: 20 #Maximum wait time to get a connection, in milliseconds maxWait: 6000 #Minimum number of connection pools minIdle: 1 #Configure how often to detect idle connections that need to be closed. The unit is milliseconds timeBetweenEvictionRunsMillis: 2000 #The minimum time, in milliseconds, that a connection remains idle without being evicted minEvictableIdleTimeMillis: 600000 maxEvictableIdleTimeMillis: 900000 #sql used to check whether the connection is valid validationQuery: select 1 #Check when applying for a connection. If the idle time is greater than timebetween evictionrunsmillis, run validationQuery to check whether the connection is valid testWhileIdle: true #When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce performance testOnBorrow: false #When returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance testOnReturn: false #asyncInit is a newly added configuration in 1.1.4. If there are a large number of initialSize, opening it will speed up the application startup time asyncInit: true #Enable web monitoring visualization stat-view-servlet: enabled: true allow: "" url-pattern: /druid/* #login-username: admin #login-password: admin #Turn on monitoring filter: stat: enabled: true log-slow-sql: true slow-sql-millis: 2000 merge-sql: true #Anti SQL injection wall: config: multi-statement-allow: true