preface

Better manage the connection with the database, avoid memory leakage and server crash due to too many connections, and reduce resource consumption.

1, Database connection pool

1. The necessity of JDBC database connection pool

• When developing web programs based on database, the traditional mode basically follows the following steps:

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

• Problems in the development of this model:

  • Ordinary JDBC database connections are obtained using DriverManager. Each time a Connection is established to the database, the Connection must be loaded into memory, and then the user name and password must be verified (it takes 0.05s ~ 1s). When you need a database Connection, ask for one from the database and disconnect it after execution. This way will consume a lot of resources and time** The Connection resources of the database have not been well reused** If hundreds or even thousands of people are online at the same time, frequent database Connection operation 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 exceptions, it will lead to memory leakage in the database system and eventually restart the database. (recall: what is Java's memory leak?)
  • This development cannot 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 leakage and server crash.

2. Database connection pool technology

• 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 connections. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, just take one from the buffer pool and put it back after use.

• Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one.

• During initialization, the database connection pool will create a certain number of database connections into the connection pool. The number of these database connections is set by the minimum number of database connections. No matter whether these database connections are used or not, the connection pool will always ensure that there are at least so many connections. 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 requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.
  

• working principle:

• Advantages of database connection pool technology

  1. Resource reuse

  Because database connections are reused, frequent creation and release of connections can cause a lot of performance overhead. On the basis of reducing system consumption, on the other hand, it also increases the stability of system operation environment.

  2. Faster system response

  During the initialization of the database connection pool, several database connections have often been created and placed in the connection pool for standby. At this time, the initialization of the connection has been completed. For business request processing, the existing available connections are directly used to avoid the time overhead of database connection initialization and release process, so as to reduce the response time of the system

  3. New means of resource allocation

  For systems where multiple applications share the same database, the maximum number of available database connections of an application can be limited through the configuration of database connection pool at the application layer to avoid an application monopolizing all database resources

  4. Unified connection management to avoid database connection leakage

  In the more perfect implementation of database connection pool, the occupied connections can be forcibly recovered according to the pre occupation timeout setting, so as to avoid the possible resource leakage in the conventional database connection operation.

3. Multiple open source database connection pools

• The database connection pool of JDBC is represented by javax.sql.DataSource. DataSource is only an interface, which is usually implemented by the server (Weblogic, WebSphere, Tomcat) and some open source organizations:
  • dbcp is a database connection pool provided by Apache. The tomcat server has its own dbcp database connection pool. The speed is relatively c3p0 fast, but hibernate 3 no longer provides support due to its own BUG.
  • C3P0 is a database connection pool provided by an open source organization. The * * speed is relatively slow and the stability is OK** hibernate is officially recommended
  • Proxool is an open source project database connection pool under sourceforge. It has the function of monitoring the status of the connection pool, and its stability is c3p0 poor
  • BoneCP is a database connection pool provided by an open source organization with high speed
  • Druid is a database connection pool provided by Alibaba. It is said to be a database connection pool integrating the advantages of DBCP, C3P0 and Proxool. However, it is uncertain whether it is faster than BoneCP
• DataSource is usually called data source, which includes connection pool and connection pool management. Traditionally, DataSource is often called connection pool
• DataSource is used to replace DriverManager to obtain Connection, which is fast and can greatly improve database access speed.
• Special attention:
  • The data source is different from the database connection. There is no need to create multiple data sources. It is the factory that generates the database connection. Therefore, the whole application only needs one data source.
  • After the database access is completed, the program closes the database connection as before: conn.close(); However, Conn. Close () does not close the physical connection to the database. It only releases the database connection and returns it to the database connection pool.

1,C3P0

A. Method 1: read the attribute file

# Configuration file required for JDBC connection
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
user = root
password = lls
initPoolSize = 10

/**
     * @throws Exception
     * @function Create and configure a connection pool by reading data from the properties file
     */
    @Test
    public void test_C3P0() throws Exception {
        //Create a connection pool (four key attributes are required to associate the data source, driver, url, user and password)
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        //read configuration file
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        pro.load(is);
        is.close();
        //Get profile information
        String driverClass = pro.getProperty("driverClass");
        String url = pro.getProperty("url");
        String user = pro.getProperty("user");
        String password = pro.getProperty("password");
        //Set the required properties of the connection pool
        cpds.setDriverClass(driverClass);
        cpds.setJdbcUrl(url);
        cpds.setUser(user);
        cpds.setPassword(password);
        //Set up connection pool and some other basic configurations
        int initPoolSize = Integer.parseInt(pro.getProperty("initPoolSize"));
        cpds.setInitialPoolSize(initPoolSize);
        //Get connection
        Connection conn = cpds.getConnection();
        System.out.println(conn);
        //Destroy connection pool
        DataSources.destroy(cpds);
    }

B. Mode 2: read the configuration file

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="helloc3p0">
        <!-- Get 4 basic information of connection -->
        <property name="user">root</property>
        <property name="password">lls</property>
        <property name="jdbcUrl">jdbc:mysql:///test</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>

        <!-- Settings of related properties related to the management of database connection pool -->
        <!-- If the number of connections in the database is insufficient, How many connections are requested from the database server at a time -->
        <property name="acquireIncrement">5</property>
        <!-- The number of connections when initializing the database connection pool -->
        <property name="initialPoolSize">5</property>
        <!-- The minimum number of database connections in the database connection pool -->
        <property name="minPoolSize">5</property>
        <!-- The maximum number of database connections in the database connection pool -->
        <property name="maxPoolSize">10</property>
        <!-- C3P0 The database connection pool can be maintained Statement Number of -->
        <property name="maxStatements">20</property>
        <!-- Each connection can be used at the same time Statement Number of objects -->
        <property name="maxStatementsPerConnection">5</property>

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

/**
     * @function Read data using profile
     */
    @Test
    public void test_ReadConfig() throws Exception {
        ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
        Connection conn = cpds.getConnection();
        System.out.println(conn);
        DataSources.destroy(cpds);
    }

2,Druid

A.Druid database connection pool

Druid is a database connection pool implementation on Alibaba's open source platform. It combines the advantages of C3P0, DBCP, Proxool and other DB pools, and adds log monitoring. It can well monitor the connection of DB pool and the execution of SQL. It can be said that Druid is a DB connection pool for monitoring, which can be said to be one of the best connection pools at present.

B.druid.properties

url = jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
userName = root
password = lls
driverClassName = com.mysql.jdbc.Driver
initialSize = 10
maxActive = 10

C.connection

 /**
     * @function druid Connection pool
     */
    public void test_Druid()throws Exception{
        Properties pro = new Properties();
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);
        is.close();
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = ds.getConnection();
        System.out.println(conn);

    }

D. Detailed configuration parameters

to configure	default	explain
name		The significance of configuring this attribute is that if there are multiple data sources, they can be distinguished by name during monitoring. If there is no configuration, a name will be generated in the format of "DataSource -" + System.identityHashCode(this)
url		The url to connect to the database is different from database to database. For example: MySQL: JDBC: mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		User name to connect to the database
password		Password to connect to the database. If you don't want the password written directly in the configuration file, you can use ConfigFilter. See here for details: https://github.com/alibaba/druid/wiki/ Using ConfigFilter
driverClassName		Automatic identification according to url is optional. If druid is not configured, dbType will be automatically identified according to url, and then corresponding driverclassname will be selected (under recommended configuration)
initialSize	0	The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection
maxActive	8	Maximum number of connection pools
maxIdle	8	It is no longer used, and the configuration has no effect
minIdle		Minimum number of connection pools
maxWait		Maximum wait time to get a connection, in milliseconds. After maxWait is configured, the fair lock is enabled by default, and the concurrency efficiency will be reduced. If necessary, you can use a non fair lock by configuring the useUnfairLock attribute to true.
poolPreparedStatements	false	Whether to cache preparedStatement, that is, PSCache. PSCache greatly improves the performance of databases that support cursors, such as oracle. It is recommended to close under mysql.
maxOpenPreparedStatements	-1	To enable PSCache, it must be configured to be greater than 0. When greater than 0, poolPreparedStatements is automatically triggered and modified to true. In Druid, there will be no problem that PSCache in Oracle occupies too much memory. You can configure this value to be larger, such as 100
validationQuery		The sql used to check whether the connection is valid requires a query statement. If validationQuery is null, testonmirror, testOnReturn, and testwhiteidle will not work.
testOnBorrow	true	When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce performance.
testOnReturn	false	When returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance
testWhileIdle	false	It is recommended to configure to true, which will not affect performance and ensure security. Check when applying for a connection. If the idle time is greater than timebetween evictionrunsmillis, run validationQuery to check whether the connection is valid.
timeBetweenEvictionRunsMillis		It has two meanings: 1) the destroy thread will detect the connection interval; 2) the judgment basis of testwhiteidle. See the description of testwhiteidle property for details
numTestsPerEvictionRun		No longer used, a DruidDataSource only supports one EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		sql executed during physical connection initialization
exceptionSorter		According to dbType, it is automatically recognized that when the database throws some unrecoverable exceptions, the connection is discarded
filters		The attribute type is string. The extension plug-ins are configured by alias. The commonly used plug-ins are: filter for monitoring statistics: stat, filter for log: log4j, filter for defending sql injection: wall
proxyFilters		The type is List. If both filters and proxyFilters are configured, it is a combination relationship, not a replacement relationship

summary

1) C3P0 connection pool
2) Druid connection pool

reference

[1] JDBC Silicon Valley

appendix

1. JDBC utils connection change

private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
    private static DataSource druid;
    static {
        Properties pro = new Properties();
        InputStream is = System.class.getClassLoader().getResourceAsStream("druid.properties");

        try {
            pro.load(is);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }