Address: http://www.haha174.top/article/details/2577891. Talk about the principle of database connection pool

This time we take the way of technology evolution to talk about the emergence of database connection pool technology and its principle, as well as the most popular open source database connection pool JAR package.

How do we do database operations in the early days

① Load the database driver;

② Establish database connection through JDBC;

③ Access the database and execute SQL statements;

4 Disconnect the database.

Code 2.

       Public void FindAllUsers(){//1, sqlserver driver Class.forname ();"com.mysql.jdbc.Driver"); / / 2, establish a database Connection via JDBC Connection con = DriverManager. GetConnection ("jdbc:mysql://localhost:3306/test? &useUnicode=true&characterEncoding=utf-8&failOverReadOnly=false"."root"."root"); Statement state = con.createstatement (); ResultSet result =state.executeQuery(ResultSet result =state.executeQuery("select * from users"); //5. Output the query resultwhile(result.next()){
                     System.out.println(result.getString("username")); } //6, disconnect from the database result.close(); state.close(); con.close(); }Copy the code

3. The 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, taking 0.05s to 1s each time, and the system has to allocate memory resources. This time for one or several database operations, the system may not feel much overhead. But with today’s Web applications, especially large e-commerce sites, it’s not uncommon to have hundreds or even thousands of people online at the same time. In this case, the frequent database connection operation is bound to take up a lot of system resources, the response speed of the website is bound to decline, and even serious will cause the crash of the server. Not to sound alarmist, this is the technical bottleneck that restricts the development of some e-commerce sites. Second, for every database connection, disconnect after use. Otherwise, if the program fails to close due to an exception, it will cause a memory leak in the database system and eventually the database will have to be restarted. In addition, such development does not control the number of connection objects created, and system resources can be allocated recklessly, such as too many connections, memory leaks, and server crashes.

In the above user query case, if 1000 people visit at the same time, there will be continuous database connection and disconnection operations:

As can be seen from the above analysis, the root cause 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. This mode is to solve the problem caused by the frequent allocation and release of resources. To solve the above problems, database connection pooling technology can be used. The basic idea of database connection pooling is to create a “buffer pool” for database connections. A certain number of connections are placed in the buffer pool beforehand, and when a database connection needs to be established, you simply take one out of the buffer pool and put it back in. We can prevent the system from endlessly connecting to the database by setting the maximum number of connections in the connection pool. More importantly, we can monitor the number and usage of database connections through the connection pool management mechanism, providing basis for system development, testing and performance adjustment. Welcome to follow, more benefits