Implementation of Connection Pool (Java EE 8 + MySQL)

  1. Introduction of connection pool technology
    The common mainstream open source database connection pools are C3P0, DBCP, Tomcat Jdbc Pool, BoneCP, Druid, etc. the database connection pool technology introduced and implemented in this paper is DBCP.
    DBCP (Database Connection Pool): a Java database connection pool project developed by Apache, the Jakarta commons pool object pool mechanism, and the connection pool component used by Tomcat is DBCP. Using DBCP alone requires three packages: common-dbcp.jar, common pool.jar, and common collections.jar. Put the database connection in memory in advance. When an application needs to establish a database connection, it can directly apply for one in the connection pool and put it back when it is used up.
  2. Principle of connection pool technology
    The basic idea of connection pool is to store the database connection as an object in memory when the system is initialized. When the user needs to access the database, it is not to establish a new connection, but to take an established free connection object out of the connection pool. After use, the user does not close the connection, but puts the connection back into the connection pool for the next request access.
    The establishment and disconnection of connections are managed by the connection pool itself. At the same time, you can also control the initial number of connections in the connection pool, the number of upper and lower limits of connections, the maximum number of uses of each connection, the maximum idle time and so on by setting the parameters of the connection pool. It can also monitor the number and usage of database connections through its own management mechanism.
  3. Implementation of connection pool
    Implementation environment: JDK 13.0.2 + Java EE 8 + MySQL 5.7
    Using DBCP alone requires three packages: commons-dbcp.jar, commons-pool.jar, commons-collections.jar.
    Download link: commons-dbcp-1.4.jar, commons-pool-1.6.jar, commons-collections4-4.4.jar
    The directory structure of jar package, configuration file and DBCP implementation class is shown in the figure below

    Project structure - > libraries import jar package as shown in the figure below

    Profile implementation
# Database address
dbUrl=jdbc:mysql://127.0.0.1:3306/school?useSSL=FALSE&serverTimezone=UTC&characterEncoding=utf8
# Database user name
userName=root
# Database password
password=990818
# Database driven
driverName=com.mysql.jdbc.Driver
# Number of initial connections
initSize=2
# Maximum number of simultaneous connections
maxActive=1
# Maximum waiting time
maxWait=3000
# Maximum number of idle connections
maxIdle=2
# Minimum free connections
minIdle=0

DBCP implementation

package com.javaEE.code.class12;

import org.apache.commons.dbcp.BasicDataSource;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

// Database Connection Pool
public class DBCP {

    // Database connection pool
    private static BasicDataSource dbcp;

    //Manage connections for different threads
    private static ThreadLocal<Connection> tl;

    //Get database parameters through configuration file
    static {
        try {
            Properties properties = new Properties();

            InputStream inputStream = DBCP.class.getClassLoader().getResourceAsStream("com/javaEE/code/class12/Application.properties");

            properties.load(inputStream);
            inputStream.close();

            // Initialize connection pool
            dbcp = new BasicDataSource();

            // Database driver (Class.forName())
            dbcp.setDriverClassName(properties.getProperty("driverName"));
            // Database url
            dbcp.setUrl(properties.getProperty("dbUrl"));
            // Database user name
            dbcp.setUsername(properties.getProperty("userName"));
            // Database password
            dbcp.setPassword(properties.getProperty("password"));
            // Number of initial connections
            dbcp.setInitialSize(Integer.parseInt(properties.getProperty("initSize")));
            // Maximum connections allowed for connection pool
            dbcp.setMaxActive(Integer.parseInt(properties.getProperty("maxActive")));
            // Maximum waiting time
            dbcp.setMaxWait(Integer.parseInt(properties.getProperty("maxWait")));
            // Minimum number of idle
            dbcp.setMinIdle(Integer.parseInt(properties.getProperty("minIdle")));
            // Maximum number of idle
            dbcp.setMaxIdle(Integer.parseInt(properties.getProperty("maxIdle")));
            // Initialize thread local
            tl = new ThreadLocal<Connection>();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Get a connection through the connection pool
    public static Connection getConnection() throws SQLException {
        Connection conn = dbcp.getConnection();
        tl.set(conn);
        return conn;
    }

    // Return connection, not closed
    public static void closeConnection() {
        try {
            Connection conn = tl.get();
            if (conn != null) {
                conn.close();
                tl.remove();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

When establishing a database connection

// Get connection
Connection connetion = DBCP.getConnection();

When returning a database connection

// Return database connection
DBCP.closeConnection();
Published 5 original articles, praised 0, visited 73
Private letter follow

Tags: Database Java JDBC MySQL

Posted on Thu, 12 Mar 2020 05:16:32 -0400 by wpt394