Use and Operation of Database Connection Pool (DBCP)

When using SQL statements to operate databases in java, it is often necessary to load the driver first, establish a connection, and then execute some SQL after successful connection. After executing SQL, it is necessary to consider whether to close the connection. When there is another SQL to execute, you need to re-establish the connection, and then do these steps. There are at least two things to consider here. The first is that connection establishment takes a certain amount of time, which affects the efficiency of SQL execution. The second is that if the connection is maintained and not closed, new connections are needed to execute SQL for other content at this time, which results in countless connections, leading to system ruin due to excessive connections.
The database connection pool maintains a certain number of database connections in a POOL pool:
1. When the program needs to operate the database, take out a connection from the pool and allocate it.
2. When the program finishes executing SQL, put the connection back into the connection pool.
3. The database connection pool initially has a certain number of connections. When there are many programs that need to execute the database, when the connections in the connection pool are not enough, new connections are established online according to the number of connections set up and allocated to the program.
4. When fewer connections are used, the connection pool closes more than that number of connections depending on the number of connections that are maintained free.
5. When connections exceeding the number of wires on the connection pool are used and there are programs that need to connect, the connection can be obtained by waiting for a set timeout, or by waiting indefinitely until there is a connection in the connection pool.

Catalog

development environment

• java development toolkit, jdk-8u144-windows-x64.exe
• java develops IDE, eclipse-jee-neon-2-win32-x86_64
• DBCP tool jar package, commons-dbcp2-2.1.1.jar
• POOL2 tool jar package, commons-pool2-2.4.2.jar
• MSSQL JDBC tool jar package, sqljdbc4-3.0.jar
• logging depends on the tool jar package. commons-logging-1.2.jar
• Project Engineering Source Code, github Library

Development process

1. Establish database connection pool DBCP;
2. Configure connection pool attributes, through dbcpconfig.properties configuration, but also through code configuration;
3. Take the connection from the connection pool;
4. Execute SQL as usual.
5. close connection, not closing the connection, but putting the connection back into the connection pool.

source code

package com.demo;
/**
 * @created time:2017 11 August, 2000, 2:18:12 p.m.
 * @author:chixh
 * @file:DataPool.java
 */


import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;


public class DataPool {
    public static void main(String[] args)throws Exception  {
        poolMode();
    }
    // Connection pool mode
    public static void poolMode() throws Exception {
        long s = System.currentTimeMillis();
        String sql = "insert into test values(1,'iPhone8',8888.88)";

        BasicDataSource ds = null;
        Connection conn = null;     
        Statement st = null;
        Properties pro = new Properties();
        //Loading database connection pool configuration
        pro.load(new BufferedInputStream(new FileInputStream("./dbcpconfig.properties")));
        //Establish connection pool
        ds = BasicDataSourceFactory.createDataSource(pro);

        //You can also set connection pool properties here
        //Setting the initial connection pool size
        ds.setInitialSize(8);
        //Setting the maximum capacity of connection pool
        ds.setMaxTotal(20);

        for (int j = 0; j < 8; j++) {
            //Getting a connection here is not about setting up a connection, it's about getting a connection from the connection pool.
            conn = ds.getConnection();
            st = conn.createStatement();
            for (int i = 0; i < 1000; i++) {
                st.executeUpdate(sql);
            }
        }
        System.out.println("expensive time:" + (System.currentTimeMillis() - s) + "ms");// (8,100,1199ms)(1466ms)
        //The close connection here is not to close the connection, but to put the connection back into the connection pool.
        conn.close();
    }


}

Database connection pool configuration, where the database server is localhost, username is sa, and password is root

#####dbcpconfig.properties
#########DBCP config
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433;databaseName=test
databaseName=test
username=sa
password=root

#connection pool initial size
initialSize=8
#connection pool max connect num
maxActive=8
#connection pool max idle num
maxIdle=8
#connection pool min idle num
minIdle=8
#max wait time
maxWait=60000

#connect other properties
connectionProperties=useUnicode=true;characterEncoding=utf8
#default auto commit
defaultAutoCommit=true
#default read only
defaultReadOnly=
#driver default TransactionIsolation
#NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=REPEATABLE_READ
#########DBCP config

// Table structure of test.sql database

--localhost 

use test

CREATE TABLE "test" (
    "id" INT NOT NULL,
    "phoneName" VARCHAR(50) NULL,
    "price" DECIMAL NULL
);