Druid database connection pool _druid database connection pool

Introduction to Druid

Druid First is a database connection pool. Druid is currently the best database connection pool, in terms of function, performance, extensibility, are more than other database connection pool, including DBCP, C3P0, BONECP, Proxool, JBoss DataSource. Druid has deployed more than 600 applications in Alibaba, a rigorous test of large-scale deployment over a year of production.

At the same time Druid is not just a database connection pool, it includes four parts:

Druid is a JDBC component that includes three parts:

Plug-in system based on Filter-chain mode.

Druiddatasource Efficient and manageable database connection pool.

Sqlparser

The function of Druid

1, replace DBCP and C3P0. Druid provides an efficient, powerful, scalable database connection pool.

2, can monitor the database access performance, the Druid built-in provides a powerful Statfilter plug-in, can the detailed statistics SQL execution performance, this is helpful for the online analysis database access performance.

3, the database password encryption. Writing the database password directly in the configuration file is bad behavior and can lead to security problems. Druiddruiver and Druiddatasource both support PasswordCallback.

4, SQL execution log, Druid provides a variety of logfilter, to support common-logging, log4j and Jdklog, you can choose the appropriate logfilter to monitor the database access you have applied.

5, extended JDBC, if you want to have the JDBC layer programming requirements, you can provide through the Druid filter mechanism, it is easy to write the JDBC layer extension.

Druid Introduction

Druid is Alibaba open source platform on the database connection pool implementation, it combines the advantages of C3P0, DBCP, Proxool db pool, while adding log monitoring, can be a good monitor DB pool connectivity and SQL performance, can be said to monitor the DB connection pool ( It is said to be the best connection pool, do not know the speed is no BONECP fast).

Configuration parameters

As with other connection pools, the Druid DataSource class is: Com.alibaba.druid.pool.DruidDataSource, the basic configuration parameters are as follows:

Configure Default Value Description name
The significance of configuring this property is that if there are multiple data sources, the monitoring can be distinguished by name.
If not configured, a name will be generated, in the format: "datasource-" + system.identityhashcode (This) jdbcurl
The URL of the connection database, different databases. 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 database password
The password to connect to the database. If you do not want the password to be written directly in the configuration file, you can use Configfilter. See here: Https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter Driverclassname automatically recognizes this item according to the URL is not worthy, If you do not configure Druid, the DbType is automatically identified based on the URL, and then the corresponding driverclassname (recommended configuration) InitialSize 0 to establish the number of physical connections when initializing. Initialization occurs when the display invokes the Init method, or when the first getconnection is maxactive 8 The maximum number of connection pools Maxidle 8 is no longer in use and has no effect on configuration Minidle
Minimum connection pool number maxwait
The maximum wait time, in milliseconds, to get the connection. When MAXWAIT is configured, the default is to enable fair locks, and concurrency is reduced, and if you need to, you can use an unfair lock by configuring the Useunfairlock property to True. Poolpreparedstatements false whether to cache PreparedStatement, or Pscache. Pscache is a great boost to database performance for cursors, such as Oracle. It is recommended to close under MySQL. MaxOpenPreparedStatements-1 to enable Pscache, you must configure greater than 0, and when greater than 0 o'clock, Poolpreparedstatements automatically trigger the modification to true. In Druid, there is no problem of excessive memory occupied by Oracle Pscache, which can be configured to a larger number, such as Validationquery
SQL that is used to detect the validity of a connection and requires a query statement. If Validationquery is Null,testonborrow, Testonreturn, Testwhileidle will not function. Testonborrow true to perform a validationquery detection connection when requesting a connection, this configuration can degrade performance. Testonreturn False when the connection is returned the Validationquery detection connection is valid and this configuration degrades performance Testwhileidle false recommends that the configuration be true, does not affect performance, and guarantees security. When requesting a connection, detect if the idle time is greater than timebetweenevictionrunsmillis, perform validationquery detection connection is valid. Timebetweenevictionrunsmillis
Has two meanings:
1 Destroy thread will detect the connection interval of 2) Testwhileidle judgment basis, detailed look at Testwhileidle attribute description Numtestsperevictionrun
No longer used, a druiddatasource only supports a evictionrun Minevictableidletimemillis

Connectioninitsqls
The SQL Exceptionsorter executed when the physical connection is initialized automatically recognizes that the connection is discarded when the database throws some unrecoverable exceptions filters
The property type is a string that configures the extension through an alias, and the common plug-ins are:
FILTER:LOG4J Defense SQL injection Filter:wall for FILTER:STAT logs for monitoring statistics proxyfilters

The type is LIST<COM.ALIBABA.DRUID.FILTER.FILTER>, and if both filters and Proxyfilters are configured, it is a combinatorial relationship, not a replacement relationship

How to use

DB data sources are used in 2 different ways, one is to write in code to create datassource through the new operator, and then set some connection properties, which is not described here; another is based on the spring configuration method, Then let spring's context load the configuration automatically (the following configuration file defaults to the Conf folder in the project root directory)

1. Property file: Application.properties (DataSource connection parameter)

Jdbc.driverclassname=com.mysql.jdbc.driver 
jdbc.url=jdbc:mysql://127.0.0.1:3306/test 
jdbc.username= Root 
jdbc.password=1qaz! QAZ

2. Spring configuration file: Spring-base.xml

<?xml version= "1.0" encoding= "UTF-8"?> <beans "xmlns=" xmlns: Xsi= "Http://www.w3.org/2001/XMLSchema-instance" xmlns:batch= "Http://www.springframework.org/schema/batch" xsi: schemalocation= "Http://www.springframework.org/schema/beans Http://www.springframework.org/schema/beans/sprin G-beans-4.0.xsd "> <bean id=" propertyconfigure "class=" Org.springframework.beans.factory.config.PropertyPlac Eholderconfigurer "> <property name=" Locations "> <list> <value>./conf/application.properti es</value> </list> </property> </bean> <bean id= "DataSource class=" COM.ALIBABA.D Ruid.pool.DruidDataSource "init-method=" Init "destroy-method=" close "> <property name=" driverclassname "value= "${jdbc.driverclassname}"/> <property name= "url" value= "${jdbc.url}"/> <property name= "username" value = "${jdbc.username}"/> <properTy name= "password" value= "${jdbc.password}"/> <!--configuration initialization size, MIN, max--> <property name= "InitialSize" value = "1"/> <property name= "Minidle" value= "1"/> <property name= "maxactive" value= "ten"/> <!--configuration obtained 
  Take connection wait timeout--> <property name= "maxwait" value= "10000"/> <!--how long does the configuration interval take to detect and detect idle connections that need to be closed, in milliseconds--> <property name= "Timebetweenevictionrunsmillis" value= "60000"/> <!--Configure the minimum time that a connection is to survive in the pool, in milliseconds--> <prop Erty name= "Minevictableidletimemillis" value= "300000"/> <property name= "Testwhileidle" value= "true"/> ;! --This recommendation is configured to TRUE to prevent the connection being fetched from being unavailable--> <property name= "Testonborrow" value= "true"/> <property name= "Testonreturn" "Value= false"/> <!--opens Pscache and specifies the size of pscache on each connection--> <property name= "Poolpreparedstatements" value= "True"/> <property name= "maxpoolpreparedstatementperconnectionsize" value= "/> <!--Configure the Submit method here by default

 Is true, you can not configure--> <property name= "Defaultautocommit" value= "true"/> <!--Verify that the connection is valid or not, different data configurations are different--> <property name= "V Alidationquery "value=" Select 1 "/> <property name=" Filters "value=" stat "/>" <property name= "Proxyfilte" RS "> <list> <ref bean=" Logfilter "/> </list> </property> </bean> & Lt;bean id= "Logfilter" class= "Com.alibaba.druid.filter.logging.Slf4jLogFilter" > <property name= " Statementexecutablesqllogenable "value=" false "/> </bean> </beans>

The Red callout section above performs monitoring for monitoring DB pool connections, followed by detailed instructions. Monitoring mode

1. Web Mode monitoring Configuration

<servlet> <servlet-name>DruidStatView</servlet-name> <servlet-class>com.alibaba.druid. support.http.statviewservlet</servlet-class> </servlet> <servlet-mapping> <servlet-name> Druidstatview</servlet-name> <url-pattern>/druid/*</url-pattern> </servlet-mapping> < Filter> <filter-name>druidWebStatFilter</filter-name> <filter-class> Com.alibaba.druid.support.http.webstatfilter</filter-class> <init-param> <param-name> 
  Exclusions</param-name> <param-value>/public/*,*.js,*.css,/druid*,*.jsp,*.swf</param-value> </init-param> <init-param> <param-name>principalSessionName</param-name> <param-valu e>sessioninfo</param-value> </init-param> <init-param> <param-name>profileenable</ Param-name> <param-value>true</param-value> &Lt;/init-param> </filter> <filter-mapping> <filter-name>druidwebstatfilter</filter-name > <url-pattern>/*</url-pattern> </filter-mapping>

Add the above servlet configuration to the project Web.xml. Then run Tomcat, browser input Http://IP:PROT/druid

You can open the Druid Monitor page.

2, log file monitoring

Druid provides a variety of log file monitoring commons-logging, log4j, and so on, where we mainly use SLF4J and logback for log monitoring configuration.

First you introduce SLF4J and logback-related jar files (download http://search.maven.org/from the MAVEN public repository)

<slf4j.version>1.7.7</slf4j.version>
<logback.version>1.1.2</logback.version>

<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-access</artifactId>
<version>${logback.version}</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>${logback.version}</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback.version}</version>
</dependency>

Next Configure Logback configuration file (./conf/logback.xml)

<configuration>

<appender name= "STDOUT" class= "Ch.qos.logback.core.ConsoleAppender" >
<layout class= "Ch.qos.logback.classic.PatternLayout" >
<pattern>%d{hh:mm:ss. SSS} [%thread]%-5level%logger{36}-%msg%n
</Pattern>
</layout>
</appender>

<appender name= "FILE" class= "Ch.qos.logback.core.FileAppender" >
<file>./logs/druid_info.log</file>
<layout class= "Ch.qos.logback.classic.PatternLayout" >
<pattern>%d{hh:mm:ss. SSS} [%thread]%-5level%logger{36}-%msg%n</pattern>
</layout>
<filter class= "Ch.qos.logback.classic.filter.ThresholdFilter" >
<level>debug</level>
</filter>
</appender>

<root level= "DEBUG" >
<appender-ref ref= "FILE"/>
</root>
</configuration>

The final thing is to write a test class to test

public class Testmain {

public static void Loadloggercontext () {
System.getproperties (). Put ("Logback.configurationfile", "./conf/logback.xml");
Loggercontext LC = (Loggercontext) loggerfactory.getiloggerfactory ();
Statusprinter.setprintstream (System.err);
Statusprinter.print (LC);
}

public static void Main (string[] args) {
try {
Loadloggercontext ();
Filesystemxmlapplicationcontext context = new Filesystemxmlapplicationcontext ("./conf/spring-base.xml");

catch (Exception e) {
System.out.println (e);
}
}
}

Note: Reference configuration for Druid:

<bean id= "DataSource" class= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "Close" > <property name= "url" value= "${jdbc_url}"/> <property name= "username" value= "${jdbc_user}"/&gt  
    ;  
   
    <property name= "Password" value= "${jdbc_password}"/> <property name= "Filters" value= "stat"/> <property name= "maxactive value="/> <property name= "InitialSize" value= "1"/> &LT;PR Operty name= "maxwait" value= "60000"/> <property name= "Minidle" value= "1"/> <property name= "t Imebetweenevictionrunsmillis "value=" 60000 "/> <property name=" Minevictableidletimemillis "value=" 300000 "/&gt  
   
    ;  
    <property name= "Validationquery" value= "select ' X '"/> <property ' name= "Testwhileidle" value= "true"/>  
       
    <property name= "Testonborrow" value= "false"/> <property "Name=" Testonreturn "false" value= ,;p roperty name= "poolpreparedstatements" value= "true"/> <property "name="   Size "value="/> </bean>

Above in the configuration above, usually you need to configure the URL, username, password, maxactive these items.
In Druiddatasource, you can not configure Driverclass, which is automatically identified by the URL. Druid can automatically identify more than 20 URLs, and common JDBC driver are included.

Migration from the DBCP is the most convenient, the Org.apache.commons.dbcp.BasicDataSource modified to com.alibaba.druid.pool.DruidDataSource just fine.