Research and Application of JDBC-based database connection pool technology

Introduction

In recent years, with the rapid development of Internet/Intranet network construction technology and the rapid popularization in the world

Applications have been switched from traditional desktop applications to Web applications. The layer-3 development mode based on the Browser/Server architecture gradually replaces the development mode of the C/S (Client/Server) architecture, it has become a widely used technology for developing enterprise-level applications and e-commerce. In the early days of Web application development, the main technologies used were CGI, ASP, and PHP. Later, Sun introduced the Java-based Servlet + Jsp + JavaBean technology. Compared with traditional development technologies, it features cross-platform, secure, effective, and portable features, making it easier to use and develop.

How Java applications access the database

In Java, JDBC (Java DataBase Connection) serves as a bridge between applications and databases,

That is, the Java language accesses the database through the JDBC technology. JDBC is an open solution that provides a standard application design interface for database application developers and Database Front-End Tool developers, this allows developers to write complete database applications in pure Java. JDBC provides two types of APIS, namely for developers and for the underlying JDBC driver API, the underlying mainly through direct JDBC driver and JDBC-ODBC bridge driver to achieve the connection with the database.

Generally, the Java application accesses the database (1) as follows:

① Load the database driver;

② Establish a database connection through JDBC;

③ Access the database and execute SQL statements;

④ Disconnect the database.

Figure 1 Java Database Access Mechanism

As a database access technology, JDBC is easy to use. However, this mode is used for Web applications.

There are many problems in program development: first, each Web request requires a database connection. Establishing a connection is a time-consuming activity, and it takes seconds each time ~ 1 S, and the system also needs to allocate memory resources. This time may not feel the system overhead for one or several database operations. However, for today's Web applications, especially large e-commerce websites, it is normal that hundreds or even thousands of people are online at the same time. In this case, frequent database connection operations will inevitably occupy a lot of system resources, and the website's response speed will inevitably decrease, which may even cause server crashes. This is a technical bottleneck restricting the development of some e-commerce websites. Second, each database connection must be closed after use. Otherwise, if the program fails to be closed due to an exception, the memory in the database system may leak and the database will have to be restarted. In addition, such development cannot control the number of connection objects to be created, and system resources will be allocated out without consideration. For example, too many connections may also cause memory leakage and server crash.

How database connection pool works

1. Basic Concepts and Principles

From the above analysis, we can see that the root cause of the problem is the inefficient management of database connection resources. We know that,

There is a well-known design pattern for shared resources: Resource Pool ). This mode is designed to solve the problems caused by frequent resource allocation and release. To solve the above problems, you can use the database connection pool technology. The basic idea of the database connection pool is to create a "buffer pool" for the database connection ". A certain number of connections are put in the buffer pool in advance. When you need to establish a database connection, you only need to extract one from the "buffer pool" and put it back after use. We can set the maximum number of connections in the connection pool to prevent endless connections to the database. More importantly, we can monitor the number and usage of database connections through the connection pool management mechanism to provide a basis for system development, testing and performance adjustment. For the basic working principle of the Connection Pool, see 2.

Figure 2 basic working principle of the Connection Pool

2. Connection Pool provided by the server

The jdbc api does not provide the connection pool method. Some large WEB application servers such as BEA's WebLogic and IBM WebSphere provide connection pool mechanisms, but they must have third-party dedicated class methods to support connection pool usage.

Key issues in connection pool

1. Concurrency Problems

To maximize the versatility of the connection management service, you must consider the multi-threaded environment, that is, the concurrency issue. This problem is relatively well solved, because the Java language itself provides support for concurrent management, using the synchronized keyword can ensure that the thread is synchronized. The usage is to add the synchronized keyword directly before the class method, for example:

Public synchronized Connection getConnection ()

2. Multiple database servers and multiple users

For large enterprise applications, it is often necessary to connect different databases (such as Oracle and Sybase) at the same time ). How to connect to different databases? Our strategy is to design a single-instance connection pool management class and read a resource file when the unique instance of the connection pool management class is created, the resource file contains the url addresses of multiple databases (<poolName. url>), user name (<poolName. user>), password (<poolName. password>. For example, tx. url = 172.21.15.123: 5000/tx_it, tx. user = yang, tx. password = yang321. Create multiple connection pool instances based on the information provided by the resource file. Each instance is a connection pool of a specific database. The connection pool management instance takes a name for each connection pool instance and uses different names to manage different connection pools.

When multiple users in the same database use different names and passwords to access the database, you can also use resource file processing to set multiple URLs in the resource file, database connection information with different user names and passwords.

3. Transaction Processing

As we know, transactions are atomic, and database operations must comply with the "ALL-NOTHING" principle, that is, for a group of SQL statements, either ALL or no.

In Java, the Connection class provides transaction support. You can set the AutoCommit attribute of Connection to false, and then explicitly call the commit or rollback method. However, for efficient Connection reuse, a corresponding transaction support mechanism must be provided. Each transaction can be exclusively connected. This method can greatly reduce the complexity of transaction management.

4. Connection Pool allocation and release

The allocation and release of the connection pool have a great impact on the system performance. Reasonable Allocation and release can increase the reusability of connections, reduce the overhead of establishing new connections, and speed up user access.

Idle pools can be used for connection management. That is, the connections that have been created but have not been allocated are stored in an idle pool at the creation time. When a user requests a connection, the system first checks whether there is any idle connection in the idle pool. If there is one, allocate the connection with the longest creation time (stored in the container order) to it (actually, determine whether the connection is valid first, and assign it to the user if it is available, if the connection pool is unavailable, delete the connection from the idle pool and re-check whether there are connections in the idle pool). If not, check whether the connection pool currently opened has reached the maximum number of connections allowed by the connection pool (maxConn ), if the connection fails, a new connection is created. If the connection is reached, a certain time (timeout) will be waited ). If a connection is released within the waiting time, the connection can be allocated to the waiting user. If the waiting time exceeds the specified time timeout, a null value is returned ). The system only counts connections that have been allocated and are in use, and returns the connections to the idle pool after use. For the idle connection status, special thread timing detection can be opened up, which will consume a certain amount of system overhead, but can ensure a fast response speed. You can also choose not to open up special threads, but to detect them before allocation.

5. Configuration and maintenance of the Connection Pool

How many connections should be placed in the connection pool to optimize system performance? You can set minConn and maxConn to control connections in the connection pool. The minimum number of connections is the number of connections created in the connection pool when the system starts. If too many instances are created, the system starts slowly, but the system responds quickly after creation. If too few instances are created, the system starts quickly and responds slowly. In this way, you can set a smaller minimum number of connections during development, and the development will be faster, while the system will set a larger number in actual use, because it will be faster for customers to access. The maximum number of connections is the maximum number of connections allowed in the connection pool. The specific setting depends on the System Access traffic. You can find the best point through repeated tests.

How can we ensure the minimum number of connections in the connection pool? There are two policies: Dynamic and Static. Dynamically checks the connection pool at a certain time. If the number of connections is smaller than the minimum number of connections, the corresponding number of new connections are added to ensure the normal operation of the connection pool. Static checks are performed when idle connections are not enough.

Connection Pool implementation

1. Connection Pool Model

The connection pool discussed in this article includes a connection pool class (DBConnectionPool) and a connection pool management class (DBConnetionPoolManager ). The connection pool class is the "buffer pool" for all connections to a database. It mainly implements the following functions: ① obtaining or creating available connections from the connection pool; ② returning the connection to the connection pool after use; ③ disconnect all connections and release the system resources occupied by connections before the system is shut down; ④ it can also handle invalid connections (originally registered as available connections, which are no longer available for some reason, such as timeout, communication problems), and can limit the total number of connections in the connection pool to be no less than a predetermined value and not more than a predetermined value.

The connection pool management class is the wrapper of the connection pool class. It complies with the singleton mode, that is, the system can only have one instance of the connection pool management class. It is mainly used to manage multiple connection pool objects and has the following functions: ① load and register the JDBC driver for a specific database; ② create a connection pool object based on the information given in the property file; ③ to facilitate the management of multiple connection pool objects, a name is given for each connection pool object to realize the ing between the connection pool name and its instance; ④ tracking the customer's use of connections, in this case, you need to close the connection to release resources. Connection Pool Management is introduced to facilitate the use and management of multiple connection pools. For example, the system needs to connect to different databases or the same database. However, due to security issues, different users must use different names and passwords.

2. Connection Pool implementation

The following describes the main attributes of the connection pool class and connection pool management class and the basic interface to be implemented:

Public class DBConnectionPool implements TimerListener {
Private int checkedOut; // Number of allocated connections
Private ArrayList freeConnections = new ArrayList (); // container, idle pool, store the connections that have been created but not allocated according to // Creation Time Sequence
Private int minConn; // the minimum number of connections in the connection pool
Private int maxConn; // The maximum number of connections allowed in the connection pool
Private String name; // name the connection pool for convenient management
Private String password; // password required for database connection
Private String url; // the address of the database to be connected
Private String user; // user name required for database connection
Public Timer timer; // Timer
Public DBConnectionPool (String name, String URL, String user, String
Password, int maxConn) // public Constructor
Public synchronized void freeConnection (Connection con) // after use, // return the Connection to the idle pool
Public synchronized Connection getConnection (long timeout) // get a Connection, // timeout is the waiting time
Public synchronized void release () // disconnect all connections and release occupied system resources
Private Connection newConnection () // create a database Connection
Public synchronized void TimerEvent () // timer event processing function

}

Public class DBConnectionManager {
Static private DBConnectionManager instance; // unique instance of the connection pool management class
Static private int clients; // number of customers
Private ArrayList drivers = new ArrayList (); // container, which stores the database driver

Private HashMap pools = new HashMap (); // access the connection pool in the form of name/value // Object name and connection pool object
Static synchronized public DBConnectionManager getInstance () // If the unique // instance has been created, this instance is directly returned. Otherwise, the private constructor is called to create a unique instance of the connection pool management class.

Private DBConnectionManager () // private constructor, in which the initialization function init () is called ()

Public void freeConnection (String name, Connection con) // release a Connection, // name is the name of a Connection pool object

Public Connection getConnection (String name) // obtain a Connection from the Connection pool object named name //

Public Connection getConnection (String name, long time) // name from

// Gets a connection from the connection pool object. time is the waiting time.

Public synchronized void release () // release all resources

Private void createPools (Properties props) // create one or more connection pools based on the information provided in the property File

Private void init () // initialize the unique instance of the connection pool management class, called by the private constructor

Private void loadDrivers (Properties props) // load the database driver

}

3. connection pool usage

How can the connection pool implemented above be applied to the system during program development? The following uses Servlet as an example to describe how to use the connection pool.

The Servlet's life cycle is to call its initialization (init) method when starting to establish a servlet. Each user request causes a thread to call the service method of the Instance created earlier. Finally, when the server decides to uninstall a servlet, it first calls the destroy method of the servlet.

Based on servlet features, we can generate a unique instance of the connection pool management class in the initialization function (including creating one or more connection pools ). For example:

Public void init () throws ServletException
{
ConnMgr = DBConnectionManager. getInstance ();
}

Then, you can use the connection pool name in the service method to perform database operations. Finally, release the occupied system resources in the destroy method, for example:

Public void destroy (){
ConnMgr. release (); super. destroy ();
}

Conclusion

Database connection management is a challenge in database-related application development using JDBC. In many cases, the overhead of system resources caused by chaotic management of connections becomes a bottleneck restricting the efficiency of large-scale enterprise applications. For Web applications accessed by many users, the efficiency and stability of systems using database connection technology are much better than those using other traditional methods. This article describes the technology of using JDBC to access the database, discusses the key issues of database connection management based on the connection pool technology, and provides an implementation model. The article provides a basic mode for the connection pool management program. In order to improve the overall performance of the system, many meaningful extensions can be made on this basis.