Research and design of database connection pool technology based on JDBC

This paper introduces the working principle of database connection pool based on JDBC, expounds the key technologies such as transaction processing of connection pooling technology, multi database server and so on, puts forward an efficient connection pool management strategy, and finally details the implementation process of database connection pool application.

Keywords JDBC; Database Connection pool; Transaction processing

With the rapid development and wide application of information technology, the position of database technology in the field of information technology is more and more important, especially the rapid development of network application and e-commerce, all need database technology to support Dynamic Web site operation, and the traditional development mode is: first in the main program (such as Servlet, Beans And then perform SQL operations to query, modify, and delete objects in the database, and finally disconnect the database. With this development pattern, for a simple database application, because the database access is not very frequent, just need to access the database to create a connection, after the use of the shutdown, it does not significantly increase the overhead of the system. But for a complex database application, the situation is completely different: the frequent establishment and shutdown of the database, will greatly reduce the performance of the system, increase the cost of the system, and even become the bottleneck of the system. Using this traditional pattern, you must also manage each connection to the database to ensure that they are properly shut down and that some connections fail to close if a program exception occurs, causing memory leaks in the database system and eventually having to restart the database. Therefore, it is very important to use database technology with faster running speed and more efficient database access to improve the operation efficiency of the system.

To solve this problem, the JDBC Connection pooling technology is proposed in JDBC2.0, which can improve resource usage and improve application responsiveness by sharing a set of connections among customers instead of generating them when they are needed.

JDBC Overview

JDBC (Java database Connectivity,java DB connection) is a JAVAAPI for executing SQL statements, and can be a variety of relational databases such as Oracle, Sybase, SQL Server, Access, and so on) provides a unified access interface consisting of classes and interfaces written in a set of Java languages that enable database developers to write database applications with standard JAVAAPI.

Connection Pooling Technology

1. Connection Pool principle

The core idea of connection pool technology is: connection multiplexing, through establishing a database connection pool and a set of connection use, allocation, management strategy, make the connection in this connection pool can be efficient, secure reuse, avoid the database connection frequent establishment, shutdown overhead. 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. The connection pool is made up of three parts (as shown in Figure 1): Connection pooling, usage management of connection pools, and shutdown of connection pools. The following is a discussion of the three parts and the configuration of the connection pool.

Figure 1 Connection Pool mode

1.1 Establishment of Connection pool

The connection pool established in the application is actually a static one. A static connection pool is a connection that is allocated in a connection pool when the system is initialized, and the connection cannot be closed at will. Java provides a number of container classes to facilitate the construction of connection pools, such as vectors, Stack, Servlet, Bean, and so on, by reading the connection properties file Connections.properties to establish a connection with the database instance. When the system is initialized, the connection is created according to the configuration and placed in the connection pool so that it can be retrieved from the connection pool when it is needed, thus avoiding the overhead associated with the arbitrary establishment and shutdown of the connection.

1.2 Management of connection pools

The connection pool management strategy is the core of the connection pooling mechanism. When the connection pool is established, how to manage the connection in the connection pool and solve the distribution and release of the connection in the connection pool has a great effect on the performance of the system. The reasonable allocation and release of the connection can improve the reuse of the connection, reduce the cost of the system to establish new connections, and also speed up the user's access speed. The following describes the allocation and release policies for connections in a connection pool.

The allocation and release strategy for connection pooling is important for efficient reuse of connections, and the approach we adopt is a well-known design pattern: Reference counting (reference count). This model is widely used in the reuse of resources, and applies this method to the allocation and release of the connection, for each database connection, keep a reference count, to record the number of users of the connection. The specific implementation methods are:

When a client requests a database connection, first see if there is an idle connection in the connection pool (a connection that is not currently assigned). If there is an idle connection, assign the connection to the customer and handle it appropriately (that is, mark the connection as being in use, reference count plus 1). If there is no idle connection, see if the current number of connections is up to maxconn (maximum number of connections), and if not, recreate a client that is connected to the request, and then wait for the set Maxwaittime (maximum wait time) if it is reached. Throws an exception to the user without an idle connection if there is no idle connection after waiting for the maxwaittime.

When a client releases a database connection, it first determines whether the connection is more than the specified number of references. If the connection is removed and the total number of connections in the current connection pool is less than minconn (minimum number of connections), the connection pool is filled if it is less than and the connection is marked as open if it is not exceeded For reuse. It can be seen that it is this set of strategies to ensure the efficient reuse of database connections, to avoid the frequent establishment and release of the connection brought about by the system resources overhead.

1.3 Shutdown of Connection pool

When an application exits, the connection pool should be closed, and the connection object to the database request when the connection pool is established should be returned to the database uniformly (that is, all database connections are closed), which is exactly the reverse process of establishing the connection pool.

1.4 Configuration of connection pools

The number of connections to be placed in the database connection pool to make the system performance better, with Minconn and maxconn to limit it. Minconn is the number of connections created by the connection pool when the application is started, if the startup slows down, but the response is faster after startup, and if the startup speed is too small, the initial user will inevitably slow down the execution speed because there is not enough connection in the connection pool. Therefore, we should set the smaller minconn in the process of development, and set the larger minconn in the practical application. The maxconn is the maximum number of connections in the connection pool and can be determined by repeated tests. To do this, add two methods Getactivesize () and Getopensize () in the connection pool class ConnectionPool, activesize indicate how many connections are being used at a certain time, and opensize indicate how many connections are being opened in the connection pool. Reflects the peak value of the connection pool usage. The two values are reflected in the log information, and the value of the minconn should be less than the average activesize, while the Maxconn value should be between Activesize and Opensize.