Introduction to Druid (Spring Boot + Mybatis + Druid Data Source [Official start]

Keywords: Programming Druid Spring SQL Database

Introduction to Druid

Druid is a very good database connection pool. In terms of function, performance and extensibility, it surpasses other database connection pools, including DBCP, C3P0, BoneCP, Proxool and JBoss Data Source.

Druid has deployed more than 600 applications in Alibaba, after more than a year of rigorous deployment of large-scale production environment.

Druid is a JDBC component that consists of three parts:

A plug-in system based on Filter-Chain mode.
Druid Data Source is an efficient and manageable database connection pool.
SQLParser

Functions of Druid

Compatible with DBCP

Druid provides an efficient, powerful and scalable database connection pool. Moving from DBCP to Druid requires only modifying the implementation class of the data source.

Powerful monitoring features

Druid has built-in a powerful StatFilter plug-in that monitors database access performance and clearly knows how connection pools and SQL work.

Monitor SQL execution time, ResultSet holding time, number of returned rows, number of updated rows, number of errors, and error stack information.
Time-consuming interval distribution of SQL execution. What is the time-consuming interval distribution? For example, one SQL executes 1000 times, 50 times in the 0-1 millisecond interval, 800 times in 1-10 milliseconds, 100 times in 10-100 milliseconds, 30 times in 100-1000 milliseconds, 15 times in 1-10 seconds, and more than 5 times in 10 seconds. Through time-consuming interval distribution, we can know the execution time-consuming situation of SQL very clearly.
Monitor the number of physical connection creation and destruction, the number of logical connection applications and closures, the number of non-empty waiting, the hit rate of PSCache, etc.

Encryption of database password

Writing the database password directly in the configuration file is a bad behavior, which can easily lead to security problems. Both Druid Druiver and Druid Data Source support Password Callback.

SQL Execution Log

Druid provides different LogFilters that support Common-Logging, Log4j and JdkLog. You can select the appropriate LogFilter as needed to monitor the database access of your application.

Extend JDBC

If you have a programming requirement for the JDBC layer, you can easily write JDBC layer extension plug-ins through the Filter mechanism provided by Druid.

Druid provides an extended API for Filter-Chain mode on Druid Data Source and Proxy Driver, similar to Serlvet's Filter, which configures Filter to intercept JDBC method calls.

SQLParser

SQL Parser is an important part of Druid. It provides complete support for MySql, Oracle, Postgresql and SQL-92. It is a handwritten high-performance SQL Parser, which supports Visitor mode and makes it convenient to analyze the abstract syntax tree of SQL.

Simple SQL statements take less than 10 microseconds and complex SQL takes 30 microseconds.

The SQL Parser provided by Druid can intercept the SQL in JDBC layer for corresponding processing, such as defensive SQL injection (WallFilter), merge statistics without parameterization of SQL (merge Sql of StatFilter), SQL formatting, sub-database and sub-table.

http://blog.csdn.net/qq_30444003/article/details/50795116

Detailed configuration of Druid

To configure	Default value	Explain
name		The significance of configuring this property is that if there are multiple data sources, monitoring can be distinguished by name. If it is not configured, a name will be generated in the format of "DataSource-" + System.identityHashCode(this). In addition, configuring this property will not work in at least version 1.0.5, and forcing the name will be wrong. Details - click here.
url		The url of connecting database is different from different database. For example: <br/>mysql:jdbc: mysql://10.20.153.104:3306/druid2<br /> oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		User Name for Connecting to Database
password		The password to connect to the database. If you don't want your password written directly in the configuration file, you can use ConfigFilter. Look at this in detail.
driverClassName	Automatic recognition based on url	If you do not configure druid, you will automatically identify dbType according to the url, and then select the corresponding driverClassName.
initialSize	0	Number of physical connections established at initialization. Initialization occurs when the init method is displayed, or when the first getConnection is called
maxActive	8	Maximum number of connection pools
maxIdle	8	It's no longer in use, and it's useless to configure it.
minIdle		Minimum number of connection pools
maxWait		Gets the maximum waiting time for a connection in milliseconds. After configuring maxWait, fair locks are enabled by default and concurrency efficiency decreases. If necessary, unfair locks can be used by configuring the useUnfairLock attribute to true.
poolPreparedStatements	false	Whether to cache preparedStatement, or PSCache. PSCache has greatly improved the performance of database supporting cursors, such as oracle. It is recommended to close under mysql.
maxPoolPreparedStatementPerConnectionSize	-1	To enable PSCache, you must configure it to be greater than 0, and when it is greater than 0, pool Prepared Statements automatically triggers to be changed to true. In Druid, PSCache does not take up too much memory under Oracle. You can configure this value to be larger, for example, 100.
validationQuery		The sql used to check whether the connection is valid requires a query statement, commonly selected'x'. If validation Query is null, testOnBorrow, testOnReturn, and testWhileIdle will not work.
validationQueryTimeout		Unit: seconds, check whether the connection is valid timeout time. The void setQueryTimeout(int seconds) method of the jdbc Statement object is invoked at the bottom level
testOnBorrow	true	Validation Query is executed when requesting a connection to check if the connection is valid, and this configuration can degrade performance.
testOnReturn	false	Validation Query is performed when the connection is returned to check whether the connection is valid, and this configuration can degrade performance.
testWhileIdle	false	The recommended configuration is true, does not affect performance, and guarantees security. When applying for a connection, check if the idle time is longer than time Between Eviction Runs Millis, and perform validation Query to check if the connection is valid.
timeBetweenEvictionRunsMillis	1 minute (1.0.14)	There are two meanings: < br /> 1) The Destroy thread detects the connection interval and closes the physical connection if the connection idle time is greater than or equal to minEvictable IdleTime Millis. <br /> 2) Judgment basis of testWhileIdle, see the description of testWhileIdle attribute in detail
numTestsPerEvictionRun	30 minutes (1.0.14)	No longer in use, a Druid Data Source supports only one Eviction Run
minEvictableIdleTimeMillis		The longest time a connection remains idle without being expelled
connectionInitSqls		sql executed during physical connection initialization
exceptionSorter	Automatic recognition based on dbType	When the database throws some unrecoverable exceptions, the connection is discarded
filters		Attribute type is a string. Extension plug-ins are configured by aliases. The commonly used plug-ins are filter: stat < br /> log filter: log 4J < br /> wall for sql injection prevention.
proxyFilters		The type is List <com.alibaba.druid.filter.Filter>, if both filters and proxy filters are configured, it is a combination relationship, not a replacement relationship.

Druid source code

https://github.com/alibaba/druid/wiki

Frequent Druid Problems

https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98

Project practice

We use Spring boot + mybatis + Druid (official start) architecture

At present, there are four connection pools supported by default in Spring Book: dbcp, dbcp2, Tomcat and hikari.

Because Druid is not directly supported in Spring Bootz for the time being. Now Ali has officially optimized Spring Boot by writing a start, which we can rely on directly in the Spring Boot project.

Import dependency packages

<!-- Database Driver -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- Mybatis -->
<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>1.3.0</version>
</dependency>
<!--Mybatis Paging plugins pagehelper -->
<dependency>
	<groupId>com.github.pagehelper</groupId>
	<artifactId>pagehelper-spring-boot-starter</artifactId>
	<version>1.1.1</version>
</dependency>
<!-- Druid Connection pool package -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	<version>1.1.1</version>
</dependency>

Configuring data sources

Druid Start Github address

application.properties configuration information:

#https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter
spring.datasource.druid.url=jdbc:mysql://localhost:3306/ssb_test
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.username=root
spring.datasource.druid.password=root
# Initialization size, minimum, maximum
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
# Configuration to get the connection waiting timeout time
spring.datasource.druid.max-wait=60000
# How often is the configuration interval detected to detect idle connections that need to be closed in milliseconds?
spring.datasource.druid.time-between-eviction-runs-millis=60000
# Configure the minimum lifetime of a connection in the pool in milliseconds
spring.datasource.druid.min-evictable-idle-time-millis=300000
#Detecting whether the connection is valid sql
spring.datasource.druid.validation-query=SELECT 'x'
spring.datasource.druid.validation-query-timeout=60000
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
# Recommended closure under PSCache Mysql
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=-1
#spring.datasource.druid.max-open-prepared-statements= #Equivalent to the max-pool-prepared-statement-per-connection-size above

#mybatis
#Package name for entity scan
mybatis.type-aliases-package=com.xiaolyuh.domain.model
#Location of Mapper.xml
mybatis.mapper-locations=classpath*:/mybaits/*Mapper.xml
#Open MyBatis secondary cache
mybatis.configuration.cache-enabled=true

#pagehelper
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

Here we have the data source configured. Write a test class to see if the data source is valid.

Test class

package com.xiaolyuh;

import com.alibaba.fastjson.JSON;
import com.xiaolyuh.config.DruidDataSourceProperty;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.junit4.SpringRunner;

import javax.sql.DataSource;

@RunWith(SpringRunner.class)
@SpringBootTest
public class DataSourceTests {

    @Autowired
    ApplicationContext applicationContext;

    @Test
    public void testDataSource() throws Exception {
        // Get the configured data source
        DataSource dataSource = applicationContext.getBean(DataSource.class);
        // View Configuration Data Source Information
        System.out.println(dataSource.getClass().getName());
    }

}

Print logs:

com.alibaba.druid.pool.DruidDataSource
{"connectionProperties":"druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000","driverClassName":"com.mysql.jdbc.Driver","filters":"stat,wall,log4j","initialSize":5,"maxActive":20,"maxPoolPreparedStatementPerConnectionSize":-1,"maxWait":60000,"minEvictableIdleTimeMillis":300000,"minIdle":5,"password":"root","poolPreparedStatements":false,"testOnBorrow":false,"testOnReturn":false,"testWhileIdle":true,"timeBetweenEvictionRunsMillis":60000,"url":"jdbc:mysql://localhost:3306/ssb_test","useGlobalDataSourceStat":true,"username":"root","validationQuery":"SELECT 'x'"}
2017-07-02 11:29:04.183  INFO 340 --- [       Thread-2] o.s.w.c.s.GenericWebApplicationContext   : Closing org.springframework.web.context.support.GenericWebApplicationContext@11fc564b: startup date [Sun Jul 02 11:28:58 CST 2017]; root of context hierarchy

Configuration of Druid Monitoring

Now let's talk about the configuration of Druid monitoring:

application.properties configuration:

# Configure filters that are intercepted by monitoring statistics, and sql can not be counted after removing them.'wall'is used for firewall
spring.datasource.druid.filters=stat,wall,log4j

Use this filter configuration to turn on monitoring support. The attribute type is a string, which configures the extension plug-in by alias. The common plug-ins are:

filter:stat for monitoring statistics
Log filter:log4j
filter:wall to defend sql injection

WebStatFilter configuration

Add the following configuration to the application.properties file:

# WebStatFilter configuration, refer to Druid Wiki for instructions, configure _configure WebStatFilter
#Visit http://127.0.0.1:8080/druid after starting the project
#Whether StatFilter default value true is enabled
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
#The default session StatMaxCount is 1000
spring.datasource.druid.web-stat-filter.session-stat-max-count=1000
#Turn off session statistics
spring.datasource.druid.web-stat-filter.session-stat-enable=false
#Configure principalSessionName so that druid knows who the user of the current session is
#If you save non-string objects in session, you need to overload the toString method
spring.datasource.druid.web-stat-filter.principalSessionName=xxx.user
#If user information is stored in cookie s, you can configure principalCookieName so that druid knows who the current user is.
spring.datasource.druid.web-stat-filter.principalCookieName=xxx.user
#Druid version 0.2.7 started supporting profiles, configuring profileEnable to monitor sql lists of individual url calls.
spring.datasource.druid.web-stat-filter.profile-enable=false

StatViewServlet configuration