Small knowledge, big challenge! This article is part of the “Programmer’s Essentials

This article also participated in the “Digitalstar Project” to win a creative gift package and creative incentive money

Today I continued my Java project. I didn’t do anything for two days and had a comfortable weekend. But I don’t know why I always feel a little worried.

Before the game server data source is used by Alibaba Druid, today about the data source, to give an example.

1, what is database connection pool

It is a container that holds multiple database connections, and when the application needs to operate on the database, it pulls the connections out of the pool and returns them back, just like a thread pool.

2. Why is connection pooling needed and what are the benefits?

1. Save resources. If a new connection is created every time a database is accessed, the creation and destruction will waste system resources

2, better responsiveness, save the creation time, 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 schemes

There are many database connection pooling schemes. The connection pooling schemes I have been exposed to are:

1, C3p0

I saw this connection pool a long time ago, but at that time I was very weak and did not understand it well, and now I use it very little, grandpa connection pool, can be ignored

2. Database Connection Pool (DBCP)

The name is straightforward, database connection pool, and starting with Tomcat 5.5, The data source implementation of DBCP is built into Tomcat, so it is very easy to configure DBCP data sources.

3, Druid

Ali open source data source, which is also used by the former company data source, Druid can provide powerful monitoring and extension capabilities, strong in monitoring.

4, HiKariCP

Billed as the fastest database connection pool, springboot2.0 has also changed the default data source to HikariCP, which is better than performance.

Druid’s database connection pool configuration is as follows:

<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>Copy the code

DriverClassName Full and valid Java class name of the JDBC Driver used, such as connecting to mysql com.mysql.cj.jdbc.driver

2. Connect to jdbcUrl database. Such as JDBC: mysql: / / 127.0.0.1:3306 / mydatabase

Username specifies the username of the database

4, password is too straightforward, database user password, such as P123456

InitialSize Indicates the number of database connections that are automatically created when the connection pool is created. It is recommended that 10-50 be sufficient

MaxIdle: Indicates the maximum number of connections allowed to remain idle in the connection pool. If the value is set to negative, the number will be released. It is recommended to set it to the same as initialSize to reduce the performance loss of release and creation.

MinIdle: Specifies the minimum number of connections that can remain idle in the pool. If the number is lower than this, new connections will be created. If the value is set to 0, no new connections will be created

8. MaxActive Maximum number of connections that can be activated at the same time.

MaxWait Indicates the maximum waiting time if no connection is available in the connection pool. The unit is millisecond. The value -1 indicates an infinite wait

Testxxx checks the validity of a connection when it operates on a connection. For example, testOnBorrow checks the validity of a connection when it applies for a connection and executes validationQuery. It is recommended to set this configuration to false for online connections because it will affect performance.

Drui connects to the database to execute this SQL statement. If this SQL statement is returned, the connection is available. Otherwise, the connection is unavailable

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

4.1 Pom.xml adds dependencies

Alibaba </groupId> <artifactId>druid</artifactId> <version>1.2.6</version> </dependency>Copy the code

4.2 Configuration File

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <! <property name=" ${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" /> <! -- configure initialSize, min, Max --> <property name="maxActive" value="20" /> <property name="initialSize" value="1" /> <property name="minIdle" value="1" /> <! <property name="maxWait" value="60000" /> <! - how soon a detection configuration interval, testing needs to be closed free connection, the unit is milliseconds - > < property name = "timeBetweenEvictionRunsMillis" value = "60000" / > <! -- Set the minimum lifetime of a connection in the pool, Unit is milliseconds - > < property name = "minEvictableIdleTimeMillis" value = "300000" / > < property name = "testWhileIdle" value = "true" / > <property name="testOnBorrow" value="false" /> <property name="testOnReturn" value="false" /> <! -- Open PSCache, And specify the size of the PSCache on each connection --> <property name="poolPreparedStatements" value="true" /> <property name="maxOpenPreparedStatements" value="20" /> </bean> <! -- Configure jdbcTemplate, If there is no extends userDao 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>Copy the code

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 (" parsley "); user.setPassowrd("root"); userDao.saveUser(user); }Copy the code

5, summary

Connection pooling is the same as thread pooling: pooling resources reduces generation and destruction costs and improves system responsiveness.

Today’s focus is on understanding the principle of thread pools and remembering most of the configuration parameters. Although the details of thread pools are different, the principle is the same. Master one, master all.

All right. Let’s call it a day. Sleep.