The principle of database connection pool and the realization of the custom connection pool __ Database

Implementation Principle

When the database connection pool is initialized, a certain number of database connections are created into the connection pool, which is restricted by the minimum number of database connections. Regardless of whether or not these database connections are used, the connection pool will always ensure that there are at least as many connections. The maximum number of database connections for the connection pool limits the maximum number of connections that the connection pool can hold, and when the application requests more connections to the connection pool than the maximum number of connections, the requests are added to the wait queue.
The basic idea of a connection pool is to store the database connection as an object in memory when the system initializes, and when the user needs to access the database, instead of creating a new connection, an established idle connection object is removed from the connection pool. Once used, the user does not close the connection, but instead puts the connection back into the connection pool for use by the next request for access. And the connection is established, disconnected by the connection pool itself to manage. You can also control the number of initial connections in the connection pool, the upper and lower bounds of the connection, the maximum number of times per connection, the maximum idle time, and so on, by setting the parameters of the connection pool. You can also monitor the number and usage of database connections through its own management mechanism. Attention Matters

1. The minimum number of connections to the database connection pool is the database connection that the connection pool maintains, so if the application does not use the database connection very much, a large amount of database connection resources will be wasted.
2. The maximum number of connections to the database connection pool is the maximum number of connections that can be requested by the connection pool, and if the database connection request exceeds this number, subsequent database connection requests will be added to the wait queue, which will affect the subsequent database operations.
3, the maximum number of connections to see the value of the system access. To go through constant testing to get a balanced value
4, a period of time to detect the connection pool, found less than the minimum number of connections to supplement the corresponding number of new connections
5, the minimum number of connections and the maximum number of connections, the minimum number of connections and the maximum number of connections is too large, then the first connection request will be profitable, then more than the minimum number of connection requests is equivalent to establishing a new database connection. However, these database connections that are larger than the minimum number of connections are not released immediately after use, and will be released in the connection pool pending reuse or idle timeout. database Connection Pool configuration Properties

At present, there are many types of database connection pool, different kinds of basic configuration properties, such as C3P0, Proxool, Ddconnectionbroker, Dbpool, Xapool, Druid, DBCP, here we take dbcp as an example to say the main configuration items:

#最大连接数量: The maximum number of active connections that the connection pool can allocate at the same time, if set to a maxactive=15, the default value of 8
is
#最小空闲连接: The minimum number of connections allowed to remain idle in the connection pool, A new connection will be created if set to 0, and the default value of 0
minidle=5
#最大空闲连接: The maximum number of connections allowed to remain idle in the connection pool, excess idle connections will be freed, and if set to negative for unlimited, the default value is 8
maxidle=10
#初始化连接数: Number of initialization connections created at Connection pool startup, default value 0
initialsize=5
#连接被泄露时是否打印
logabandoned=true
# Whether to automatically recycle timeout connection
removeabandoned=true 
#超时时间 (in seconds)
removeabandonedtimeout=180
# Maximum wait time: When no connection is available, The maximum time (in milliseconds) that the connection pool waits for the connection to be returned, and throws an exception if set to-1 for infinite wait, and the default value is infinite
maxwait=3000
#在空闲连接回收器线程运行期间休眠的时间值 (in milliseconds).
timebetweenevictionrunsmillis=10000
#在每次空闲连接回收器线程 (if any) the number of connections checked at run time
numtestsperevictionrun=8
# The connection remains idle in the pool without being disconnected by the idle connection collector thread
minevictableidletimemillis=10000
#用来验证从连接池取出的连接
validationquery=select 1
#指明是否在从池中取出连接前进行检验
testonborrow=true
#testOnReturn  false to  indicate whether to test
before returning to the pool Testonreturn=true
#设置为true后如果要生效, the Validationquery parameter must be set to a non-empty string
testwhileidle

Custom Database connection pooling sample

First look at the definition of the connection pool. It initiates the maximum upper limit of the connection through a constructor, maintains the connection through a two-way queue, and the caller needs to call the Fetchconnection (long) method to specify how many milliseconds to get the connection, and when the connection is completed, the call to Releaseconnection ( Connection) method to put the connection back to the thread pool

public class ConnectionPool {private linkedlist<connection> pool = new linkedlist<connection> (); /** * Initialize Connection pool size * @param initialsize */public connectionpool (int initialsize) {if initialsiz E > 0) {for (int i = 0; i < initialsize i++) {pool.addlast (Connectiondriver.createcon
            Nection ()); }}/** * Free connection, put back to connection pool * @param connection/public void Releaseconnection (connectio N connection) {if (connection!= null) {synchronized (pool) {//connection is released after notification is required so that other consumers can feel
                A connection Pool.addlast (connection) is known to have been returned to the connection pool;
            Pool.notifyall (); }}/** * Cannot get a connection within mills and will return NULL * @param mills * @return * @throws Interruptedexc Eption/Public Connection fetchconnection (Long Mills) throws interruptedexception{-synchronized (pool) {//Unrestricted waitPending if (Mills <= 0) {while (Pool.isempty ()) {pool.wait ();
            return Pool.removefirst ();
                }else{Long future = System.currenttimemillis () + Mills;
                long remaining = Mills; 
                    while (Pool.isempty () && remaining > 0) {//wait timeout pool.wait (remaining);
                Remaining = Future-system.currenttimemillis ();
                } Connection result = null;
                if (!pool.isempty ()) {result = Pool.removefirst ();
            return result; }
        }
    }
}

Since Java.sql.Connection is an interface, the final implementation is implemented by the database-driven provider, considering just an example, we construct a connection through a dynamic proxy, and the proxy implementation of the connection is simply a commit () Hibernate 100 milliseconds when method calls

public class Connectiondriver {
    static class Connectionhandler implements invocationhandler{
        @Override
        public object invoke (object proxy, Method method, object[] args) throws Throwable {
            if (method.equals ("commit")) {
                TimeUnit.MILLISECONDS.sleep (MB);
            return null;
        }
    }

    /**
     * Create a Connection agent that sleeps 100 milliseconds at commit
     * @return * *
     public
    static final Connection CreateConnection () {return
        (Connection) proxy.newproxyinstance (ConnectionDriver.class.getClassLoader ()),
                New class[] {connection.class},new connectionhandler ());
    }

The following is an example to test the operation of the simple database connection pool, simulate the process of the client Connectionrunner fetch, use, and finally release the connection, and when it is used, the connection will increase the number of connections fetched, and conversely, increase the number of connections not acquired

public class Connectionpooltest {static ConnectionPool pool = new ConnectionPool (10);
    Ensure that all Connectionrunner can start at the same time static countdownlatch start = new Countdownlatch (1);
    The main thread will wait for all Connectionrunner to complete before continuing with the static Countdownlatch end;
        public static void Main (string[] args) {//number of threads, you can modify the number of threads to observe int threadcount = 10;
        end = new Countdownlatch (threadcount);
        int count = 20;
        Atomicinteger got = new Atomicinteger ();
        Atomicinteger notgot = new Atomicinteger ();  for (int i = 0; i < threadcount i++) {thread thread = new Thread (new Connetionrunner (count, got, Notgot),
            "Connectionrunnerthread");
        Thread.Start ();
        } start.countdown ();
        try {end.await ();
        catch (Interruptedexception e) {e.printstacktrace ();
        } System.out.println ("Total Invoke:" + (ThreadCount * count)); System.out.println ("Got connection:" +Got);
    System.out.println ("Not got Connection" + Notgot);
        The static class Connetionrunner implements Runnable {int count;
        Atomicinteger got;
        Atomicinteger Notgot;
            public Connetionrunner (int count, Atomicinteger got, Atomicinteger notgot) {this.count = count;
            This.got = got;
        This.notgot = Notgot;
            @Override public void Run () {try {start.await (); The catch (Exception ex) {} while (Count > 0) {try {///from thread The pool gets the connection, and if it is not available within 1000ms, it returns NULL//statistics of the number of got and the quantity not fetched Notgot Connection connecti
                    On = pool.fetchconnection (1);
                            if (connection!= null) {try {connection.createstatement ();
                        Connection.commit ();
      finally {                      Pool.releaseconnection (connection);
                        Got.incrementandget ();
                    } else {notgot.incrementandget ();
                The catch (Exception ex) {} finally {count--;
        } end.countdown (); }

    }

}

The Countdownlatch class is a synchronous counter, constructed with the int parameter, which is the initial value of the counter, each time the countdown () method is invoked, the counter is reduced by 1, the counter is greater than 0 o'clock, await () Method blocks the program from continuing execution Countdownlatch, as it writes, is an inverted-count latch that triggers a specific event when the count is reduced to 0 o'clock. With this feature, you can let the main thread wait for the end of the child thread. This ensures that all the Connetionrunner
are executed and then main is printed.

Run Result:
20 Clients

Total invoke:200
got connection:200
got connection 0

50 clients

Total invoke:1000
got connection:999
got connection 1

100 clients

Total invoke:2000
got connection:1842
got connection 158

In the case of resources (10 connections in the connection pool), the rate at which clients are unable to get connections is rising as the client thread increases. Although the client thread will not be able to obtain a connection in this timeout-fetching mode, it can guarantee that the client thread will not hang on to the operation of the connection fetch, but rather "on time" return, and inform the client that the connection acquisition problem is a system self-protection mechanism. The database connection pool design can also be reused for other resource acquisition scenarios, which should be timed out against expensive resources such as database connections.