java database connection pool beginner entry is enough too simple!
- 2021-09-12 01:02:03
- OfStack
1. What is a database connection pool
Is a container to hold multiple database connections, when the program needs to operate the database directly from the pool to take out the connection, and then return it after use, and thread pool 1 reason.
2. Why do you need connection pooling and what are the benefits?
1. Save resources. If a new connection is created every time the database is accessed, the creation and destruction of the system resources are wasted
2. The responsiveness is better, which saves the creation time and has 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 schemes
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 pool
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.
driverClassName
The full and valid Java class name of the JDBC driver used, such as connecting mysql com. mysql. cj. jdbc. Driver
2.
jdbcUrl
Connection to the 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
Too straightforward, the user password of the database, such as p123456
5.
initialSize
When the connection pool is created, the number of database connections automatically created is recommended. 10-50 is enough
6.
maxIdle
Maximum free connections: The maximum number of connections allowed to remain idle in the connection pool. Excess free connections will be released. If set to negative, it means no limit. It is recommended to set the same as initialSize to reduce the performance loss of release and creation.
7.
2、DBCP (Database Connection Pool)0
Minimum free connections: The minimum number of connections allowed to remain idle in a connection pool below which new connections will be created, or not if set to 0
8.
maxActive
Maximum number of simultaneous active connections.
9.
maxWait
If there are no available connections in the connection pool, the maximum wait time, timeout means no available connections, in milliseconds, setting-1 means unlimited wait, and it is recommended to set to 100 milliseconds
10,
testxxx
When operating on the connection, whether to detect the validity of the connection, such as testOnBorrow will first detect the validity of the connection when applying for the connection, and execute validationQuery. It is recommended to set this configuration as false on the line, because it will affect the performance.
11,
validationQuery
An SQL statement that checks whether the connection in the pool is still available. drui connects to the database to execute the SQL. If it returns normally, the connection is available. Otherwise, the connection is unavailable. It is recommended that select 1 from dual
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>
<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>
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.
I hope you will pay more attention to other contents of this site!