Detailed explanation of alibaba druid database connection pool

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 configureDefault valueexplain
nameThe 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
urlThe 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
usernameUser name to connect to the database
passwordPassword 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
driverClassNameAutomatic identification according to urlThis 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
initialSize0The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection
maxActive8Maximum number of connection pools
maxIdle8It is no longer used, and the configuration has no effect
minIdleMinimum number of connection pools
maxWaitMaximum 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
poolPreparedStatementsfalseWhether 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-1To 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
validationQueryThe 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
validationQueryTimeoutUnit: 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
testOnBorrowtrueWhen applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce performance
testOnReturnfalseWhen returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance
testWhileIdlefalseIt 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
keepAlivefalseThe keepAlive operation will be executed if the idle time of connections within the number of minidles in the connection pool exceeds minEvictableIdleTimeMillis
timeBetweenEvictionRunsMillis1 minuteThere 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
numTestsPerEvictionRun30 MinutesNo longer used, a DruidDataSource only supports one EvictionRun
minEvictableIdleTimeMillisThe minimum time a connection remains idle without being evicted
connectionInitSqlssql executed during physical connection initialization
exceptionSorterWhen the database throws some unrecoverable exceptions, the connection is discarded
filtersThe 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
proxyFiltersThe 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

Tags: Java Druid

Posted on Thu, 18 Nov 2021 06:01:32 -0500 by harishkumar09