JDBC Learning notes-a simple connection pool

1. Save the connection using LinkedList

Even the simplest JDBC operation involves the following steps: Establishing a connection, creating a SQL statement, executing a statement, processing execution results, freeing a resource, and establishing a connection step that is computationally expensive, if we create a new JDBC connection each time we do the JDBC operation, and then release the connection immediately after we have finished using it , doing so consumes a lot of performance. A more reasonable approach would be to create a JDBC connection, use a JDBC connection, not immediately release the JDBC connection after use, but instead cache the connection, and the next time we operate JDBC, we can directly use the JDBC connection that is already connected in the cache.

The following code saves the JDBC connection created by a linkedlist, and at the time of creation we will first create 10 connections and save them in the list, and when the client code needs to use connection, we take the first connection back from the list , when the customer code is finished using connection, call the free () method and put the connection back in the list:

public class DataSource1 {linkedlist<connection> ConnectionPool = new linkedlist<connection> ();p ublic DataSource1 () {try {for (int i = 0; i <; i++) {This.connectionPool.addLast (this.createconnection ());}} catch (Sqlexc Eption e) {throw new Exceptionininitializererror (e);}} Public Connection getconnection () {return Connectionpool.removefirst ();} public void Free (Connection conn) {connectionpool.addlast (conn);} Private Connection CreateConnection () throws SQLException {return drivermanager.getconnection ("jdbc:mysql:// Localhost:3306/jdbc "," Root "," ");}}

2. Control the number of connections

In the Getconnection () method of the example above, we did not detect whether the ConnectionPool contained connection and returned directly to the first connection in ConnectionPool, which was unsafe, Before returning connection from ConnectionPool, we should first detect if there is connection in ConnectionPool, and if so, return the first connectionpool in connection, if not, We can create a new one and return it. However, there is a problem, if the number of threads requested, we create a lot of unlimited connection can cause the database to block, because the database can support a limit of connections, so we should control the new connection cap, if not reached the upper limit, We can create and return, and if we reach the connection limit, we throw an exception. At the same time we should lock on getconnection () to ensure multithreading security, modified code as follows, we use Initcount to represent the list initialization size, Maxcount represents the list maximum size, Currentcount indicates the number of connection currently alive:

public class DataSource2 {private static int initcount = 10;private static int Maxco UNT = 30;private int currentcount = 0; linkedlist<connection> ConnectionPool = new linkedlist<connection> ();p ublic DataSource2 () {try {for (int i = 0; i < Initcount; i++) {this.connectionPool.addLast (this.createconnection ()); this.currentcount++;}} catch (SQLException e) {throw new Exceptionininitializererror (e);}} Public Connection getconnection () throws SQLException {synchronized (ConnectionPool) {if (connectionpool.size () > 0) return Connectionpool.removefirst (); if (This.currentcount < MaxCount) {This.currentcount++;return CreateConnection ();} throw new SQLException ("no Link");}} public void Free (Connection conn) {connectionpool.addlast (conn);} Private Connection CreateConnection () throws SQLException {return drivermanager.getconnection ("jdbc:mysql:// Localhost:3306/jdbc "," Root "," ");}} 

3. Dynamic Agent blocking Close method

the problem with the above code is that to close connection must call our free () method, not directly call the close () method on connection, which is close () for some habits to use the connection Users are not very friendly, it is easy to cause them to forget to use the connection back to ConnectionPool, in order to allow users to maintain the original habit, we want to be able to rewrite the connection close () method, so that it is not directly close the connection, Instead of returning the connection back to the ConnectionPool, the other methods remain the same, and for this need we can use dynamic proxy implementations:

public class DataSource3 {private static int initcount = 1;private static int maxCount = 1;int Currentcount = 0; linkedlist<connection> Connectionspool = new linkedlist<connection> ();p ublic DataSource3 () {try {for (int i = 0; i < Initcount; i++) {this.connectionsPool.addLast (this.createconnection ()); this.currentcount++;}} catch (SQLException e) {throw new Exceptionininitializererror (e);}} Public Connection getconnection () throws SQLException {synchronized (Connectionspool) {if (This.connectionsPool.size () > 0) return This.connectionsPool.removeFirst (); if (This.currentcount < MaxCount) {This.currentcount++;return This.createconnection ();} throw new SQLException ("no Link");}} public void Free (Connection conn) {this.connectionsPool.addLast (conn);} Private Connection CreateConnection () throws SQLException {Connection realconn = drivermanager.getconnection ("JDBC: Mysql://localhost:3306/jdbc "," Root "," "); Myconnectionhandler proxy = new Myconnectionhandler (this); return Proxy.bind(Realconn);}} Class Myconnectionhandler implements Invocationhandler {private Connection realconnection;private Connection Warpedconnection;private DataSource3 DataSource; Myconnectionhandler (DataSource3 dataSource) {this.datasource = DataSource;} Connection bind (Connection realconn) {this.realconnection = Realconn;this.warpedconnection = (Connection) Proxy.newproxyinstance (This.getclass (). getClassLoader (), new class[] {connection.class},this); return Warpedconnection;} @Overridepublic object Invoke (Object proxy, Method method, object[] args) throws Throwable {if ("Close". Equals ( Method.getname ())) {this.dataSource.connectionsPool.addLast (this.warpedconnection);} Return Method.invoke (this.realconnection, args);}}

The key point of the above code is the Myconnectionhandler class, which returns a Wrapedconnection,wrapedconnection method in the Bind () method of the class as follows:

New class[] {connection.class},this);

The Proxy.newproxyinstance () method is a key method of a Java dynamic agent that dynamically creates a class in memory at run time, the first parameter of which is to specify a ClassLoader, and the second parameter specifies the interface that dynamically creates the class implementation. The third parameter specifies which class the method called on the class should be transferred to, and this is specified here, so all methods are transferred to Myconnectionhandler, which is exactly the Myconnectionhandler invoke method:

public object invoke (object proxy, Method method, object[] args) throws Throwable {if ("Close". Equals (Method.getname ())) { This.dataSource.connectionsPool.addLast (this.warpedconnection);} Return Method.invoke (this.realconnection, args);}

In the Invoke () method, we can see that the second argument passes the method of the call, we judge by the passed method object, whether the user calls the Close method, and if it is the Close method, then we re-add the connection to the list, If not, then we will execute the corresponding method on the real connection.

It is also important to note that the connection object that is now called createconnection () is not the original connection object, but instead invokes a proxy class that is dynamically generated by the bind () method on the Myconnectionhandler class:

Private Connection CreateConnection () throws SQLException {Connection realconn = drivermanager.getconnection ("JDBC: Mysql://localhost:3306/jdbc "," Root "," "); Myconnectionhandler proxy = new Myconnectionhandler (this); return Proxy.bind (Realconn);}

JDBC Learning notes-a simple connection pool