Connection Pooling---Druid, c3p0, DBCP Learning

Connection pool

is a technique for creating and managing a pooled pool of connections that are ready to be used by any thread that needs them. Database connectivity is a critical, expensive, and limited resource that uses database connection pooling to improve the performance of database connections. When a thread needs JDBC to operate on a database, it requests a connection from the pool, and when the thread finishes using the connection, it returns the connection to the connection pool, ready to be used by other threads. When the connection is "loaned out" from the pool, it is used exclusively by the thread that requested it. From a programmatic point of view, this is the same as when a user's thread calls Drivermanager.getconnection () whenever a JDBC connection is needed, using a connection pooling technique that can end a thread by using a new or existing connection.

DBCP Connection Pool

DBCP is an open source connection pool implementation under the Apache Software fund, and to use the DBCP data source, the application should add the following two jar files to the system:

Commons-dbcp.jar: Implementation of connection pooling

Commons-pool.jar: Dependency libraries for connection pooling implementations

The connection pool for Tomcat is implemented with this connection pool. The database connection pool can be used either in combination with the application server or independently by the application.

Step one: Add the DBCP configuration file under the class directory: Dbcp.ini

#连接设置
Driverclassname=com.mysql.jdbc.driver
Url=jdbc:mysql://127.0.0.1:3306/test?useunicode=true&characterencoding=utf-8
Username=root
Password=root
#<!--Initialize the connection --
initialsize=10
#最大连接数量
maxactive=50
#<!--Maximum idle connection --
maxidle=20
#<!--Minimum idle connection --
minidle=5
#<!--Timeout wait time in milliseconds of 60000 milliseconds/1000 equals 60 seconds--
maxwait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样: [attribute name =property;]
#注意: The two properties of "User" and "password" are explicitly passed, so there is no need to include them here. 
Connectionproperties=useunicode=true;characterencoding=utf-8
#指定由连接池所创建的连接的自动提交 (auto-commit) state. 
defaultautocommit=true
#driver Default Specifies the read-only (read-only) state of the connection created by the connection pool. 
#如果没有设置该值, the "setreadonly" method will not be called. (Some drivers do not support read-only mode, such as: Informix)
defaultreadonly=
#driver default Specifies the transaction level (transactionisolation) of the connection created by the connection pool. 

Step Two: Create a connection pool in the static code block of the tool class that gets the database connection

In Java, writing a database connection pool requires implementing the Javax.sql.DataSource interface, and each database connection pool is an implementation of that interface.

Public class DBCP {

    private static DataSource Mydatasource=null;
Create a connection pool in a static code block
    static{
        Properties Pro=new properties ();
        try {
Loading the JDBC configuration file
            Pro.load (DBCP.class.getClassLoader (). getResourceAsStream ("Dbcp.ini"));
Create a data source
            Mydatasource= Basicdatasourcefactory.createdatasource (PRO);
        } catch (Exception e) {
            E.printstacktrace ();
        }
    }
Get a database connection from the data source
    public static Connection getconnection () {
        Connection Conn=null;
        try {
            Conn= mydatasource.getconnection ();
        } catch (SQLException e) {
            E.printstacktrace ();
        }
        Return conn;
    }
}

Step three: Release the database connection and get the connection in the app.

C3P0 Connection Pool

C3P0 is an open source JDBC connection pool that implements the data source and Jndi bindings, and supports the standard extensions of the JDBC3 specification and JDBC2. C3P0 is generally used with a frame such as hibernate,spring, and of course it can be used alone.

DBCP does not automatically reclaim idle connections, C3P0 has the ability to automatically reclaim idle connections.

You need to import C3p0.jar using C3P0, and if you are working with an Oracle database, you also need to import C3p0-oracle-thin-extras-pre1.jar.

Step 1:

Add the C3P0 configuration file under the class directory: C3p0-config.xml

<?xml version= "1.0" encoding= "UTF-8"?>
<c3p0-config>

<!--c3p0 Default (default) configuration- -
<!--If in code "Combopooleddatasourceds = new Combopooleddatasource ();" Writing this means using the default (default) configuration information for C3P0 to create the data source--

<default-config>
<property name= "automatictesttable" >con_test</property>

<property name= "Checkouttimeout" >30000</property>

<property name= "Idleconnectiontestperiod" >30</property>

<property name= "initialpoolsize" >10</property>

<property name= "MaxIdleTime" >30</property>

<property name= "maxpoolsize" >100</property>

<property name= "minpoolsize" >10</property>

<property name= "maxstatements" >200</property>

</default-config>

 <!--c3p0 named configuration,--> 

<!--If in the code "Combopooleddatasourceds = new Combopooleddatasource (" MySQL "); This means that using the name is the configuration information for MySQL to create the data source--

<named-config name= "MySQL" >

<property name= "Driverclass" >com.mysql.jdbc.Driver</property>

<property name= "Jdbcurl" >jdbc:mysql://localhost:3306/test?useunicode=true&amp;characterencoding= Utf-8
</property>

<property name= "user" >root</property>

<property name= "password" >root</property>

<property name= "acquireincrement" >5</property>

<property name= "initialpoolsize" >10</property>

<property name= "minpoolsize" >5</property>

<property name= "maxpoolsize" >30</property>

</named-config>
   
</c3p0-config>

Step Two: Create a connection pool in the static code block of the tool class that gets the database connection

Package com.wanyu.db;

import Com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.SQLException;

/**
* Created by Samsung on 2017/10/13.
 */
Public class C3p0 {
Public static Combopooleddatasource Cs=null;
Static {
Create a data source by reading the C3P0 configuration file using a named configuration
cs=new combopooleddatasource ("MySQL");
    }
Public static Connection getconnection () {
Connection conn=null;
try {
Get a database connection from the data source
conn=cs.getconnection ();
} catch (SQLException e) {
e.printstacktrace ();
        }
return conn;
    }
}

Step three: Release the connection and get the connection in the app.

Druid Connection Pool

Druid First is a database connection pool. Druid is currently the best database connection pool, in terms of functionality, performance, scalability, and more than other database connection pools, including DBCP, C3P0, BONECP, Proxool, JBoss DataSource.

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

2, can monitor database access performance, Druid built-in provides a powerful statfilter plug-in, the ability to detailed statistics of SQL execution performance, which is useful for online analysis of database access performance.

3, Database password encryption. Write the database password directly in the configuration file, this is bad behavior, easy to lead to security issues. Both Druiddruiver and Druiddatasource support PasswordCallback.

4, SQL execution log, Druid provides different logfilter, can support common-logging, log4j and Jdklog, you can select the appropriate logfilter as needed to monitor the database access of your application.

5, extended JDBC, if you want to have the JDBC layer programming requirements, can be provided by the Druid filter mechanism, it is convenient to write the JDBC layer extension.

The Druid.jar package needs to be added using Druid.

Step One: Add the Druid configuration file under the class directory: Druid.ini

#mysql数据库
Url=jdbc:mysql://127.0.0.1:3306/test?useunicode=true&characterencoding=utf-8
Username=root
Password=root
#filters =stat
#最大连接数量
maxactive=2000
#初始化连接数量
initialsize=400
#超时等待时间以毫秒为单位
maxwait=3000
#最小空闲连接
minidle=5
#校验连接池中限制时间超过minEvictableIdleTimeMillis的连接对象
timebetweenevictionrunsmillis=6000
#连接在池中保持空闲而不被空闲连接回收器线程 (if any) the minimum time value of the collection, in milliseconds
#minEvictableIdleTimeMillis =
#SQL查询, used to verify the connection taken out of the connection pool before the connection is returned to the caller
Validationquery=select now ();
#指明连接是否被空闲连接回收器 (if any) for inspection.
#如果检测失败, the connection is removed from the pool.
testwhileidle=true
#指明是否在从池中取出连接前进行检验, if the test fails, the connection is removed from the pool and an attempt is taken to remove the other.
Testonborrow=false
#指明是否在归还到池中前进行检验
Testonreturn=false
#poolPreparedStatements =true
#maxPoolPreparedStatementPerConnectionSize =20

Step Two: Create a connection pool in the static code block of the tool class that gets the database connection

Package com.wanyu.db;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import Javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
* Created by Samsung on 2017/10/13.
 */
Public class Druid {
To create a connection pool object
private static DataSource mydatasource=null;
Static {
Properties Pro=new properties ();
try {
Load the Druid configuration file
Pro.load (Druid.class.getClassLoader (). getResourceAsStream ("Druid.ini"));
Get Data source
mydatasource= Druiddatasourcefactory.createdatasource (pro);
} catch (Exception e) {
e.printstacktrace ();
        }
    }
Public static Connection getconnection () {
Connection conn=null;
try {
Get a database connection from the data source
conn=mydatasource.getconnection ();
} catch (SQLException e) {
e.printstacktrace ();
        }
return conn;
    }
}

Step three: Release the connection and get the database connection in the app.

Connection Pooling---Druid, c3p0, DBCP Learning