Implementation of a simple JDBC database connection pool

Directory:
1. Preface
2. connection pool technical background
2.1 JDBC
2.2 JDBC connection pool
2.3 connection pool and Resource Management
3. Implementation of simple JDBC connection pool
3.1 System Description
3.2 connection pool centralized management ConnectionManager
3.3 connection pool usage example
4. Summary
1. Preface
Database applications are often used in many software systems and are indispensable for developing large systems. However, if the database resources are not well managed (for example, resources such as the database cursors (ResultSet), Statement, and Connection) are not recovered in time), system stability is often directly caused. This type of instability is not only caused by the database or the system itself. It will be gradually revealed only when the system is officially used and with the increase of traffic and users.
In a Java-based system, JDBC is the main way for programmers to deal with databases and provides complete database operation methods and interfaces. However, considering the applicability of the specifications, JDBC only provides the most direct database operation specifications, and manages database resources, such as physical connection management and buffering. It is expected that a third-party Application Server).
This article introduces the relevant database connection pool mechanism based on the JDBC specification, and introduces related implementation technologies for effective management of database resources in a simple way.

2. connection pool technical background
2.1 JDBC
JDBC is a specification that complies with the JDBC interface specification. Each database manufacturer implements its own Driver, as shown in:

When an application obtains a database connection, it needs to specify the type of Driver as a URL. After obtaining a specific connection, it can operate different types of databases according to fixed interfaces, such: obtain Statement, execute SQL to obtain ResultSet, and so on, as shown in the following example:

Import java. SQL .*;
...
DriverManager. registerDriver (new oracle. jdbc. driver. OracleDriver ());
Connection dbConn = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: oracle", "username", "password ");
Statement st = dbConn. createStatement ();
ResultSet rs = st.exe cuteQuery ("select * from demo_table ");

... Some data source operation in here

Rs. close ();
St. close ();
DbConn. close ();

After completing data operations, you must also close all the database resources involved. Although this has no impact on the logic of the application, it is a key operation. The above is a simple example. if many if-else and exception are involved, the management of resources will inevitably be sparse. Like the memory leakage in C, the Java system will also face a crash. Therefore, the management of database resources depends on the application system itself, which is a hidden danger of insecurity and instability.

2.2 JDBC connection pool
The standard JDBC interface does not provide resource management methods. Therefore, the application is responsible for default resource management. Although in the JDBC specification, it is often mentioned that the resource is closed/recycled and its rational use. However, the safest way is to provide effective management means for applications. Therefore, JDBC provides a management standard interface (connection pooling) implemented by the database manufacturer for the third-party Application Server ). The Connection Pool is introduced to manage database resources.

The most common JDBC resources include:
-Connection: database Connection.
-Statement: Session Declaration.
-ResultSet: result set cursor.

There are the following relationships:

This is a "parent-child" relationship. The management of Connection is the management of database resources. For example, to determine whether a database Connection times out, you need to determine whether its (all) Sub-Statement times out. Similarly, You need to determine whether all relevant ResultSet times out; before closing the Connection, you must disable all related Statement and ResultSet.
Therefore, the Connection Pool not only manages connections, but also involves Statement and ResultSet.

2.3 connection pool and Resource Management
The ConnectionPool uses the buffer pool mechanism to control and manage Connection, Statement, and ResultSet within a certain range. The resources of any database are limited. If used up, more data services cannot be obtained.
In most cases, resource depletion is not due to the high normal load of the application, but to the program.
In actual work, data resources are often bottlenecks, and different applications will access the same data source. When an application consumes database resources, other applications cannot run normally. Therefore, the first ConnectionPool task is to limit the maximum resources that each application or system can possess. That is, determine the size of the Connection Pool (PoolSize ).
The second task of ConnectionPool: resources are used to the maximum extent within the PoolSize range of the Connection Pool, shortening the use cycle of database access. In many databases, Connection is not the smallest unit of resources. Controlling Statement resources is more important than Connection. Take Oracle as an example:
Each request for a Connection will establish a communication Connection on the physical network (such as the TCP/IP network), and a certain number of Statement can be applied for this Connection. The number of active Statement provided by the same connection can reach several hundred. While saving network resources, It shortens the cycle of each session (establishing a physical connection is a time-consuming operation ). However, in general applications, most of the operations follow the 2.1 example. In this case, 10 program calls will generate 10 physical connections, and each Statement occupies one physical connection separately, this is a great waste of resources. ConnectionPool can solve this problem, so that dozens or hundreds of Statement occupy only the same physical connection, to take advantage of the original advantages of the database.
The ConnectionPool effectively manages resources, and the total number of Statement obtained by the application reaches:

(Concurrent physical connections) × (number of Statement quantities available for each connection)

For example, if a database can establish 200 physical connections and each connection can provide 250 Statement at the same time, the total number of concurrent Statement provided by the ConnectionPool for the application is: 200x250 = 50,000. This is a concurrent number, and few systems break through this magnitude. At the beginning of this section, it is pointed out that resource depletion is related to the direct management of applications.
The optimal management of resources relies heavily on the availability of the database's own JDBC Driver. The JDBC Driver of some databases does not support the logical Connection between Connection and Statement. For example, SQLServer, we can only wait for her own updated version.
The application, release, recovery, sharing and synchronization of resources are complex and precise. Therefore, another function of ConnectionPool is to encapsulate these operations to provide simple calling interfaces for applications without changing the application style.

3. Implementation of simple JDBC connection pool
According to the original mechanism in Chapter 2, Snap-ConnectionPool (a simple and fast connection pool tool that can be used at Http: // Www. snapbug. net/download) implements effective management of database resources in the connection pool according to some JDBC specifications.
3.1 System Description
In the JDBC specification, an application uses a Driver Interface to directly evaluate database resources. To effectively and reasonably manage resources, a connection pool is added between the application and JDBC Driver: Snap-ConnectionPool. In addition, through the object-oriented mechanism, most operations in the connection pool are transparent. See the Snap-ConnectionPool system:

As shown in the figure, through the implementation of some JDBC resource object interfaces (Connection, Statement, ResultSet), three logical resource objects are generated in the Snap-ConnectionPool: PooledConnection, PooledStatement, and PooledResultSet. They are also the main management operation objects of the connection pool and inherit the corresponding subordination in JDBC. Such a system has the following features:
-Transparency. Provide resource management services without changing the original JDBC driver interface of the application. The application system, like the original JDBC, uses the logical object resources provided by the connection pool. Simplified application connection pool transformation.
-Resource encapsulation. Complex resource management is encapsulated in the Snap-ConnectionPool without interference from the application system. The reliability and security of management operations are guaranteed by the connection pool. Application interference (for example, proactively disabling resources) only plays a role in optimizing system performance, and missing operations will not have a negative impact.
-Use resources properly. According to the resource subordination in JDBC, the Snap-ConnectionPool not only caches the Connection, but also processes the Statement. As described in section 2.3, reasonable use of the relationship between Connection and Statement can be used to a greater extent. Therefore, the Snap-ConnectionPool encapsulates Connection resources and provides more Statement resources for the application system by managing PooledConnection internally.
-Resource chain management. Snap-ConnectionPool contains three logical objects that inherit the subordination between the corresponding objects in JDBC. In internal management, chain management is also conducted according to the subordination. For example, to determine whether a Connection times out, you need to determine whether the Statement is active Based on the included Statement; to determine whether the Statement is also based on the degree of activity of the ResultSet.

3.2 connection pool centralized management ConnectionManager
ConnectionPool is the connection pool object of Snap-ConnectionPool. Within the Snap-ConnectionPool, you can specify multiple connection pools (ConnectionPool) to serve applications. ConnectionManager manages all the connection pools. Each connection pool is named differently. Use the configuration file to adapt to different database types. As shown in:

ConnectionManager allows you to manage multiple connection pools at the same time and provides a one-on-one management interface. In the application system, ConnectionManager and related configuration files can be used to scatter the database configuration information (including the database name, user, password, and other information) in their respective applications ), in a single file. This facilitates system maintenance.

3.3 connection pool usage example

For the example of 2.1 Standard JDBC, use the connection pool. The result is as follows:

Import java. SQL .*;
Import net. snapbug. util. dbtool .*;
...
.. ConnectionPool dbConn = ConnectionManager
. GetConnectionPool ("testOracle ");
Statement st = dbConn. createStatement ();
ResultSet rs = st.exe cuteQuery (
"Select * from demo_table ");
...
Some data source operation
In herers. close (); st. close ();

In this example, the Snap-ConnectionPool encapsulates the application's Connection management. As long as you change the method of JDBC to obtain the Connection, to obtain the Connection pool (ConnectionPool) (in bold), other data operations can not be modified. In this way, the Snap-ConnectionPool can help the application effectively manage database resources. If the application ignores the release of the final resource: rs. close () and st. close (), the connection pool will be automatically reclaimed through the time-out mechanism.

4. Summary

Both Snap-ConnectionPool and other database connection pools should have the following basic functions:

-Source database resource protection

-Make full use of valid database resources

-Simplified the database interface of the application and closed resource management.

-Automatic collection and sorting of application legacy resources to improve resource reuse.

On this premise, applications can devote more energy to their own business logic. Database resources are no longer the bottleneck of the system.