A method of realizing database connection pool (1)

Data | database | Database connection database connection pool in writing application services is often required to use the module, too frequent connection of the database is a bottleneck in terms of service performance, the use of buffer pool technology can be used to eliminate this bottleneck. We can find many sources of database connection pools on the Internet, but we all find this common problem: the implementation of these connection pools increases the degree of coupling with the user in varying degrees. Many of the connection pools require the user to obtain the database connection through its prescribed method, which we can understand, after all, the way that all the application servers take the database connection is realized in this way. Another common problem, however, is that they do not allow the user to explicitly invoke the Connection.close () method, but rather to close the connection using one of its specified methods. This approach has two disadvantages:

First: Changed the user usage habit, increased the user's use difficulty.

First, let's take a look at a normal database operation process:


int ExecuteSQL (String sql) throws SQLException
{
Connection conn = getconnection (); Get a database connection in some way
PreparedStatement PS = null;
int res = 0;
try{
PS = conn.preparestatement (SQL);
res = Ps.executeupdate ();
}finally{
try{
Ps.close ();
}catch (Exception e) {}
try{
Conn.close ()//
}catch (Exception e) {}
}
return res;
}




After using the database connection, the consumer typically calls the connection's method close directly to free the database resource, and if we use the connection pooling implementation method that we mentioned earlier, the statement conn.close () will be replaced by some specific statements.

Second: The connection pool cannot be exclusive control of all connections in. Because the connection pool does not allow the user to directly invoke the connected Close method, the connection pool will not maintain the state of all connections properly, and the problem is more likely to occur when the connection pool and application are implemented by different developers, as long as the database connection is closed directly by a custom problem during use.

Combining the two issues mentioned above, let's discuss how to solve these two deadly problems.

First of all, we put ourselves in the right place to consider how users would like to use this database connection pool. A user can obtain a connection to a database in a specific way, while the type of the connection should be the standard java.sql.Connection. The user can take any action on this connection after obtaining the connection to the database, including closing the connection, and so on.

By describing the user, how to take over the Connection.close method has become the subject of our article.

In order to take over the Close method of database connection, we should have a mechanism similar to hooks. For example, in Windows programming, we can use the hook API to implement a takeover of a Windows API. There is also a mechanism in Java. Java provides a proxy class and a invocationhandler, all two classes in the Java.lang.reflect package. Let's take a look at how the documentation provided by Sun Company describes these two classes.


public interface Invocationhandler

Invocationhandler is the interface implemented by the invocation handler of a proxy instance.

Each proxy instance has a associated invocation handler.
When a is invoked on a proxy instance,
The method invocation are encoded and dispatched to the Invoke method of it invocation handler.




There are a lot of descriptions of proxy in Sun's API documentation, not listed here. Through the description of the interface Invocationhandler by the document we can see that the Invoke method of the Invocationhanlder is triggered when a method that invokes a proxy instance is invoked. From the Java documentation We also learned that this dynamic proxy mechanism can only take over the interface method, but not the general class, considering that Java.sql.Connection itself is also an interface, thus finding a way to solve how to take over the Close method.

First, we define a class of database connection pool parameters, define the JDBC driver class name for the database, the URL of the connection, the username password, and so on, which are the parameters for initializing the connection pool, which are defined as follows:


public class Connectionparam implements Serializable
{
Private String driver; Database driver
Private String URL; The URL of the data connection
Private String user; Database user Name
private String password; Database Password
private int minconnection = 0; Initialize the number of connections
private int maxconnection = 50; Maximum number of connections
Private long TimeOutValue = maximum idle time for 600000;//connection
Private long waittime = 30000; When the connection is taken, the maximum wait time if no connection is available