Analysis of pooling ideas
Pooling idea is a very important idea in the process of our project development, such as integer pool, string pool, object pool, connection pool, thread pool and so on are all an application of pooling idea, which is to reduce the resource consumption caused by creating and releasing objects through reuse of objects, so as to improve system performance. For example, the internal pool application of the Integer object would look like this:
package com.cy.java.pool; public class TestInteger01 { public static void main(String[] args) { Integer n1=100; // integer.valueof (100) Compile-time optimization Integer n2=100; Integer n3=200; Integer n4=200; // New Integer(200) system.out.println (n1==n2); //true System.out.println(n3==n4); //false } }Copy the codeIntroduction to database connection pools
Background analysis
Mesh applications interact with the database development process, the “connection” or “release connection” is very consumes system resources two process, frequently on the establishment of the database connection and close will significantly affect the performance of the system, if a multi-threaded concurrent volume is very big, so time-consuming database connections can let the system become caton. Because TCP connections are expensive to create and there is a limit to the number of concurrent TCP connections a database can support, database connection pooling was created for this scenario. As shown below:
Consider: If you were to design a connection pool, what Angle would you design it from? First: physical storage structure (based on what structure to store data) second: based on what algorithm to connect from the pool? Third: What algorithm is used to remove the connection from the pool? Fourth: How are connection requests processed when there are no connections in the pool? Fifth: the pool can be shared, we need to consider the pool access concurrency security?
Analysis of connection pool principle
During system initialization, a space is created in memory, a certain number of database connections are stored as objects in the object pool, and methods of obtaining and returning database connections are provided externally. When a user accesses a database, he does not establish a new connection, but takes out an existing free connection object from the database connection pool. Connections returned after use will not be closed immediately, but are managed by the database connection pool to be ready for the next borrowing. If a connection in the database connection pool is fully borrowed due to high concurrency requests, other threads will wait until a connection is returned. The connection is not closed, but recycled, borrowed and returned. Database connection pool can also set its parameters to control the initial number of connections in the connection pool, the number of upper and lower limits of connections, as well as the maximum use of each connection, the maximum idle time, and so on. It can also monitor the number and usage of database connections through its own management mechanism.
Connection pooling in Java
In order to better use connection pooling technology in applications, Java defines a set of DataSource specifications, such as the javax.sql.DataSource interface, based on this interface, many teams and individuals create different connection pool objects. The DataSource interface in our application can then be coupled to easily switch the connection pool between different vendors. A basic procedure for obtaining a connection from a connection pool in a Java project is shown below:
In the figure above, the user retrieves a connection using the getConnection() method of the DataSource object. If there are connections in the pool, they are returned directly to the user. If there is no connection in the pool, the connect method of the Dirver (driver, implemented by the database vendor) object is called to get the connection from the database. Once you have the connection, you can put a copy of the connection in the pool and return the connection to the caller. When the connection demander needs the connection again, it can obtain it from the pool and return it to the pool object after it is used up.
Database connection pool in the Java database related middleware products, should be the bottom of the most basic type of product, as an enterprise application development indispensable component, countless offers us a genius and a good product, some with the time development, retire, while others are iterative, old and grow stronger, more new products, Or unbeatable performance, or comprehensive features. Common connection pools in the market include DBCP, C3P0, DRUID,HikariCP, etc.
HikariCP integration test under SpringBoot project
Data initialization
Open the mysql console and execute the goods.sql file as follows. Step 1: Log in to mysql.
Mysql - uroot - prootCopy the codeStep 2: set the console encoding mode.
set names utf8;
Copy the codeStep 3: Execute the goods. SQL file (remember not to open the file and copy it to the mysql client to run).
source d:/goods.sql
Copy the codeThe goods. SQL file contains the following contents:
drop database if exists dbgoods;
create database dbgoods default character set utf8;
use dbgoods;
create table tb_goods(
id bigint primary key auto_increment,
name varchar(100) not null,
remark text,
createdTime datetime not null
)engine=InnoDB;
insert into tb_goods values (null,'java','very good',now());
insert into tb_goods values (null,'mysql','RDBMS',now());
insert into tb_goods values (null,'Oracle','RDBMS',now());
insert into tb_goods values (null,'java','very good',now());
insert into tb_goods values (null,'mysql','RDBMS',now());
insert into tb_goods values (null,'Oracle','RDBMS',now());
insert into tb_goods values (null,'java','very good',now());
Copy the codeCreate the project Module and add dependencies
Step 1: Create project Module based on IDEA, as shown in the figure:
Step 2: Add dependencies
- Mysql database driver dependency.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
Copy the code- Spring object JDBC support (this will download the HiKariCP connection pool for us by default).
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
Copy the codeThe HikariCP connection pool is configured
Open the application.properties configuration file and add the following content (mandatory).
spring.datasource.url=jdbc:mysql:///dbgoods? serverTimezone=GMT%2B8&characterEncoding=utf8 spring.datasource.username=root spring.datasource.password=rootCopy the codeHikariCP other additional configuration (optional), the code is as follows (specific configuration is not clear can be baidu) :
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.pool-name=DatebookHikariCP
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1
Copy the codeHikariCP connection pool test
Unit test API design and application analysis, as shown in the figure:
Add the unit test class and test method to the project as follows:
package com.cy.pj.common.datasource; import java.sql.SQLException; import javax.sql.DataSource; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; @SpringBootTest public class DataSourceTests { @Autowired private DataSource dataSource; @Test public void testConnection() throws Exception{ System.out.println(dataSource.getConnection()); }}Copy the codeIn the current test class we need:
- Master unit test class and test method writing specification.
- Understand the DataSource design specification and its implementation.
- Who does the dataSource attribute point to in the test class?
- Who creates and manages the DataSource implementation object in the test class?
- What is the basic process of getting a connection based on the DataSource interface?
Test BUG Analysis
- Database does not exist, as shown in figure:
- DataSource: javax.sql
- Connection error: database failed to connect, as shown in the figure:
Implementing JDBC operations based on HikariCP (Exercise)
Business analysis
Based on HikariCP, using JDBC technology to access the data in the commodity database.
API Architecture Design
API design based on business, as shown in the figure:
Business sequence diagram analysis
Based on business requirements, design the sequence diagram of commodity query process, as shown in the figure:
[Image upload failed…(image-AEaa9E-1612504955913)]
Business code design and implementation
Step 1: Define the GoodsDao interface, for example:
package com.cy.pj.goods.dao; import java.util.List; import java.util.Map; /** * public interface GoodsDao {/** * public interface GoodsDao List<Map<String,Object>> findGoods(); }Copy the codeStep 2: Create a GoodsDao interface implementation class with the following code:
package com.cy.pj.goods.dao; / * * * This object is a commodity data layer access object, and now we need to define a method in this class that retrieves commodity information from the database based on JDBC and encapsulates it into a map collection, requiring a row to record a Map object (key is the name of the field in the table, value is the value of the field name), and multiple maps to be stored in the List collection. The @repository annotation is usually used to describe the data layer implementation class object, which is essentially a special @Component, */ @repository public class DefaultGoodsDao implements GoodsDao{@autoWired Private DataSource implements GoodsDao dataSource; Public List< map <String,Object>> findGoods(){Connection conn=null; public List< map <String,Object>> findGoods(){Connection conn=null; //java.sql.* Statement stmt=null; ResultSet rs=null; String sql="select * from tb_goods"; Conn = datasource.getConnection (); //1. //2. CreateStatement object STMT = conn.createstatement (); SQL rs=stmt.executeQuery(SQL); List<Map<String,Object>> List =new ArrayList<>(); While (rs.next()){// loop one row at a time and map one row to a map object list.add(rowMap(rs)); } return list; }catch (SQLException e){ e.printStackTrace(); throw new RuntimeException(e); // Convert to non-checked exceptions (exceptions not detected at compile time)}finally{//5\. Close (RS, STMT, CONN); }}Copy the codeDefine the row mapping method
private Map<String,Object> rowMap(ResultSet rs)throws SQLException{ Map<String,Object> rowMap=new HashMap<>(); Rowmap.put ("id", rs.getint ("id")); rowmap.put ("id", rs.getint ("id")); //rowMap.put("name",rs.getString("name")); //rowMap.put("remark",rs.getString("remark")); //rowMap.put("createdTime",rs.getTimestamp("createdTime")); ResultSetMetaData RSMD = rs.getMetadata (); Int columnCount=rsmd.getColumnCount(); int columnCount=rsmd.getColumnCount(); For (int I =0; i<columnCount; i++){ rowMap.put(rsmd.getColumnLabel(i+1),rs.getObject(rsmd.getColumnLabel(i+1))); //getColumnLabel(i+1); Return rowMap;} return rowMap; }Copy the codeDefine methods to release resources
private void close(ResultSet rs,Statement stmt,Connection conn){ if(rs! =null)try{rs.close(); }catch(Exception e){e.printStackTrace(); } if(stmt! =null)try{stmt.close(); }catch(Exception e){e.printStackTrace(); } // The connection is returned to the pool if(conn! =null)try{conn.close(); }catch(Exception e){e.printStackTrace(); }}}Copy the codeTest code writing and running
Define unit test classes and unit test their query procedures, for example:
package com.cy.pj.goods.dao; @SpringBootTest public class GoodsDaoTests { @Autowired private GoodsDao goodsDao; @Test void testFindGoods(){ List<Map<String,Object>> list= goodsDao.findGoods(); for(Map<String,Object> map:list){ System.out.println(map); }}}Copy the codeBUG analysis during test run
Record, analyze and summarize the problems in the testing process.
Summary (Summary)
In short, database connection pool brings many advantages for our project development and operation, as follows:
- Resource reuse is better.
As the database connection is reused, the overhead of creating and closing a large number of connections is reduced, and the number of memory fragments and database temporary processes and threads is greatly reduced, making the whole system run more smoothly.
- System tuning is easier.
With database connection pooling, the overhead of frequently closing connections is greatly reduced due to resource reuse, and the frequency of TIME_WAIT is greatly reduced.
- The system responds faster.
During application initialization, some database connections are prepared in advance. Service requests can directly use the created connections without waiting for connection creation costs. Initializing database connections in conjunction with resource reuse enables database connection pooling to greatly reduce the overall response time of the system.
- More flexible connection management.
As a kind of middleware, the user can configure the minimum number of connections, maximum number of connections, maximum idle time, connection timeout, heartbeat detection, etc. In addition, users can add a series of practical functions such as dynamic configuration, monitoring, and failure drills of the database connection pool based on new technology trends.
Source: www.jianshu.com/p/a042ec31f…