Implementing a database connection pool using dynamic proxies in Java

December 05, 2002

The

author implements the database connection pool by using dynamic proxies in Java, allowing the consumer to use the connection pool in a normal JDBC connection usage.

Database connection pooling is a frequently needed module in writing application services, and too frequent connection databases are a bottleneck for service performance, and buffer pool technology can be used to eliminate this bottleneck. We can find many source programs on the Internet about database connection pools, but we all find that the common problem is that the implementation methods of these connection pools increase the degree of coupling with the users to 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 current application server to access the database connection in this way is implemented. Another common problem, however, is that they do not allow the user to explicitly invoke the Connection.close () method, but need to use one of its prescribed methods to close the connection. There are two drawbacks to this approach:

First: Change the user usage habits, increase 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();//通过某种方式获取数据库连接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 the user has used the database connection, it is usually a direct call to the connection method close to release the database resources, if using the connection pool implementation method we mentioned earlier, the statement conn.close () will be replaced by some specific statements.

Second: The connection pool cannot have exclusive control over all connections in it. Because connection pooling does not allow users to directly invoke the connected Close method, the connection pool will not maintain the state of all connections as soon as the user has directly closed the database connection due to customary problems in the course of use, and it is more likely that the connection pool and application are implemented by different developers.

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

First of all, let's think about how users want to use this database connection pool. A user can obtain a connection to a database in a specific way, and the type of the connection should be the standard java.sql.Connection. The user can take any action on the connection, including closing the connection, after acquiring the database connection.

Through the description of user use, how can take over the Connection.close method is the subject of our article.

To take over the Close method of the 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 such a mechanism in Java. Java provides a proxy class and a invocationhandler, which are all two classes in the Java.lang.reflect package. Let's take a look at how the documentation provided by Sun describes the two classes.

public interface InvocationHandlerInvocationHandler is the interface implemented by the invocation handler of a proxy instance. Each proxy instance has an associated invocation handler. When a method is invoked on a proxy instance, the method invocation is encoded and dispatched to the invoke method of its invocation handler.

There are many descriptions of the proxies in Sun's API documentation, which are not listed here. Through the documentation of the interface Invocationhandler we can see that the Invoke method of Invocationhanlder is triggered when a method of invoking a proxy instance is invoked. From the Java documentation We also learned that this dynamic agent mechanism can only take over the interface method, and the general class is not valid, considering that the java.sql.Connection itself is an interface thus found a solution to how to take over the close method of the way out.

First, we define a database connection pool parameter class, defines the database JDBC driver class name, the connection URL and user name password, and so on some information, this class is used to initialize the connection pool parameters, specifically defined as follows:

public class ConnectionParam implements Serializable{private String driver;//数据库驱动程序private String url;//数据连接的URLprivate String user;//数据库用户名private String password;//数据库密码private int minConnection = 0;//初始化连接数private int maxConnection = 50;//最大连接数private long timeoutValue = 600000;//连接的最大空闲时间private long waitTime = 30000;//取连接的时候如果没有可用连接最大的等待时间

The next is the factory class connectionfactory of the connection pool, through which a connection pool object is mapped to a name, and the user can obtain the specified connection pool object by that name, with the following code:

/** * Connection Pool class factory, this class is commonly used to hold multiple data source names matching database connection pool hash * @author Liusoft */public class connectionfactory{// The hashtable is used to save the data source name and the connection pool object's relational table static Hashtable Connectionpools = Null;static{connectionpools = new Hashtable (2,0.75f);}/** * Gets the connection pool object that corresponds to the specified name from the connection pool factory * @param the name of the DataSource Connection pool Object * @return DataSource Returns the connection pool object with the name * @throws Namenotfoundexception Unable to find the specified connection pool */public static DataSource lookup (String DataSource) throws namenotfoundexception{ Object ds = Null;ds = Connectionpools.get (DataSource); if (ds = = NULL | |! ( DS instanceof DataSource)) throw new Namenotfoundexception (DataSource); return (DataSource) DS;} /** * Binds the specified name and database connection configuration together and initializes the database connection pool * @param name corresponds to the connection pool names * @param the configuration parameters of the Param connection pool, see class Connectionparam * @return DATASOURC e If the connection pool object is returned after the binding is successful * @throws namealreadyboundexception the name is already bound and throws the exception * @throws ClassNotFoundException unable to find the driver class in the connection pool configuration * @throws the driver class in Illegalaccessexception connection pool configuration is incorrect * @throws Instantiationexception Unable to instantiate driver class * @throws SqlException cannot connect to the specified database */public static DataSource bind (String name, ConnEctionparam param) throws Namealreadyboundexception,classnotfoundexception,illegalaccessexception, Instantiationexception,sqlexception{datasourceimpl Source = null;try{lookup (name); throw new Namealreadyboundexception (name);} catch (Namenotfoundexception e) {Source = new Datasourceimpl (param); source.initconnection (); Connectionpools.put (name , source);} return source;} /** * rebind database connection pool * @param name corresponds to the connection pool names * @param param connection Pool configuration parameters, see class Connectionparam * @return DataSource If the binding is successful, return the connection pool object * @t Hrows Namealreadyboundexception A certain name is already bound and throws the exception * @throws ClassNotFoundException cannot find the driver class in the connection pool configuration @throws Illegalaccessexception driver class in connection pool configuration * @throws instantiationexception Unable to instantiate driver class * @throws SqlException cannot connect to the specified database properly */ public static DataSource rebind (String name, Connectionparam param) throws Namealreadyboundexception, Classnotfoundexception,illegalaccessexception,instantiationexception,sqlexception{try{unbind (name);} catch (Exception e) {}return bind (name, param);} /** * Delete a database connection pool object * @param name * @throwsNamenotfoundexception */public static void Unbind (String name) throws Namenotfoundexception{datasource DataSource = Lookup (name), if (DataSource instanceof Datasourceimpl) {Datasourceimpl DSi = (Datasourceimpl) datasource;try{dsi.stop ( );d si.close ();} catch (Exception e) {}finally{dsi = null;}} Connectionpools.remove (name);}}

ConnectionFactory mainly provides the user to bind the connection pool to a specific name and unbind the operation. Users only need to care about these two classes to use the database connection pool functionality. Below we give a section on how to use the Connection pool code:

String name = "pool";String driver = " sun.jdbc.odbc.JdbcOdbcDriver ";String url = "jdbc:odbc:datasource";ConnectionParam param = new ConnectionParam(driver,url,null,null);param.setMinConnection(1);param.setMaxConnection(5);param.setTimeoutValue(20000);ConnectionFactory.bind(name, param);System.out.println("bind datasource ok.");//以上代码是用来登记一个连接池对象，该操作可以在程序初始化只做一次即可//以下开始就是使用者真正需要写的代码DataSource ds = ConnectionFactory.lookup(name);try{for(int i=0;i<10;i++){Connection conn = ds.getConnection();try{testSQL(conn, sql);}finally{try{conn.close();}catch(Exception e){}}}}catch(Exception e){e.printStackTrace();}finally{ConnectionFactory.unbind(name);System.out.println("unbind datasource ok.");System.exit(0);}

As you can see from the user's sample code, we've solved two problems with regular connection pooling. But the most we care about is how to solve the approach of taking over the Close method. Two lines of code that take over the job mainly in ConnectionFactory:

source = new DataSourceImpl(param);source.initConnection();

Datasourceimpl is a class that implements the interface Javax.sql.DataSource, which maintains a connection pool object. Because the class is a protected class, the method that it exposes to the consumer is only the method defined in interface DataSource, and all other methods are not visible to the consumer. Let's take care of a user-accessible approach getconnection

/** * @see javax.sql.datasource#getconnection (string,string) */public Connection getconnection (string user, String Password) throws SQLException {//First find the Idle object from the connection pool Connection conn = getfreeconnection (0); if (conn = = null) {//Determines whether the maximum number of connections is exceeded, If the maximum number of connections is exceeded//it waits for a certain time to see if there is an idle connection, otherwise throwing an exception tells the user that there is no available connection if (Getconnectioncount () >= connparam.getmaxconnection ()) conn = Getfreeconnection (Connparam.getwaittime ()); else{//does not exceed the number of connections, re-obtain a database connection Connparam.setuser (user); Connparam.setpassword (password); Connection conn2 = drivermanager.getconnection (Connparam.geturl (), user, password);//Agent will return the connection object _connection _conn = New _connection (conn2,true); synchronized (Conns) {conns.add (_conn);} conn = _conn.getconnection ();}} Return conn;} /** * Take an idle connection from the connection pool * @param ntimeout If the parameter has a value of 0, it simply returns a null if there is no connection * Otherwise wait for ntimeout milliseconds to see if there is an idle connection, if no exception is thrown * @return Connection * @throws SQLException */protected synchronized Connection getfreeconnection (long ntimeout) throws sqlexception{ Connection conn = Null;iterator iter = Conns.iterator (); while (Iter.hasnext () {_connection _conn = (_connection) iter.next (); if (!_conn.isinuse ()) {conn = _conn.getconnection (); _conn.setinuse ( true); break;}} if (conn = = null && ntimeout > 0) {//wait ntimeout milliseconds to see if there is an idle connection try{thread.sleep (ntimeout);} catch (Exception e) {}conn = getfreeconnection (0), if (conn = = null) throw new SQLException ("No database connection available");} Return conn;}

The logic of implementing the Getconnection method in the Datasourceimpl class is consistent with the logical connection pool of the normal database, first to determine whether there are idle connections, and if not, to determine whether the number of connections has exceeded the maximum number of connections and so on some logic. But there is a difference is that the database connection through DriverManager is not returned in time, but through a class called _connection, and then call _connection.getconnection return. If we do not take over an interface object to be returned by a proxy in Java, then we have no way to intercept the Connection.close method.

Finally to the core, let's take a look at how _connection is implemented, and then describe how a process is going when the client calls the Connection.close method, and why the connection is not actually closed.

/** * Self-sealing of data connection, shielded Close method * @author Liudong */class _connection implements invocationhandler{private final static String C Lose_method_name = "Close";p rivate Connection conn = busy state of the null;//database private Boolean inUse = false;// The time the user last accessed the connection method private Long LastAccessTime = System.currenttimemillis (); _connection (Connection conn, Boolean InUse) { This.conn = Conn;this.inuse = InUse;} /** * Returns the Conn. * @return Connection */public Connection getconnection () {//returns the takeover class of the database connection conn in order to intercept the Close method Connection conn2 = (Connection) Proxy.newproxyinstance (Conn.getclass (). getClassLoader (), Conn.getclass (). Getinterfaces (), this); return conn2;} /** * This method really closes the connection to the database * @throws SQLException */void Close () throws sqlexception{//because the class attribute conn is not taken over by the connection, So once the Close method is called, the Connection Conn.close () is closed directly;} /** * Returns the InUse. * @return Boolean */public boolean isinuse () {return inUse;} /** * @see Java.lang.reflect.invocationhandler#invoke (java.lang.Object, Java.lang.reflect.Method, Java.lang.Object) */public object Invoke (object proxy, MEthod m, object[] args) throws Throwable {Object obj = null;//Determines if the method of close is called, and if the Close method is called, the connection is set to a useless state if (close_method_ Name.equals (M.getname ())) Setinuse (false) Elseobj = M.invoke (conn, args);//Set the last access time to clear the time-out connection LastAccessTime = System.currenttimemillis (); return obj;} /** * Returns the LastAccessTime. * @return Long */public long GetLastAccessTime () {return lastaccesstime;} /** * Sets the inUse. * @param inUse the inUse to set */public void Setinuse (Boolean inUse) {this.inuse = InUse;}}

Once the user invokes the Close method of the resulting connection, the Java Virtual Opportunity first calls the The Connection.invoke method, in which the method first determines whether it is the Close method, and if not, transfers the code to a real connection object that is not taken over Conn. Otherwise, simply set the status of the connection to available. You may be aware of the entire takeover process, but there is also a question: Is it not possible for these established connections to actually close? The answer is yes. Let's take a look at the Connectionfactory.unbind method, which first finds the connection pool object that the name corresponds to, then closes all connections in that connection pool and removes the connection pool. A close method is defined in the Datasourceimpl class to close all connections, and the detailed code is as follows:

/** * 关闭该连接池中的所有数据库连接 * @return int 返回被关闭连接的个数 * @throws SQLException */public int close() throws SQLException{int cc = 0;SQLException excp = null;Iterator iter = conns.iterator();while(iter.hasNext()){try{((_Connection)iter.next()).close();cc ++;}catch(Exception e){if(e instanceof SQLException)excp = (SQLException)e;}}if(excp != null)throw excp;return cc;}

The method one by one calls the Close method of each object in the connection pool, the Close method corresponds to the implementation of close in _connection, and when the database connection is closed in the _connection definition, the Close method of the object that is not taken over is called directly. So the Close method really frees up the database resources.

The above text just describes the interface method of the takeover, a practical connection pool module also need to monitor the idle connection and release the connection in time, detailed code please refer to the attachment.

Implementing a database connection pool using dynamic proxies in Java