Spark Large Project Combat (vii): User Access Session Analysis (vii)--database connection pooling principle

* * Article Address: http://www.haha174.top/article/details/257789**
1. Talking about the principle of database connection pool
-------------
This time we take a technological evolution to talk about the process of database connection pooling technology and its rationale, and the most popular open source database connection pool jar package today.

I. How do we perform database operations in the early stages?

1. Principle: In general, the process of accessing a database by a Java application is:

① loading the database driver;

② database connection through JDBC;

③ Access database, execute SQL statement;

④ Disconnect the database.

2. Code

       Public void FindAllUsers(){              //1、装载sqlserver驱动对象            Class.forName("com.mysql.jdbc.Driver");              //2、通过JDBC建立数据库连接              Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/test?&useUnicode=true&characterEncoding=utf-8&failOverReadOnly=false", "root", "root");                        //3、创建状态              Statement state =con.createStatement();                         //4、查询数据库并返回结果              ResultSet result =state.executeQuery("select * from users");                         //5、输出查询结果              while(result.next()){                     System.out.println(result.getString("username"));              }                          //6、断开数据库连接              result.close();              state.close();              con.close();        }

3. Analysis

In the process of program development, there are many problems: first, each Web request to establish a database connection. Establishing a connection is a time-consuming activity that takes 0.05s~1s times each time, and the system allocates memory resources. This time for one or several database operations, you may not feel how much overhead the system has. But for today's web applications, especially large e-commerce sites, there are hundreds of people or even thousands of people online is very normal. In this case, the frequent database connection operation is bound to occupy a lot of system resources, the response speed of the site must be reduced, serious or even cause a server crash. is not alarmist, this is restricting the development of some e-commerce website technology bottleneck problem. Second, for each database connection, you have to disconnect after use. Otherwise, if the program fails to shut down, it will cause a memory leak in the database system and will eventually have to restart the database. Also, this development does not control the number of connection objects being created, and system resources are allocated without consideration, such as too many connections, which can lead to memory leaks and server crashes.

The above user query case, if there are 1000 people access, there will be continuous database connection, disconnection operation:

Through the above analysis, we can see that "database connection" is a scarce resource, in order to protect the normal use of the site, it should be properly managed. In fact, after we query the database, if we do not close the connection, but temporarily stored up, when others use, the connection to their use. It avoids the time consuming of establishing a database connection and a disconnected operation. The principle is as follows:

Two. Technology Evolved database Connection pool

As can be seen from the above analysis, the root of the problem lies in the inefficient management of database connection resources. We know that there is a well-known design pattern for shared resources: resource pools (resource pool). This model is to solve the problem caused by the frequent allocation of resources ﹑ release. To solve the above problems, the database connection pooling technology can be adopted. The basic idea of a database connection pool is to establish a "buffer pool" for database connections. A certain number of connections are pre-placed in the buffer pool, and when a database connection needs to be established, simply take one out of the buffer pool and put it back when you are finished. We can prevent the system from endlessly connecting to the database by setting the maximum number of connections to the connection pool. More importantly, we can monitor the number of connections in the database through the connection pool management mechanism ﹑ usage, provide the basis for system development ﹑ test and performance adjustment.
Welcome attention, More benefits
----

Spark Large Project Combat (vii): User Access Session Analysis (vii)--database connection pooling principle