Simple use of the DBCP database connection pool

0, DBCP Introduction

DBCP Database connection pool is a Java Connection pool project on Apache. DBCP the connection pool in advance with the database to establish some connections in memory (that is, the connection pool), the application needs to establish a database connection directly to the slave pool to request a connection to use, after the use of the connection pool to reclaim the connection, so as to achieve connectivity reuse, reduce resource consumption.

1, DBCP depends on the jar package (the following example is based on the following Jar package version)

Commons-dbcp2-2.1.1.jar Commons-logging-1.2.jar Commons-pool2-2.4.2.jar

2. DBCP Use Example

　　Is a Java project created in Eclipse, using the DBCP related jar package, MySQL JDBC driver jar package, JUNIT4.

The config directory is created under the SRC sibling directory, which is used to store the DBCP configuration file.

The "note" class Dbcputil.java is not used in the following example.

　　

1) dbcp configuration file Dbcp.properties

####### #DBCP配置文件 ##########

#驱动名
Driverclassname=com.mysql.jdbc.driver
#url
Url=jdbc:mysql://127.0.0.1:3306/mydb
#用户名
Username=sa
#密码
password=123456
#初试连接数
Initialsize=30
#最大活跃数
Maxtotal=30
#最大idle数
maxidle=10
#最小idle数
Minidle=5
#最长等待时间 (MS)
maxwaitmillis=1000
#程序中的连接不使用后是否被连接池回收 (This version will use Removeabandonedonmaintenance and Removeabandonedonborrow)
#removeAbandoned =true
Removeabandonedonmaintenance=true
Removeabandonedonborrow=true
#连接在所指定的秒数内未使用才会被删除 (seconds) (configured to 1 seconds for mate Tester)
Removeabandonedtimeout=1

2) Create a class that initializes the DBCP Kcydbcputil.java

  Package dbcp;  Import Java.io.FileInputStream;  Import java.io.IOException;  Import java.sql.Connection;  Import java.sql.SQLException;    Import java.util.Properties;  Import Javax.sql.DataSource;  Import Org.apache.commons.dbcp2.BasicDataSourceFactory; /** * DBCP Configuration class * @author SUN * * public class Kcydbcputil {private static properties Properties = New Propertie     S ();     private static DataSource DataSource;               Load dbcp configuration file static{try{FileInputStream is = new FileInputStream ("Config/dbcp.properties");         Properties.load (IS);         }catch (IOException e) {e.printstacktrace ();         } try{DataSource = Basicdatasourcefactory.createdatasource (properties);         }catch (Exception e) {e.printstacktrace ();         }}//Get a connection from the connection pool public static Connection getconnection () {Connection Connection = null; try{connection = Datasource.getconNection ();         }catch (SQLException e) {e.printstacktrace ();         } try {Connection.setautocommit (false);         } catch (SQLException e) {e.printstacktrace ();     } return connection;     } public static void Main (string[] args) {getconnection (); } }

3) Create a class Dbconn.java that uses JDBC to get a database connection (for comparison with DBCP connection pool)

Package dbcp;

    Import java.sql.Connection;  Import Java.sql.DriverManager;    public class Dbconn {      private static Connection conn = null;            Gets a database connection public     static Connection getconnection () {         try {             class.forname ("Com.mysql.jdbc.Driver");             Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());            String Dburl = "Jdbc:mysql://127.0.0.1:3306/mydb";             conn = Drivermanager.getconnection (Dburl, "sa", "123456");            System.out.println ("======== database connection succeeded ========");         } catch (Exception e) {             e.printstacktrace ();//            System.out.println ("======== database connection failed ========");             return null;         }         return conn;     } }

4) Create a test class Dbcptest.java

The test class uses the 3 method to insert 2000 data into the same table in the database, emptying the table each time before inserting the data, and comparing the results.

3 Insert data in the following method:

(1) A connection is created each time a piece of data is inserted, and when the data is inserted, the connection is closed;

(2) using the DBCP connection pool, each time a data is inserted, a connection is obtained from the DBCP connection pool, and after the data is inserted, the connection is managed by the DBCP connection pool;

(3) Create a connection before inserting the data, 2000 data all use the connection, and after 2000 data is inserted, close the connection.

  Package dbcp;  Import java.sql.Connection;  Import java.sql.SQLException;    Import java.sql.Statement;   Import Org.junit.Test;          public class Dbcptest {//test, create a new connection @Test public void Testwritedbbyeveryconn () throws exception{before writing a single piece of data.         for (int i = 0; i < i++) {writedbbyeveryconn (i);              } System.out.println ("Done");  }//test, use connection pooling, get a connection from the connection pool before each write of data @Test public void testwritedbbydbcp () throws exception{for (int i = 0; I < 2000;         i++) {writedbbydbcp (i);     } System.out.println ("Done");  }//test, build only one connection, write all data @Test public void Testwritedbbyoneconn () throws exception{Connection conn =         Dbconn.getconnection ();         Statement stat = conn.createstatement ();        for (int i = 0; i < i++) {writedbbyoneconn (I, stat);         } conn.close ();     System.out.println ("Done"); }//Do not use connection pooling to write the database, each write a data creationA connection public void Writedbbyeveryconn (int data) {String sql = ' INSERT INTO DBCP values ("+ Data +") ";         Connection conn = Dbconn.getconnection ();             try{Statement stat = conn.createstatement ();                     Stat.executeupdate (SQL);         }catch (Exception e) {e.printstacktrace ();                 }finally{try {conn.close ();             } catch (SQLException e) {e.printstacktrace (); }}}//Do not use connection pool write database, only one connection, write all data public void writedbbyoneconn (int data, Statement STA          t) {String sql = "INSERT into DBCP values (" + Data + ")";                     try{stat.executeupdate (SQL);         }catch (Exception e) {e.printstacktrace (); }//write database by DBCP connection pool public void writedbbydbcp (int data) {String sql = ' INSERT into DBCP values (          "+ Data +") ";            try { Connection conn = Kcydbcputil.getconnection ();             Statement stat = conn.createstatement ();             Stat.executeupdate (SQL);             Conn.commit ();         Conn.close ();           } catch (SQLException e) {e.printstacktrace (); }}} 

The test results are as follows:

(1) A connection is created each time a piece of data is inserted, and when the data is inserted, the connection is closed. Takes 158.318 seconds

　　　

(2) using the DBCP connection pool, a connection is obtained from the DBCP connection pool each time a data is inserted, and the connection is managed by the DBCP connection pool when the data is inserted. Takes 122.404 seconds

(3) Create a connection before inserting the data, 2000 data all use the connection, and after 2000 data is inserted, close the connection. Takes 117.87 seconds

By comparing the results, it is very time consuming to insert 2000 data into the same table, creating a new connection before inserting a single piece of data, and using the DBCP connection pool and using the same connection operation will be time-consuming and close to each other.

3. Related issues

　　1) in the application, after a database connection is used, dbcp how the connection pool manages the connection.

In two cases:

(1) The application actively closes the connection, that is, the 79th row in Dbcptest.java conn.close ();

Instead of manually shutting down the connection, the connection is returned to the DBCP connection pool, which is managed by the connection pool. The database connection is submitted to the DBCP connection pool, which is displayed after the connection is exhausted.

(2) The connection is not closed in the application, and the 79th line in Dbcptest.java Conn.close () is commented out

This situation DBCP configuration items in configuration file dbcp.properties (note the JAR package version, use the Removeabandoned=true configuration item in the lower version)

Removeabandonedonmaintenance=true
Removeabandonedonborrow=true

Removeabandonedtimeout=1

will work,Removeabandonedonmaintenance=true and removeabandonedonborrow=true . Indicates that the DBCP connection pool is automatically managed by the connection that is used in the program,removeabandonedtimeout=1 indicates that if a connection is used in the program, if it is not used again within 1 seconds, the connection pool is reclaimed by the DBCP connection ( Typically removeabandonedtimeout is not configured for 1, which is used here for testing purposes.

(3) Verifying removeabandonedonmaintenance=true,removeabandonedonborrow=true and the role of removeabandonedtimeout=1 configuration items

Modify the WRITEDBBYDBCP (int data) method of the test class Dbcptest.java to read as follows:

      Write database by DBCP connection pool public      void writedbbydbcp (int data) {           String sql = ' INSERT INTO DBCP values ("+ Data +") ";           try {              Connection conn = Kcydbcputil.getconnection ();                Statement stat = conn.createstatement ();              Stat.executeupdate (SQL);              Conn.commit ();            conn.close ();         } catch (SQLException e) {                e.printstacktrace ();           }     }

The TESTWRITEDBBYDBCP () method is re-executed with the following results:

The visible writedbbydbcp (int data) method is the same as before the modification, which means that after the connection is used, it is managed by the DBCP connection pool.

If you modify the configuration item removeabandonedtimeout=180, which means that a connection is exhausted, it waits 180 seconds after 180 seconds before it is reclaimed by the DBCP connection pool. Re-executes the TESTWRITEDBBYDBCP () method, performing an error after a period of time (cannot get a connection, pool error Timeout waiting for idle object), as follows:

　　　　

At this point, the Query data table, found exactly 30 data inserted, as follows:

　　　　

This indicates an error when inserting the 31st data, because there is no connection available in the connection pool. This is because the DBCP connection pool initializes the number of connections,removeabandonedtimeout is set to 180 seconds, so 30 connections run out, the program is not running to 180 seconds, the program used to complete the connection has not been DBCP connection pool recycling, So there is no connection available in the DBCP connection pool, only the 31st data times are inserted in the wrong way.

Simple use of the DBCP database connection pool