一个简易的数据库连接池

àì夳堔傛蜴生んèń 2022-04-12 07:10 182阅读 0赞

### 目录 ###

*  背景
 *   *  实现原理
     *  具体实现
     *  创建一个表格

# 背景 #

数据库连接池是比较宝贵的资源，当有大量的连接时，需要使用数据库连接池来保证数据库连接资源的复用。

## 实现原理 ##

通过一个LinkedList集合来缓存连接的实例，通过等待通知机制来实现获取和释放连接。等待的时间可设置。

## 具体实现 ##

数据库连接池的实现

package com.genersoft;
    
    import java.sql.Connection;
    import java.util.LinkedList;
    
    public class ConnectionPool {
    	
    	private LinkedList<Connection>   pool = new LinkedList<>();
    	//初始化数据库连接池
    	public   ConnectionPool(int initialSize)
    	{
    		
    		for (int i = 0;i < initialSize;i++)
    		{
    			pool.addLast(ConnectionDriver.createConnection());
    			//System.out.println(""+pool);
    			
    		}
    	}
    	//释放数据库连接池
    	public  void releaseConnection(Connection  connection)
    	{
    		if (connection != null) {
    			
    			synchronized(pool) {
    				pool.addLast(connection);
    				pool.notifyAll();
    				
    			}		
    		}	
    		
    	}
    	//获取数据库连接池
    	public  Connection   fetchConnection(long  mills) throws InterruptedException
    	{
    		synchronized(pool)
    		{
    			if (mills <= 0) {
    				
    				if (pool.isEmpty())
    				{
    					
    						pool.wait();
    					
    				}
    				return pool.removeFirst();
    				
    			}
    			else {		
    				long future = System.currentTimeMillis()+mills;
    				long remaining = mills;
    				while  (pool.isEmpty() && remaining > 0)
    				{
    					
    						pool.wait(remaining);
    						
    						remaining = future - System.currentTimeMillis();
    					
    				}
    			
    				Connection result = null;
    				if (!pool.isEmpty())
    				{
    					
    					result = pool.removeFirst();
    				}
    				return result;
    			}		
    		}
    	
    		
    	}
    
    }

Mysql连接的类

package com.genersoft;
    
    import java.lang.reflect.InvocationHandler;
    import java.lang.reflect.Method;
    import java.lang.reflect.Proxy;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.concurrent.TimeUnit;
    
    public class ConnectionDriver {
    
    
    	public static Connection createConnection() {
    		
    		String url="jdbc:mysql://localhost:3306/test?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false";
    
    		String user="root";
    
    		String password="root";
    		Connection conn = null;
    
    		try {
    			 conn =  DriverManager.getConnection(url,user,password);
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		return conn;
    		
    		//return  (Connection) Proxy.newProxyInstance(ConnectionDriver.class.getClassLoader(), new Class<?>[] {Connection.class},new Handler() );
    	}
    	private static class  Handler implements InvocationHandler{
    
    		@Override
    		public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    			if (method.getName().equals("commit"))
    			{
    				TimeUnit.MILLISECONDS.sleep(100);
    			}
    			return null;
    		}
    		
    		
    	}
    }

测试类：

package com.genersoft;
    
    import java.sql.Connection;
    import java.util.concurrent.CountDownLatch;
    import java.util.concurrent.atomic.AtomicInteger;
    
    /**
     * Hello world!
     *
     */
    public class ConectionPoolTest 
    {
    	
    	private static CountDownLatch start = new CountDownLatch(1);
    	private static  CountDownLatch end;
    	private static ConnectionPool pool = new ConnectionPool(10);
    	
    	
        public static void main( String[] args ) throws InterruptedException
        {
           
           int threadNum = 30;
           end = new CountDownLatch(threadNum);
           int  count = 20;
           AtomicInteger  getNum = new AtomicInteger() ;
           AtomicInteger notGetNum = new AtomicInteger() ;
           
           for (int i = 0;i < threadNum;i++)
           {
        	
        	   Thread  runnerThread = new Thread(new RunnerThread(count,getNum,notGetNum),"RunnerThread");
        	   runnerThread.start();
           }    
           start.countDown();
           end.await();
           System.out.println("total invoked"+count*threadNum);
           System.out.println("getNum"+getNum);
           System.out.println("notGetNum"+notGetNum);
       
        }
        
        static class  RunnerThread implements    Runnable{
    	   int  count;
           AtomicInteger  getNum ;
           AtomicInteger notGetNum  ;
        	
    	  public  RunnerThread(int  count2,AtomicInteger getNum2,AtomicInteger notGetNum2)
    	  {
    		  this.count = count2;
    		  this.getNum = getNum2;
    		  this.notGetNum = notGetNum2;	
    	  }
    
    	  @Override
    	  public void run() {
    		  try {
    			start.await();
    		} catch (InterruptedException e) {
    			e.printStackTrace();
    		}
    		  
    		while (count > 0)
    		{
    			
    			Connection conn;
    		
    			try {
    				conn = pool.fetchConnection(10);
    				
    				if (conn != null)
    				{
    					try {
    						conn.createStatement();
    						conn.commit();
    					}finally {		
    						pool.releaseConnection(conn);
    						getNum.incrementAndGet();
    					}		
    				}else {
    					
    					notGetNum.incrementAndGet();
    				}
    				
    			}catch(Exception e)
    			{
    				
    			}finally {
    				
    				count--;
    			}	
    		}
    		end.countDown();
    	  }
    	
        	
        }
    }

## 创建一个表格 ##

一个简单的表格是这么创建的：

<table> 
 <thead> 
  <tr> 
   <th>线程数量</th> 
   <th>总获取次数</th> 
   <th>获取到次数</th> 
   <th>为获取次数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>10</td> 
   <td>200</td> 
   <td>200</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>30</td> 
   <td>600</td> 
   <td>579</td> 
   <td>21</td> 
  </tr> 
 </tbody> 
</table>