数据库连接池原理及基本实现

连接池：

诞生背景：一段时间后，我们的服务器的性能会有明显的下降，为什么呢？最后发现，这些跟我们的Connection对象的维护：创建销毁等，有关。

于是乎：

我们想到了“池”，先建好，放在池里，用时拿，不用放回去！

 

免去了创建与销毁的痛苦历程！

 

下面是一个基本连接池实现：

（注：未经多线程测试，基本是不能用的，属于演示）

 

ConnectionPool.java

 

package org.util.conns.pool;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.Vector;

/*
 * 默认是与mysql数据库相连接的，可以通过带参数的构造方法构造。
 */

public class ConnectionPool {
 private String jdbcDriver = "com.mysql.jdbc.Driver";
 private String dbUrl = "jdbc:mysql://localhost:3306/bookstore_db";
 private String username = "root";
 private String password = "1988323";
 private Vector<ConnectionInPool> connections = null;
 private int initNum = 0;
 private int maxNum = 10;
 private static ConnectionPool me = null;
 
 private ConnectionPool(int initNum, int maxNum) {
  super();
  this.connections = new Vector<ConnectionInPool>();
  this.initNum = initNum;
  this.maxNum = maxNum;
  ConnectionInPool conn = null;
  for(int i = 0; i < initNum; i++) {
   conn = newConnection();
   connections.add(conn);
  }
 }

 private ConnectionPool(String jdbcDriver, String dbUrl, String username,
   String password, int initNum,
   int maxNum) {
  super();
  this.jdbcDriver = jdbcDriver;
  this.dbUrl = dbUrl;
  this.username = username;
  this.password = password;
  this.connections = new Vector<ConnectionInPool>();
  this.initNum = initNum;
  this.maxNum = maxNum;
  
  ConnectionInPool conn = null;
  for(int i = 0; i < initNum; i++) {
   conn = newConnection();
   connections.add(conn);
  }
 }
 
 public static synchronized ConnectionPool getConnectionPool(int initNum, int maxNum) {
  if(me == null) {
   me = new ConnectionPool(initNum, maxNum);
  }
  
  return me;
 }
 
 public static synchronized ConnectionPool getConnectionPool(String jdbcDriver, String dbUrl, String username,
   String password, int initNum,
   int maxNum) {
  if(me == null) {
   me = new ConnectionPool(jdbcDriver, dbUrl, username, password, initNum, maxNum);
  }
  
  return me;
 }
 
 public ConnectionInPool getConnectionInPool() {
  ConnectionInPool conn = null;
  if(connections == null) {
   System.out.println("连接池不存在！");
   return null;
  }
  
  Iterator<ConnectionInPool> iterator = connections.iterator();
  
  while(iterator.hasNext()) {
   conn = iterator.next();
   if(conn.isInUse() == false) {
    conn.setInUse(true);
    return conn;
   } else {
    try {
     Thread.sleep(5000);
     if(conn.isInUse() == false) {
      conn.setInUse(true);
      return conn;
     } else {
      continue;
     }
    } catch (InterruptedException e) {
     System.out.println("线程暂停出错！");
     e.printStackTrace();
    }
   }
  }
  
  if(conn == null) {
   if(connections.size() >= maxNum) {
    System.out.println("对不起，暂时系统没有空闲资源！");
    return null;
   } else {
    conn = newConnection();
    conn.setInUse(true);
    connections.add(conn);
    
    return conn;
   }
  }
  
  return conn;
 }
 
 public void returnConnectionToPool(ConnectionInPool conn) {
  Iterator<ConnectionInPool> iterator = connections.iterator();
  ConnectionInPool connIn = null;
  while(iterator.hasNext()) {
   connIn = iterator.next();
   if(connIn == conn) {
    connIn.setInUse(false);
   }
  }
 }
 
 private ConnectionInPool newConnection() {
  ConnectionInPool conn = null;
  try {
   Class.forName(jdbcDriver);
   try {
    conn = new ConnectionInPool(java.sql.DriverManager.getConnection(dbUrl, username, password), false);
   } catch (SQLException e) {
    System.out.println("Can not get Connection!");
    e.printStackTrace();
   }
  } catch (ClassNotFoundException e) {
   System.out.println("Can not find Driver!");
   e.printStackTrace();
  }
  
  return conn;
 }
}

****************************************************

ConnectionInPool.java

package org.util.conns.pool;

import java.sql.*;

public class ConnectionInPool {
 private Connection conn = null;
 private boolean inUse = false;
 
 public ConnectionInPool(Connection conn, boolean inUse) {
  this.conn = conn;
  this.inUse = inUse;
 }

 public Connection getConn() {
  return conn;
 }

 public void setConn(Connection conn) {
  this.conn = conn;
 }

 public boolean isInUse() {
  return inUse;
 }

 public void setInUse(boolean inUse) {
  this.inUse = inUse;
 }
 
 
 
}
******************************************************

ConnectionPoolTest.java

package org.util.conns.pool;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectionPoolTest {
 public static void main(String args[]) {
  Statement stmt = null;
  ConnectionPool pool = ConnectionPool.getConnectionPool(5, 10);
  ConnectionInPool conn = pool.getConnectionInPool();
  try {
   stmt = conn.getConn().createStatement();
  } catch (SQLException e) {
   System.out.println("Can not find Statement!");
   e.printStackTrace();
  }
  try {
   ResultSet rs = stmt.executeQuery("select * from books");
   if(rs != null) {
    rs.close();
    if(stmt != null) {
     stmt.close();
    }
    if(conn != null) {
     pool.returnConnectionToPool(conn);
    }
    System.out.println("You are excellent!");
   }
  } catch (SQLException e) {
   System.out.println("查询出错!");
   e.printStackTrace();
  }
 }
}

OK啦，"You are excellent!"