Detailed explanation of Java database connection pool

Directory 1. What is database connection pooling 2. Why do you need connection pooling and what are the benefits? 3. What connection pooling schemes are available 4. Parameters that connection pooling needs attention 5. How to create connection pooling, show me the code 5.1 pom. xml add dependency 5.2 configuration file 5.3 call 6. Summary

1. What is a database connection pool

Is that one container holds multiple 数据库连接 When the program needs to operate the database, take out the connection directly from the pool and return it after using it, which is the same as thread pool.

2. Why do you need connection pooling and what are the benefits?

1. Save resources. If you create a new connection every time you access the database, the creation and destruction will waste system resources

2. Better responsiveness, saving time for creation and better responsiveness.

3. Unified 1 manages database connections to avoid the unlimited increase of database connections caused by the expansion of business.

4. Easy to monitor.

3. What are the connection pooling scenarios

There are many database connection pooling schemes, and the connection pooling schemes I have touched are:

1.C3p0

This connection pool I have seen a long time ago, but at that time they are still very weak, and do not have a good understanding, and now it is rarely used, grandpa-level connection pool, can be ignored

2.DBCP (Database Connection Pool)

This name is very straightforward, database connection pool, starting from Tomcat 5.5, Tomcat has built-in DBCP data source implementation, so it is very convenient to configure DBCP data source.

3.Druid

Ali's open source data source, which is also the data source used by the former company, Druid can provide powerful monitoring and expansion functions, and its strength lies in monitoring.

4.HiKariCP

Known as the fastest database connection pool, springboot 2.0 has also changed the default data source to HikariCP, which is better than performance.

4. Parameters to pay attention to in connection pooling

Look at the configuration of database connection pool for Druid:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${db.driverClassName}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
<property name="initialSize" value="5"/>
<property name="maxIdle" value="10"/>
<property name="minIdle" value="5"/>
<property name="maxActive" value="15"/>
<property name="removeAbandoned" value="true"/>
<property name="removeAbandonedTimeout" value="180"/>
<property name="maxWait" value="3000"/>
<property name="validationQuery">
<value>SELECT 1</value>
</property>
<property name="testOnBorrow">
<value>true</value>
</property>
</bean>

1. The complete and valid Java class name of the JDBC driver used by driverClassName, such as connecting mysql com. mysql. cj. jdbc. Driver

2. Connection to the jdbcUrl database. For example, jdbc: mysql://127.0. 0.1: 3306/mydatabase

3. username You know, the user name of the database, such as root

4. password is too straightforward. The user password of the database, such as p123456

5. When initialSize connection pool is created, the number of database connections automatically created is recommended. 10-50 is enough

6. maxIdle Maximum Idle Connections: The maximum number of connections allowed to remain idle in the connection pool. Exceeded idle connections will be released. If set to a negative number, it means no limit. It is recommended to set the same as initialSize to reduce the performance loss of release and creation.

7. minIdle Minimum Idle Connections: The minimum number of connections allowed to remain idle in the connection pool below which new connections will be created. If set to 0, no connections will be created

8. Maximum number of simultaneous maxActive connections.

9. maxWait If there is no available connection in the connection pool, the maximum wait time, timeout, there is no available connection, in milliseconds, setting-1 means unlimited wait, and it is recommended to set it to 100 milliseconds

10. When testxxx operates on the connection, whether to detect the validity of the connection. For example, testOnBorrow will first detect the validity of the connection when applying for the connection, and execute validationQuery. It is recommended to set this configuration to false on the line, because it will affect the performance.

11. validationQuery SQL statement that checks whether connections in the pool are still available. drui connects to the database to execute the SQL. If it returns normally, the connection is available. Otherwise, the connection is unavailable. select 1 from dual is recommended

5. How to create a connection pool, show me the code

5.1 pom. xml join dependency

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.2.6</version>
   </dependency>

5.2 Configuration Files

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    <!--  Basic attribute  url , user , password -->
    <property name="driverClassName" value="${jdbc.driver}" />
    <property name="url" value="${jdbc_url}" />
    <property name="username" value="${jdbc_user}" />
    <property name="password" value="${jdbc_password}" />
 
    <!--  Configure the for monitoring statistics interception filters -->
    <property name="filters" value="stat" />
 
    <!--  Configure initialization size, minimum, maximum  -->
    <property name="maxActive" value="20" />
    <property name="initialSize" value="1" />
    <property name="minIdle" value="1" />
 
    <!--  Configure the time to get the connection wait timeout  -->
    <property name="maxWait" value="60000" />    
 
    <!--  How long does the configuration interval take place 1 Detects idle connections that need to be closed, in milliseconds  -->
    <property name="timeBetweenEvictionRunsMillis" value="60000" />
 
    <!--  Configure 1 The minimum time, in milliseconds, for a connection to live in the pool  -->
    <property name="minEvictableIdleTimeMillis" value="300000" />
 
    <property name="testWhileIdle" value="true" />
    <property name="testOnBorrow" value="false" />
    <property name="testOnReturn" value="false" />
 
    <!--  Open PSCache And specifies that on each connection PSCache The size of  -->
    <property name="poolPreparedStatements" value="true" />
    <property name="maxOpenPreparedStatements" value="20" />
</bean>
 
<!-- Configure jdbcTemplate , if userDao No extends JdbcDaoSupport-->
<<bean id="jdbcTemplate" class="com.springframework.jdbc.core.JdbcTemplate">
       <property name="dataSource" ref="dataSource"/>
   </bean>
    <bean id="userDao" class="com.caraway.dao.UserDao">
       <property name="dataSource" ref="jdbcTemplate"/>
   </bean>

5.3 Call

public static void main(String[] args) {
       ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
       UserDao userDao = (UserDao) context.getBean("userDao");
       User user = new User();
       user.setUsername(" Coriander ");
       user.setPassowrd("root");
       userDao.saveUser(user);
  }

6. Summary

Connection pool and thread pool reason is a kind of, pool resources, reduce the loss of generation and destruction, improve the response of the system.

Today's focus is to understand the principle of thread pool, and remember most of the configuration parameters. Although the implementation details of each thread pool are different, the reasons are the same. If you master one, you will master everything.