Database connection pool

Label (Space Separation): jdbc

Necessity of database connection pool

Problems of Traditional Models

When developing database-based web programs, the traditional pattern basically follows the following steps:

• Establish database connection in main program (such as servlet, beans)
• Perform sql operations
• Disconnect the database connection

There are some problems in the development of this model:

• Common JDBC database connections are acquired by DriverManager. Every time a Connection is established to the database, the Connection is loaded into memory, and then the user name and password are verified (it takes 0.05s to 1s). When database Connection is needed, one is required from the database, and the Connection is disconnected after execution. This way will consume a lot of resources and time. The Connection resources of database are not well reused. If hundreds or even thousands of people are online at the same time, frequent database Connection operations will occupy a lot of system resources, and even cause server crash.

• For each database connection, it must be disconnected after use. Otherwise, if the program fails to close due to an exception, it will lead to memory leak in the database system and eventually lead to restart of the database.

• This kind of development can not control the number of connection objects created, and system resources will be allocated without consideration. If there are too many connections, it may also lead to memory leaks and server crashes.

Database connection pool

In order to solve the problem of database connection in traditional development, database connection pool technology can be used.

The basic idea of database connection pool is to establish a "buffer pool" for database connection. Put a certain number of connections in the buffer pool in advance. When database connections need to be established, just take one from the "buffer pool" and put it back after use.

The database connection pool is responsible for allocating, managing and releasing database connections, which allows applications to reuse an existing database connection rather than re-establish it.

The database connection pool will create a certain number of database connections in the connection pool when it is initialized. The number of these database connections is determined by the minimum number of database connections. Whether or not these database connections are used, the connection pool will always guarantee at least that many connections are available. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections an application requests to the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

Working principle

Two Open Source Database Connection Pools

JDBC's database connection pool is represented by javax.sql.DataSource. DataSource is only an interface, which is usually implemented by the server (Weblogic, WebSphere, Tomcat), and also provided by some open source organizations:

• DBCP database connection pool
• C3P0 database connection pool

DataSource is commonly referred to as a data source, which includes connection pool and connection pool management. It is also customarily referred to as connection pool.

DBCP Data Source

DBCP is an open source connection pool implementation under the Apache Software Fund, which relies on another open source system under the organization: Common-pool

If you need to use this connection pool, you should add the following two jar files to the system:
Commons-dbcp.jar: Implementation of connection pool
Commons-pool.jar: Dependency libraries for connection pool implementations

Tomcat's connection pool is implemented using this connection pool. The database connection pool can be integrated with the application server or used independently by the application.

Using DBCP database connection pool

Using DBCP database connection pool steps:

1. Import the jar package (two), depending on the Pooljar package
2. Create a database connection pool
3. Specify the required attributes for the data source instance
4. Getting database connections from data sources

@Test
    public void testDBCP() throws SQLException {
       BasicDataSource dataSource = null;
       //1. Create an instance of DBCP data source
       dataSource = new BasicDataSource();
       //2. Specify the required attributes for the data source instance
       dataSource.setUsername("root");
       dataSource.setPassword("1234");
       dataSource.setUrl("jdbc:mysql://localhost:3306/tables");
       dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        //3. Getting database information from data sources
        Connection connection = dataSource.getConnection();
        System.out.println(connection.getClass());
    }

Specify some optional attributes of the data source:

        //1. Specify the number of initialized connections in the database connection pool
        dataSource.setInitialSize(10);
        //2. Specify the maximum number of connections: the maximum number of free connections saved in the database connection pool
        dataSource.setMaxIdle(50);
        //3. Specify the minimum number of connections: the minimum number of free connections saved in the database connection pool
        dataSource.setMinIdle(5);
        //4. The longest waiting time for a database connection pool to allocate a connection, in milliseconds, will throw an exception
        dataSource.setMaxWaitMillis(1000*5);

Loading configuration files in factory mode

1. Load the properties configuration file of dbcp: The keys of the key-value pairs in the configuration file come from the properties of BasocDataSource.
2. Call the createDataSource method of BasicDataSourceFactory to create a DataSource instance
3. Getting database connections from DataSource instances

public void testDBCPWithDataSourceFactory() throws Exception {

        Properties properties = new Properties();
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("dbcp.properties");
        properties.load(inputStream);
        DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
        System.out.println(dataSource.getConnection());
        BasicDataSource basicDataSource = (BasicDataSource) dataSource;
        System.out.println(basicDataSource.getMaxWaitMillis());

    }

username = root
password = 1234
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///tables

initialSize = 10
maxIdle = 30
minIdle = 5
maxWaitMillis = 5000

C3P0 Data Source

C3p0 data source is a database connection pool which we often use. There are two ways: manual loading and configuration file. We usually use configuration files to load. Note: We want to import two jar packages: c3p0-0.9.5.2.jar and mchange-commons-java-0.2.11.jar

Manual loading

Fast manual loading of C3P0 data source through DEOM on QuickStart document of C3P0

@Test
    public void testC3P0() throws PropertyVetoException, SQLException {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
        cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/tables" );
        cpds.setUser("root");
        cpds.setPassword("1234");
        System.out.println(cpds.getConnection());
    }

Loading by configuration file

1. Create C3P0-config.xml file, refer to Appendix B** (Note: c3p0 configuration file should be placed under src folder**)
2. Create an instance of ComboPooled DataSource
3. Getting database connections from DataSource instances

C3P0-config.xml file:

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

    <named-config name="helloc3p0">

        <! -- Specifies the basic attributes for connecting data sources - >
        <property name="user">root</property>
        <property name="password">1234</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///tables</property>

        <! - If the number of connections in the database is insufficient, how many connections should be applied to the database server at a time - >
        <property name="acquireIncrement">5</property>
        <! - Number of connections when initializing the database connection pool - >
        <property name="initialPoolSize">5</property>
        <! - Minimum number of database connections in the database connection pool - >
        <property name="minPoolSize">5</property>
        <! - The largest number of database connections in the database connection pool - >
        <property name="maxPoolSize">10</property>

        <! - Number of Statement s that can be maintained by C3P0 database connection pool - >
        <property name="maxStatements">20</property>
        <! - Number of Statement Objects that can be used at the same time for each connection - >
        <property name="maxStatementsPerConnection">5</property>

    </named-config>

</c3p0-config>

Automatic loading code:

@Test
    public void testC3poWithConfigFile() throws Exception{
        DataSource dataSource = 
                new ComboPooledDataSource("helloc3p0");  

        System.out.println(dataSource.getConnection()); 

        ComboPooledDataSource comboPooledDataSource = 
                (ComboPooledDataSource) dataSource;
        System.out.println(comboPooledDataSource.getMaxStatements()); 
    }

Refactoring JDBCTools file

In a project, having a database connection pool is enough, so it only takes one instantiation, so we define static objects and write static blocks of code.

When closing the Connection object of the database connection pool in the relase() method, it is not really closing, but returning the database connection to the database connection pool.

C3P0

In JDBCTools:

//The database connection pool should be initialized only once. 
    static{
        dataSource = new ComboPooledDataSource("helloc3p0");
    }

    public static Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }

dbcp

In JDBCTools:

//The database connection pool should be initialized only once.
    static {
        //dataSource = new ComboPooledDataSource("helloc3p0");
        Properties properties = new Properties();
        InputStream inputStream = JDBCTools.class.getClassLoader().getResourceAsStream("dbcp.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }

test

@Test
    public void testJDBCTools() throws Exception {
        Connection connection = JDBCTools.getConnection();
        System.out.println(connection);
    }