By analogy, understand Java's database connection pool in one article

image
Like it and look again, make it a habit

I continued the Java topic today. I didn't do anything for two days. I had a comfortable weekend, but I don't know why I am always a little bit worried.

The data source of the previous game server was Druid of Alibaba. Today I will talk about the data source and give an example.

1. What is a database connection pool

That is, a container holds multiple 数据库连接, when the program needs to operate the database, the connection is directly taken out of the pool, and then returned after use, which is the same as the thread pool.

2. Why do you need a connection pool 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 management of database connections to avoid unlimited increase in database connections due to business expansion.

4. Easy to monitor.

3. What are the connection pool solutions?

There are many database connection pool schemes. The connection pool schemes I have contacted are:

1. C3p0

I have seen this connection pool a long time ago, but at that time I was still very weak and did not understand it well, and now I rarely use it. Grandpa-level connection pool can be ignored

2. DBCP (Database Connection Pool)

The name is very straightforward. Database connection pool. Starting from Tomcat 5.5, Tomcat has built-in DBCP data source implementation, so you can configure DBCP data source very conveniently.

3. Druid

Alibaba's open source data source 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 that need to be paid attention to in the connection pool

Look at the configuration of Druid's database connection pool:

<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 to mysql com.mysql.cj.jdbc.Driver

2. The connection to the jdbcUrl database. Such as jdbc:mysql://127.0.0.1:3306/mydatabase

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

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

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

6. maxIdle maximum idle connections: the maximum number of connections allowed to remain idle in the connection pool. The excess 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 connection: the minimum number of connections allowed to remain idle in the connection pool, below this number will create a new connection, if set to 0, it will not be created

8. maxActive is the maximum number of simultaneous active connections.

9. maxWait If there is no available connection in the connection pool, the maximum waiting time, there is no available connection when timeout, the unit is milliseconds, setting -1 means infinite waiting, it is recommended to set to 100 millisecond

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

11, validationQuery Checks whether the connection is still available pool of SQL statements, drui connects to execute the SQL database, if the return to normal, then a connection is available, or that the connection is not available, it is recommended select 1 from dual

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

4.1 add dependency in pom.xml

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

4.2 Configuration file

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">    <!-- 基本属性 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}" />     <!-- 配置监控统计拦截的filters -->    <property name="filters" value="stat" />     <!-- 配置初始化大小、最小、最大 -->    <property name="maxActive" value="20" />    <property name="initialSize" value="1" />    <property name="minIdle" value="1" />     <!-- 配置获取连接等待超时的时间 -->    <property name="maxWait" value="60000" />         <!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->    <property name="timeBetweenEvictionRunsMillis" value="60000" />     <!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->    <property name="minEvictableIdleTimeMillis" value="300000" />     <property name="testWhileIdle" value="true" />    <property name="testOnBorrow" value="false" />    <property name="testOnReturn" value="false" />     <!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->    <property name="poolPreparedStatements" value="true" />    <property name="maxOpenPreparedStatements" value="20" /></bean> <!--配置jdbcTemplate,如果userDao没有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>

4.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("香菜");       user.setPassowrd("root");       userDao.saveUser(user);  }

5. Summary

The principle of connection pool and thread pool is the same, pool resources, reduce the loss of generation and destruction, and improve the response of the system.

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

All right. Come here today, sleep.