AI专业者开发社区 1 号成员
  •  0 回帖  •  6 浏览  •  4 个月前
 

简单的数据库连接池实例(java语言)

1. 概述

频繁的创建和销毁数据库连接消耗非常多的系统资源，创建一个池子， 管理一定数量的连接，用的时候去池中取，用完了放回池中，这时比较通用的做法。

 

2. 关键字

LinkedList  synchronized  InvocationHandler  CountDownLatch

 

3. 代码

3.1 ConnectionPool.java

package com.rocky.pool;

import java.sql.Connection;
import java.util.LinkedList;


public class ConnectionPool {


</span><span style="color: rgba(0, 0, 255, 1)">private</span> LinkedList&lt;Connection&gt; pool = <span style="color: rgba(0, 0, 255, 1)">new</span> LinkedList&lt;Connection&gt;<span style="color: rgba(0, 0, 0, 1)">();

</span><span style="color: rgba(0, 0, 255, 1)">public</span> ConnectionPool(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> initialSize){
    </span><span style="color: rgba(0, 0, 255, 1)">if</span>(initialSize &gt; 0<span style="color: rgba(0, 0, 0, 1)">){
        </span><span style="color: rgba(0, 0, 255, 1)">for</span>(<span style="color: rgba(0, 0, 255, 1)">int</span> i=0; i&lt;initialSize; i++<span style="color: rgba(0, 0, 0, 1)">){
            pool.addLast(ConnectionDriver.createConection());
        }
    }
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> releaseConnection(Connection connection){
    </span><span style="color: rgba(0, 0, 255, 1)">if</span>(connection != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">){
        </span><span style="color: rgba(0, 0, 255, 1)">synchronized</span><span style="color: rgba(0, 0, 0, 1)"> (pool) {
            </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">连接释放后 要进行通知 这样其他消费者能够感知池中已经归还了一个连接</span>



                pool.addLast(connection);
//                pool.notifyAll();//all
pool.notify();//all

}
}
}


</span><span style="color: rgba(0, 0, 255, 1)">public</span>  Connection fetchConnection(<span style="color: rgba(0, 0, 255, 1)">long</span> mills) <span style="color: rgba(0, 0, 255, 1)">throws</span><span style="color: rgba(0, 0, 0, 1)"> InterruptedException{
    </span><span style="color: rgba(0, 0, 255, 1)">synchronized</span><span style="color: rgba(0, 0, 0, 1)"> (pool) {
        </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">超时</span>
        <span style="color: rgba(0, 0, 255, 1)">if</span>(mills &lt;= 0<span style="color: rgba(0, 0, 0, 1)">){
            </span><span style="color: rgba(0, 0, 255, 1)">while</span><span style="color: rgba(0, 0, 0, 1)">(pool.isEmpty()){
                pool.wait();
            }
            </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> pool.removeFirst();
        }</span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">{
            </span><span style="color: rgba(0, 0, 255, 1)">long</span> future = System.currentTimeMillis() +<span style="color: rgba(0, 0, 0, 1)"> mills;
            </span><span style="color: rgba(0, 0, 255, 1)">long</span> remaining =<span style="color: rgba(0, 0, 0, 1)"> mills;
            </span><span style="color: rgba(0, 0, 255, 1)">while</span>(pool.isEmpty() &amp;&amp; remaining &gt;0<span style="color: rgba(0, 0, 0, 1)">){
                pool.wait(remaining);
                remaining </span>= future -<span style="color: rgba(0, 0, 0, 1)"> System.currentTimeMillis();
            }
            Connection result </span>= <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
            </span><span style="color: rgba(0, 0, 255, 1)">if</span>(!<span style="color: rgba(0, 0, 0, 1)">pool.isEmpty()){
                result </span>=<span style="color: rgba(0, 0, 0, 1)"> pool.removeFirst();
            }
            </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> result;
        }
    }
    
}



}

3.2 ConnectionDriver.java

package com.rocky.pool;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;


public class ConnectionDriver {


</span><span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">class</span> ConnectionHandler <span style="color: rgba(0, 0, 255, 1)">implements</span><span style="color: rgba(0, 0, 0, 1)"> InvocationHandler{
    @Override
    </span><span style="color: rgba(0, 0, 255, 1)">public</span> Object invoke(Object proxy, Method method, Object[] args) <span style="color: rgba(0, 0, 255, 1)">throws</span><span style="color: rgba(0, 0, 0, 1)"> Throwable {
        </span><span style="color: rgba(0, 0, 255, 1)">if</span>(method.getName().equals("commit"<span style="color: rgba(0, 0, 0, 1)">)){
            Thread.sleep(</span>1000<span style="color: rgba(0, 0, 0, 1)">);
        }
        </span><span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
    }
}

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">创建一个connection的代理</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> Connection createConection(){
    </span><span style="color: rgba(0, 0, 255, 1)">return</span> (Connection) Proxy.newProxyInstance(ConnectionDriver.<span style="color: rgba(0, 0, 255, 1)">class</span>.getClassLoader(), <span style="color: rgba(0, 0, 255, 1)">new</span> Class&lt;?&gt;[]{Connection.<span style="color: rgba(0, 0, 255, 1)">class</span>},<span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> ConnectionHandler());
} 



}

3.3 ConnectionPoolTest.java

package com.rocky.pool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicInteger;


public class ConnectionPoolTest {


</span><span style="color: rgba(0, 0, 255, 1)">static</span> ConnectionPool pool = <span style="color: rgba(0, 0, 255, 1)">new</span> ConnectionPool(10<span style="color: rgba(0, 0, 0, 1)">);

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">保证所有runner能够同时运行</span>
<span style="color: rgba(0, 0, 255, 1)">static</span> CountDownLatch start = <span style="color: rgba(0, 0, 255, 1)">new</span> CountDownLatch(1<span style="color: rgba(0, 0, 0, 1)">);

</span><span style="color: rgba(0, 0, 255, 1)">static</span><span style="color: rgba(0, 0, 0, 1)"> CountDownLatch end ;

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span> main(String[] args) <span style="color: rgba(0, 0, 255, 1)">throws</span><span style="color: rgba(0, 0, 0, 1)"> Exception {
    </span><span style="color: rgba(0, 0, 255, 1)">int</span> threadCount = 20<span style="color: rgba(0, 0, 0, 1)">;
    
    end </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> CountDownLatch(threadCount);
    
    </span><span style="color: rgba(0, 0, 255, 1)">int</span> count = 20<span style="color: rgba(0, 0, 0, 1)">;
    AtomicInteger got </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> AtomicInteger();
    AtomicInteger notGot </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> AtomicInteger();
    </span><span style="color: rgba(0, 0, 255, 1)">for</span>(<span style="color: rgba(0, 0, 255, 1)">int</span> i=0; i&lt;threadCount; i++<span style="color: rgba(0, 0, 0, 1)">){
        Thread thread </span>= <span style="color: rgba(0, 0, 255, 1)">new</span> Thread(<span style="color: rgba(0, 0, 255, 1)">new</span> ConnectionRunner(count, got, notGot), "ConectionRunnerThread"+<span style="color: rgba(0, 0, 0, 1)">i);
        thread.start();
    }
    start.countDown();
    end.await();
    System.out.println(</span>"total invoke: "+ (threadCount) *<span style="color: rgba(0, 0, 0, 1)"> count);
    System.out.println(</span>"got connection: "+<span style="color: rgba(0, 0, 0, 1)">got);
    System.out.println(</span>"not got connection "+<span style="color: rgba(0, 0, 0, 1)"> notGot);
}

</span><span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">class</span> ConnectionRunner <span style="color: rgba(0, 0, 255, 1)">implements</span><span style="color: rgba(0, 0, 0, 1)"> Runnable{

    </span><span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> count ;
    AtomicInteger got;
    AtomicInteger notGot;
    </span><span style="color: rgba(0, 0, 255, 1)">public</span> ConnectionRunner(<span style="color: rgba(0, 0, 255, 1)">int</span><span style="color: rgba(0, 0, 0, 1)"> count, AtomicInteger got, AtomicInteger notGot){
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.count =<span style="color: rgba(0, 0, 0, 1)"> count;
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.got =<span style="color: rgba(0, 0, 0, 1)"> got;
        </span><span style="color: rgba(0, 0, 255, 1)">this</span>.notGot =<span style="color: rgba(0, 0, 0, 1)"> notGot;
    }
    
    @Override
    </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> run() {

        </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
            start.await();
        } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (InterruptedException e) {
            e.printStackTrace();
        }
        </span><span style="color: rgba(0, 0, 255, 1)">while</span>(count &gt; 0<span style="color: rgba(0, 0, 0, 1)">){
            </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
                Connection connection </span>= pool.fetchConnection(1000<span style="color: rgba(0, 0, 0, 1)">);
                </span><span style="color: rgba(0, 0, 255, 1)">if</span>(connection != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">){
                    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)">{
                        connection.createStatement();
                        connection.commit();
                    }</span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)">{
                        pool.releaseConnection(connection);
                        got.incrementAndGet();
                    }
                }</span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">{
                    notGot.incrementAndGet();
                }
            } </span><span style="color: rgba(0, 0, 255, 1)">catch</span> (InterruptedException |<span style="color: rgba(0, 0, 0, 1)"> SQLException e) {
                e.printStackTrace();
            }</span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)">{
                count</span>--<span style="color: rgba(0, 0, 0, 1)">;
            }
        }
        end.countDown();
    }
    
}



}

3.4 说明

通过改变 main 方法中的 threadCount 的数量可以观察 随着线程数的增加 获取连接命中的比例在下降，

这时因为连接池中的连接数一定 (10 个) 而客户端线程会等待并超时返回。