Working principle of Database Connection Pool

Abstract This article introduces the working principle of the JDBC-based database connection pool, describes the transaction processing and multi-database servers of the connection pool technology.

Various key technologies propose an efficient connection pool management policy. At last, the specific implementation process of the database connection pool application is described in detail.

Keywords JDBC; database; connection pool; Transaction Processing

With the rapid development and wide application of information technology, database technology is becoming more and more important in the field of information technology, especially network applications.

And the rapid development of e-commerce, database technology is required to support the operation of Dynamic Web sites, and the traditional development mode is: first in the main program (

Such as Servlet and beans). Then, perform SQL operations to query, modify, and delete objects in the database.

And disconnect the database. Using this development mode, for a simple database application, because the database access is not very frequent, only

You need to create a connection when accessing the database and close it after use. This will not significantly increase the system overhead. But for a complex

Database applications are completely different: frequent database establishment and shutdown can greatly reduce system performance and increase system overhead.

To become a bottleneck of the system. In addition, to use this traditional mode, you must manage every connection to the database to ensure that they can be properly closed, such

If a program exception occurs and some connections fail to be closed, the memory in the database system will be leaked and the database will have to be restarted. Therefore

It is vital to use the database technology with faster running speed and higher database access efficiency to improve the system running efficiency.

To solve this problem, the JDBC connection pool technology is proposed in jdbc2.0 to share a group of connections between customers rather

You can generate them when necessary to improve resource usage and improve application response capabilities.

JDBC Overview

JDBC (Java database connectivity, Java database connection) is a Java API used to execute SQL statements.

Relational databases (such as Oracle, Sybase, SQL Server, and access) provide unified access interfaces, which are written in a group of Java languages.

And interfaces, so that database developers can use standard Java APIs to write database applications.

Connection Pool Technology

1. Connection Pool Principle

The core idea of connection pool technology is connection reuse. By establishing a database connection pool and a set of connection usage, allocation, and management policies,

This enables efficient and secure reuse of connections in the connection pool, avoiding the overhead of frequent establishment and shutdown of database connections. In addition

The original connection in JDBC is encapsulated to facilitate the use of connections (especially for transaction processing) by database applications and improve development.

It is precisely because of the existence of this encapsulation layer that the application's processing logic and specific database access logic are isolated to repeat the application itself.

Use is possible. The connection pool consists of three parts (1): The establishment of the connection pool, the use and management of connections in the connection pool, and

Disable. The following focuses on the three parts and the configuration of the connection pool.

1.1 create a connection pool

The connection pool established in the application is actually static. The so-called static connection pool means that the connections in the connection pool are allocated points during system initialization.

Configure and do not close the connection at will. Java provides many container classes to easily build connection pools, such as vector, stack, and servlet.

, Bean, etc., by reading the connection property file connections. properties to establish a connection with the database instance. During system initialization

Should be configured to create a connection and placed in the connection pool, so that you can obtain it from the connection pool when necessary, so that you can avoid arbitrary connection establishment,

Overhead caused by disabling.

1.2 connection pool management

The connection pool management policy is the core of the connection pool mechanism. After the connection pool is established, how can we manage the connections in the connection pool to solve the problem?

The distribution and release of internal connections have a great impact on the system performance. Reasonable Distribution and release of connections can improve the reuse of connections and reduce system construction.

The overhead of a new connection also accelerates user access. The following describes the connection allocation and release policies in the connection pool.

The allocation and release policies of connection pools are very important for the effective reuse of connections. The method we adopt is a well-known design model:

Reference counting ). This mode is widely used in resource reuse. It applies this method to connection allocation.

When the database is released, a reference count is retained for each database connection to record the number of users of the connection. The specific implementation method is as follows:

When a customer requests a database connection, first check whether there is any idle connection in the connection pool (that is, the connection is not allocated currently ). If yes

If the connection is idle, the connection is allocated to the customer and processed accordingly (that is, the connection is marked as in use, and the reference count is increased by 1 ). If there is no idle connection

Then, check whether the current number of connections has reached maxconn (maximum number of connections). If not, create a new connection

Target customers; if it reaches, it will wait according to the set maxwaittime (maximum wait time), if it remains idle after waiting for maxwaittime

Connection, and an exception Without idle connection is thrown to the user.

When a customer releases a database connection, the customer first determines whether the number of referenced connections has exceeded the specified value. If so, the customer deletes the connection and determines

Whether the total number of connections in the current connection pool is less than minconn (minimum number of connections). If the number is less than, the connection pool is full. If the number does not exceed, the connection

Marked as open for reuse. It can be seen that this policy ensures the effective reuse of database connections and avoids frequent establishment and release.

System resource overhead caused by the put connection.

1.3 close connection pool

When the application exits, the connection pool should be closed. At this time, the connection objects applied to the database during connection pool establishment should be returned to the database in a unified manner.

(Close all database connections), which is the opposite process of establishing a connection pool.

1.4 connection pool Configuration

How many connections must be placed in the database connection pool to improve system performance. Use minconn and maxconn to limit the number of connections. Minconn

It is the number of connections created in the connection pool when the application is started. If it is too large, the startup will slow down, but the response will be faster after the application is started. If it is too small, the startup will speed up.

But the initial user will inevitably delay the execution because there is not enough connection in the connection pool. Therefore, during the development process

Minconn is smaller, while minconn is larger in actual applications. Maxconn is the maximum number of connections in the connection pool.

Verify to determine this saturation point. Therefore, two methods getactivesize () and getopensize () are added to the connection pool connectionpool (),

Activesize indicates how many connections are being used at a time, and opensize indicates how many connections are opened in the connection pool, reflecting

The peak value used. When the two values are reflected in the log information, the value of minconn should be smaller than the average activesize, and the value of maxconn should

Between activesize and opensize.

2. Key Connection Pool Technologies

2.1 Transaction Processing

We have discussed how to use a database connection to access a common database. For transaction processing, the situation becomes more complex. Because

In this case, database operations must comply with the "all-nothing" principle, that is, they must be completed in full.

Nothing. If we simply adopt the above method of connection reuse, a problem will occur, because there is no way to control

Actions of multiple database operation methods. These database operations may be performed on multiple connections, and these connections may be performed by other non-transaction parties.

Method reuse. Connection itself provides support for transactions. You can set the autocommit attribute of connection to false,

Call the commit or rollback method explicitly. However, to securely and efficiently reuse connections, you must provide corresponding transaction support.

Mechanism. The method is: using an explicit transaction support method, each transaction excludes a connection. This method can greatly reduce the transaction processing

And does not affect the reuse of connections.

The Connection Management Service provides an explicit transaction start/end (commit or rollback) statement and a transaction registry for registration.

The ing between the transaction initiator and the connection used by the transaction. Through this table, the transaction part and the connection management part are isolated, because the table is

Dynamically generated during running based on actual call conditions. The connections used by transactions cannot be reused during the transaction operation. In implementation, the user ID

Is identified by the user's thread. All subsequent accesses to the database are directed to the Registry and allocated connections are used.

. When the transaction ends, the corresponding table items are deleted from the registry.

2.2 Encapsulation

From the above discussion, we can see that common database methods and transaction methods have different connection usage (distribution and release), so that

And provides consistent operation interfaces externally. we encapsulate the connections: normal connections and transaction connections, and use the powerful

Object-oriented feature: polymorphism. Both normal connections and transaction connections implement a dbconnection interface. For the methods defined in the interface

Different implementations are made based on their own characteristics, so that the connection processing is very consistent.

2.3 concurrency

To make the connection management service more universal, we must consider the multi-threaded environment, that is, the concurrency issue. In a multi-threaded Environment

The connection management data consistency and connection internal data consistency must be ensured. Java provides good support in this respect.

(Synchronized keyword), which makes it easy to make connection management thread security.

More than 2.4 database servers

In practical applications, applications often need to access multiple different databases. How to access different databases through the same connection pool?

A core issue that applications need to solve. The following describes a solution:

First, define a database connection pool parameter class, and define the JDBC Driver Class Name, connection URL, and user name and password of the database.

And so on. This class is a parameter used to initialize the connection pool:

 

Public class connectionparam implements serializable {
// Definitions of initialization parameters
}

The second is the connectionfactory class of the connection pool, which maps a connection pool object with a name.

You can get the specified connection pool object by using this name. The main code is as follows:

 

Public class connectionfactory {
Static hashtable connectionpools = // used to save the relationship between the data source name and the connection pool object
Public static datasource Lookup (string datasourcename) throws
Namenotfoundexception {
// Find the data source named datasourcename
}

Public static datasource BIND (string name, connectionparam PARAM)
Throws exception
// Bind the name to the connection pool object initialized using Param
}

Public static void unbind (string name) throws namenotfound
Exception {
// Delete the connection pool object bound with the name
}
Implementation of connection pool applications

A complete connection pool application consists of three parts: the dbconnectionpool class, which is responsible for obtaining (or creating) connections from the connection pool and returning the connections

The dbconnectionmanager class is responsible for loading and registering JDBC drivers,

Create a dbconnectionpool Based on the attributes defined in the property file, and track the application's reference to the connection pool.

.

The database connection pool implemented in this article includes a management class dbconnectionmanager, which is responsible for providing multiple connection pool objects

(Dbconnectionpool class. Each connection pool object manages a set of encapsulated JDBC connection objects conn and encapsulated JDBC connections.

The connected object conn can be shared by any number of model layer components.

The conn class is designed as follows:

 

Class conn {
Private java. SGL. Connection con; // database connection object
Public Boolean inuse; // whether it is used
Public long lastaccess; // The last time the connection was released.
Public int usecount; // number of times used
}

The following code implements the connection pool:

 

// Initialize the database connection pool

Public static synchronized void fastinitpool ()
Throws exception {
Try {class. forname (driver );
For (INT I = 0; I <size; I ++ ){
Connection con = createconnection ();
If (con! = NULL) addconnection (CON );
}}}

// Add a database connection to the connection pool object

Private Static void addconnection (connection con ){
If (pool = NULL | pool1 = NULL ){
Pool = new vector (size );
Pool1 = new vector (size );}
Pool. addelement (CON );
Pool1.addelement ("false ");}

// Obtain the database connection

Public static synchronized connection getconn ()
Throws exception {
Connection conn = NULL;
Try {If (driver = NULL)
Fastinitpool ();
// Obtain an available (idle) Connection
. For (INT I = 0; I <pool. Size (); I ++ ){
Conn = (connection) pool. elementat (I );
If (pool1.elementat (I) = "false "){
Pool1.set (I, "true ");
// System. Out. println ("obtain the nth value from the connection pool" + (I + 1) + "idle connection ");
Return conn;
}
}
// If no connection is available and the number of existing connections is smaller than the maximum number of connections, create and add a new connection pool
Conn = createconnection ();
Pool. addelement (conn );
Pool1.addelement ("true ");
// System. Out. println ("all connections are in use and a new connection is created in the connection pool ");
}

Catch (exception e ){
System. Err. println (E. getmessage ());
Throw new exception (E. getmessage ());
}
Return conn; // return a valid new connection.
}

Public connection getconnection (string strdriver, string strurl, string strusername, string

Strpassword)
Throws sqlexception {
Try {class. forname (strdriver );
Conn = drivermanager. getconnection (strurl, strusername, strpassword );}
Return conn ;}

Conclusion

At present, web applications widely adopt the B/S structure, and the concurrency determines that multiple users access the database at the same time. JDBC-based

The database connection pool technology has been successfully applied to the web-based Higher Vocational teaching system development, and the database connection pool instance has been set up to explain and confirm

Connection Pool access method. Only by making full use of the connection pool access technology can we improve database access efficiency, improve web applications, and thus reduce the number

System overhead, greatly improving the running efficiency of the entire web application system.