Research and design of database connection pool technology based on JDBC

Design | data | database | database connection Pick to beThis 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.

Key WordsJDBC; 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 electronic 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, 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 the client releases the database connection, first determine whether the connection is more than the specified number of references, if more than the deletion of the connection, and determine whether the current connection pool total number of connections is less than minconn (minimum number of connections), if less than the connection pool is filled; if not more than the connection to mark the open state, Available 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.

2, the key technology of connection pool

2.1 Transaction Processing

The previous discussion is about using database connections for common database access. For transaction processing, the situation becomes more complex. Because the transaction itself requires the assurance of principle, at this time requires the operation of the database in line with the "all-all-nothing" principle, that is, either complete or do nothing. If you simply use the above strategy for connection reuse, there is a problem, because there is no way to control the actions of multiple database manipulation methods that belong to the same transaction, which may be done on multiple connections, and these connections may be reused by other non transaction methods. The connection itself provides support for transactions, which can be implemented by explicitly invoking a commit or Rollback method by setting the connection Autocommit property to False. However, it is necessary to provide the corresponding transaction support mechanism for the safe and efficient connection reuse. By using explicit transaction support methods, each transaction is exclusive to one connection. This approach can greatly reduce the complexity of transaction processing and will not interfere with the reuse of the connection.

The Connection Management Service provides an explicit transaction start, end (commit or rollback) declaration, and a transaction registry that registers the correspondence between the transaction initiator and the connection used by the transaction, through which the part of the transaction and the Connection Management section are isolated, Because the table is dynamically generated at run time based on the actual invocation. A transaction uses a connection that cannot be reused in this transaction. In the implementation, the user identity is identified by the thread in which the consumer is located. All subsequent accesses to the database are done by looking up the registry and using the connections that have already been allocated. Deletes the corresponding table entry from the registry when the transaction ends.

2.2 Package

As can be seen from the above discussion, common database methods and transaction methods for the use of the connection (allocation, release) is different, in order to facilitate the use of the external provision of a consistent operating interface, we have to encapsulate the connection: Common connection and transaction connectivity, and take advantage of Java in the powerful object-oriented characteristics: polymorphism. Both the common connection and the transaction connection have implemented a DbConnection interface, and the methods defined in the interface are implemented differently according to their own characteristics, so they are very consistent in the processing of the connection.

2.3 Concurrent

To make the connection Management service more versatile, we have to take into account the multithreaded environment, the concurrency problem. In a multi-threaded environment, you must ensure that the connection management of its own data consistency and connection internal data consistency, in this regard Java provides good support (synchronized keyword), so it is easy to make connection management thread-safe.

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 is a core issue that the application needs to address. Here's a way to fix this:

First, a class that defines a database connection pool parameter, defines the database's JDBC driver class name, the URL of the connection, and the username password, and so on, which is the parameter used to initialize the connection pool:

public class Connectionparam implements serializable{
Definitions of each initialization parameter
}
The second is the factory class connectionfactory of the connection pool, by which a connection pool object is mapped to a name that allows the user to obtain the specified connection pool object, with the following main code implemented:

public class connectionfactory{
static Hashtable Connectionpools =//relationship to hold data source name and connection pool object
public static DataSource lookup (String datasourcename) throws
namenotfoundexception{
Find a data source with the name DataSourceName
}

public static DataSource bind (String name, Connectionparam param)
Throws Exception
Bind name names to connection pool objects initialized with Param
}

public static void Unbind (String name) throws Namenotfound
exception{
Delete the connection pool object that is bound to the first name
}

Implementation of Connection Pool application

A complete connection pool application consists of three parts: the Dbconnectionpool class, which is responsible for obtaining (or creating) a connection from the connection pool, returning the connection to the connection pool, shutting down all connections when the system shuts down, releasing all resources; Dbconnectionmanager class, Responsible for loading and registering JDBC drivers, creating dbconnectionpool based on properties defined in the properties file, tracking application references to connection pools, and so on, and using the application for connection pooling.

The database connection pool implemented in this article includes a management class Dbconnectionmanager that provides the interface between multiple connection pool objects (Dbconnectionpool classes). Each connection pool object manages a set of encapsulated JDBC Connection Objects Conn, and the encapsulated JDBC Connection object Conn can be shared by any number of model layer components.

The design of the class Conn is simple, as follows:

Class Conn {
Private java. SGL. Connection con; Database Connection objects
Public Boolean InUse; Whether to be used
public long lastaccess; Last time the connection was released
public int usecount; Number of times to be used
}
Here is the main code to implement the connection pool:

Initializing a 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);
} } }

To add a database connection to a 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"); }

Getting a database connection

public static synchronized Connection Getconn ()
Throws Exception {
Connection conn = null;
try {if (Driver = null)
Fastinitpool ();
Get an available (free) 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 ("Get First" + (i+1) + "idle connection" from the connection pool);
Return conn;
}
}
Create and add a new connection to the connection pool if no connection is available and the number of connections is less than the maximum number of connections
conn = CreateConnection ();
Pool.addelement (conn);
Pool1.addelement ("true");
System.out.println ("All connections are in use, create a new connection in the connection pool");
}

catch (Exception e) {
System.err.println (E.getmessage ());
throw new Exception (E.getmessage ());
}
Return conn; Returns 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; }
　　 Concluding remarks

The current web application widely uses B/s structure, its concurrency determines the simultaneous access of multiple users to the database problem. The database connection pool technology based on JDBC has been successfully applied in the development of web-based higher vocational teaching system, and a database connection pool instance is established to illustrate and confirm the access method of the connection pool. Only by making full use of the connection pool access technology can we improve the access efficiency of the database, improve the Web application, reduce the overhead and greatly improve the running efficiency of the whole Web application system.