Efficient management strategy of database connection pool based on JDBC

In the development of database application based on JDBC, the management of database connection is a difficult problem, because it is an important factor to decide the application performance. Based on a thorough analysis of database connection, this paper proposes and implements an efficient connection management strategy, which makes it relatively easy to develop high-performance database applications. In particular, there are two problems in connection management: Transaction and multithreading, and a solution based on design pattern is given.

Introduced

In the Java language and database-related application development, the general use of JDBC for the interaction with the database, which has a key concept is connection (connection), which is a class in Java, represents a channel. With it, data can be accessed from the database using the application.

For a simple database application, because access to the database is not very frequent. You can simply create a new connection when you need to access the database, and then close it when you are done with it, which does not incur any significant performance overhead. But for a complex database application, the situation is completely different. Frequent establishment and shutdown of the connection will greatly reduce the performance of the system, because the use of the connection becomes the bottleneck of the system performance.

The method presented in this paper can solve the problem effectively. In this method, a reasonable and effective connection management strategy is proposed, which avoids the arbitrary and irregular use of the connection. The core idea of this strategy is: connect reuse. By establishing a database connection pool and a set of connection usage management policies, a database connection can be efficiently and securely reused, avoiding the overhead of frequent establishment and shutdown of database connection. In addition, because of the encapsulation of the original connection in JDBC, thus it is convenient for the application of the database to use the connection (especially for transaction processing), to improve the development efficiency, it is because of the existence of the encapsulation layer that the application's own processing logic and the specific database access logic are isolated, so that the reuse of the application itself becomes possible.

Problem arises

I am involved in the project is to develop a network management system, and inevitably to deal with the database. In the beginning, because the access to the database is not very frequent, the use of the database connection is simple need to set up, after the closure of the policy, this is in line with the programming of the XP (EXtreme) slogan: "Do the simplest Thing that could Possibly Work ". Yes, it worked very well at the beginning. As the project progresses, access to the database becomes more frequent, and the problem is exposed, and the original method of simply getting and shutting down the database connection greatly affects the performance of the system due to the frequent creation and destruction of those connection objects by the database resource manager process.

At this point, it is necessary to refactor the database access method (refactoring), because we do need to improve the performance of the system.

Solution

As you can see, the root cause of the problem is due to inefficient management of connection resources. For shared resources, there is a well-known design pattern: resource pools. The model is to solve the problems caused by the frequent distribution and release of resources. The application of this mode to database connection management is to establish a database connection pool, provide a set of efficient connection allocation and usage strategy, and the ultimate goal is to realize the efficient and secure reuse of the connection.

3.1. Establish Connection pool

The first step is to establish a static connection pool, the so-called static means that the pool of connections in the system when the initialization of the allocation, and can not be arbitrarily closed. Java provides us with many container classes that can be easily used to build connection pools, such as vectors, stack, and so on. When system initialization, a connection is created according to configuration and placed in a connection pool, and subsequent connections are obtained from that connection pool, thus avoiding the overhead of connection creation and shutdown (of course, we have no way to avoid the overhead of Java's garbage collection).

3.2, allocation, release strategy

With this connection pool, we can now provide a set of custom allocation and release policies.

When a client requests a database connection, first look at whether there is an idle connection in the connection pool, where idle is the connection that is not currently assigned. If there is an idle connection, the connection is assigned to the customer, and the corresponding processing, the specific processing strategy, in the key issues will be detailed, the main processing strategy is to mark the connection as assigned. If there is no idle connection in the connection pool, look for a suitable connection to the customer in the connection that has already been allocated (the selection policy is detailed in the key issues), at which point the connection is reused among multiple customers.

When a client releases a database connection, it can be handled differently depending on whether the connection is reused. If the connection does not have a user, it is placed in the connection pool instead of being closed.

It can be seen that it is this set of strategies to ensure the effective reuse of database connections.

3.3, Configuration strategy

How many connections do you want to put in the database connection pool, and how do you handle the connection when it runs out? This is a configuration policy. The general configuration strategy is to give the number of connections in an initial connection pool and the maximum number of connections a connection pool can expand to, depending on the specific application requirements at the beginning. This scheme is implemented according to this strategy.