JAVA database connection pool (pseudo code, easy to read)

First, Introduction

In recent years, with the rapid development of internet/intranet network technology and the rapid popularization in the world, the shock wave of e-commerce in the world to set off a wave, all kinds of business sites attract a large number of users, business Web site access is increasingly large. This batch-and-concurrency access makes the business Web site more responsive to users, and may even make it impossible for users to log on to the site. The heavy-load business website management company, in addition to improve and optimize the overall performance of the website server, the goal is also to the program design aspect, the database Connectivity performance optimization has become the focus. The cross-platform, portability and security features of the JAVA language make it more and more widely used, especially in network applications. Using the JAVA language-based connection pooling technology is a good solution to the bottleneck of connecting database. It enables efficient management of database connections, resulting in significant improvements in system performance.

Second, the analysis of traditional mode data connection

In general, when using JAVA to develop a database-based Web program, the traditional development model is basically the following steps: First establish a database connection in the main program (such as Servlet, Beans), and then perform SQL operations to insert, modify, and delete objects in the database. The database connection is finally disconnected.

As can be seen from Figure 1, this development model is relatively simple for programming and developers, but there are many problems. For a simple database application, because the database access is not very frequent, only need to create a connection when accessing the database, and then close it, do not significantly increase the overhead of the system. But for a complex database application, the situation is completely different:

• First of all, the frequent establishment and shutdown of the database, will greatly reduce the performance of the system, increase the cost of the system, resulting in a decline in response to the site, serious even cause the crash of the server, become the bottleneck of the system.
• Second, using this traditional pattern, you must also manage every connection to the database to ensure that they are disconnected after they are used. Otherwise, if the program fails to shut down, it will cause a memory leak in the database system and will eventually have to restart the database. 、
• Finally, this development does not control the number of connection objects being created, and system resources are allocated without consideration, such as too many connections, which can lead to memory leaks and server crashes.

Therefore, it is very important to adopt the database technology with higher running speed and more efficient database access to improve the running efficiency of the system.

Third, the connection pool solution

The root cause of the above problems is the inefficient management of the connected resources, the use of data connection pool can be a good solution to such problems, avoid the arbitrary, irregular use of the connection.

A connection pool is a "buffer storage pool" for many connected objects, which is the collection of connected objects, and the core idea is to pre-establish some database-connected objects and put them in memory for use (Figure 2). When a database connection needs to be established in a program, it is only necessary to take one from the memory object (the connection pool) instead of creating it. Again, once you're done, just put it back in memory. Connection pooling is managed by the connection pool itself, while connections are established and disconnected. You can also control the number of connections in the connection pool, the maximum number of uses per connection, and so on, by setting parameters for the connection pool.

The connection pool consists of 3 parts: Connection pool establishment, connection pool connection usage management, connection pool shutdown. Specific analysis covers the following areas:

(i) Establishment of connection pooling

In the application to establish is actually a static connection pool, so-called static refers to the connection pool connection in the system is initialized when the allocation is good, and can not be arbitrarily closed connection. When connection pooling is established, the connection pool is configured to get a preset number of connection objects from the database at once. These connection objects are free connections that the system can allocate, and subsequent connections are taken from the connection pool, which avoids the overhead of arbitrarily establishing and closing connections.

(ii) Allocation of connection pools

When a user needs to access a database, it does not directly connect to the database, but instead requests a connection to the connection pool. The design method of reference counting is adopted here, which is widely used in reusing resources, applying this method to the allocation and release of connections, and keeping a reference count for each database connection to record the number of users of the connection. This is accomplished by first looking at whether there is an idle connection in the connection pool when the user requests a database connection. The idle point here is that the connection has been established but is not currently in use. If there is an idle connection, the connection is assigned to the user and processed accordingly. The primary processing strategy is to set the connection as an assigned state, that is, to mark the connection
For being used, the reference count is added 1. If there is no idle connection, first see whether the number of connections currently open has reached Maxconn (maximum number of connections), if not, to the user to re-create a connection and set it to the assigned state, if it has been reached, only by the set of Maxwaittime (maximum wait time) to wait If there is still no idle connection after waiting for Maxwaittime, the exception to the user is thrown without an idle connection. In order to make the connection Management service more universal, it is necessary to take into account the multi-threaded environment, that is, concurrency problem. In a multi-threaded environment, the connection pool must be guaranteed to manage its own data consistency and the consistency of the internal data, JAVA itself through the synchronized technology provides a good support for this aspect, so it is easy to make thread connection management become efficient and secure.

(iii) User waits

Connection Pool class when the maximum number of connections is reached, the user's request is queued. When waiting for a timeout, a recursive call gets the method of the connection, attempting to get the available connection again.

(iv) Delete policy

The number of connection object dynamic record connections. If the number of times a connection is used exceeds the specified value, the connection is deleted. This is because, in the same connection, each connection to the database data operations, will open up a memory area for its use, when a connection is used too much, the memory overlay area will increase, so use a certain number of times, it is necessary to release resources. In addition, when a connection exceeds the maximum idle time, the connection is deleted, and this is done to avoid wasting memory resources.

(v) Release strategy

When the user frees the database connection, first determine whether the number of references to the connection exceeds the specified value, if the connection is deleted more than the total number of connections in the current connection pool is less than minconn (minimum number of connections), if less than the connection pool is filled, if not more than the connection is marked as open state, Instead of being closed, it can be reused again. It is this strategy that ensures the efficient use of database connections.

(vi) Closing of the connection pool

When the application exits, the connection pool should be closed. The connection object requested at the time the connection pool is established should be returned to the database uniformly (that is, all database connections are closed), which is exactly the opposite of the connection pool establishment.

(vii) configuration of the connection pool

The number of connections the database connection pool is placed on to make the system more performance is a configuration policy. This is limited by Minconn and Maxconn. Minconn is the number of connections created by the connection pool when the app is started, and if it is too large, the boot will slow down, but the response is faster after startup, and if it is too small, the startup is accelerated, but the initial user will inevitably slow down because there is not enough connectivity in the connection pool. The configuration policy is to give an initial number of connection pool connections and the maximum number of connections a connection pool can withstand, depending on the application needs. The connection pooling management policy is at the heart of the connection pooling management mechanism. In particular, the allocation and release of connection pools is a factor that has a significant impact on performance in practical applications. Reasonable allocation and release, can improve the reuse of the connection, avoid the frequent establishment, release the system resource overhead of the connection, but also can make the user access speed.

Iv. specific implementation of the connection pool

First, before establishing a connection pool, we need to determine some basic properties of the database, such as Url,username,password. We can first place these attributes in a file (file name Propfile), and then design a GetProperty class that reads some of the property values from the database in the file propfile. The benefit of this is that the connection pool does not care what type of database The operation is, and the properties of the database. When the type or property of a database changes, we just need to modify the file Propfile. The most important function in the GetProperty class is the constructor, which is the primary function of reading the database's property values from the file propfile. The core code is as follows:

     PublicGetProperty (String infile) {InputStream=Instream.getclass ();        getResourceAsStream (infile); Properties Props=NewProperties; Try{props.load (instream); Driver= Props.getproperty ("Driver"); URL= Props.getproperty ("url"); Username= Props.getproperty ("username"); Password= Props.getproperty ("Password"); }Catch(Exception e) {//output error message return;        }    }

Now we can start to establish the connection pool, the connection pool must provide several basic interfaces, below we give the definition of these interfaces in the form of pseudo-code.

     Public classConnectionPool {PrivateConnection Createconn () {//Create a new connection//first Check if there is no idle connection, if not, then check whether the connection reached the upper limit,//if the number of connections does not reach the upper limit, a new connection is created.         }         Public synchronizedConnection getconnection () {//Put user-freed connections into the connection pool        }         Public synchronized voidrelease () {//Close all Connections        }    }

Once the connection pool is set up, a management class (Connectionpoolmanager) is created to manage the connection pool, and we still give the definition of this class in pseudo-code form.

     Public classConnectionpoolmanager {Private voidinit () {//initialization of the management class        }         PublicConnection getconnection (String poolname) {//get a database connection from the specified connection pool        }         Public voidBackconn (String poolname, Connection conn) {//returns the user-freed connection to the specified connection pool        }         Public synchronized voidClose {//Close a connection pool        }         Public synchronized voidrelease () {//release all the connections        }         Public synchronized voiddestroy () {//destroying a connection pool        }    }

For large, complex and networked database application systems, the proper use of connection pooling technology can greatly improve the execution efficiency of the program.

V. Concluding remarks

In short, because Java has the incomparable advantage of other languages, Java application will be more and more extensive, the author emphatically elaborated the Java database application in the connection pool basic work mechanism, and realizes a basic connection pool, has certain practical and the reference value to the development high performance database application.

excerpt from: Shanghong [Reference] [1] General Yau Ma Digital Technology Co., Ltd. JAVA2 program design. Beijing: Tsinghua University Press, 2002[2] knittings, Hooper. JSP application development examples are detailed. Beijing: Beijing Aerospace University Press, 2002[3] [mei]joe zuffoletto.bea weblogic Server Bible. Beijing: Electronic Industry Press, 2003[4] Li Shixiang. Structs Framework application and development. Dalian: Neusoft Electronics Press, 2007

JAVA database connection pool (pseudo code, easy to read)