java数据库连接池

根据自己的理解写的，数据库连接池。

实现了动态增长，连接重用等。

连接池初始时，会有一定数量的连接，随着连接请求的增多，动态增长连接。

存在的问题：当可用连接占总连接数的百分比达到一个数值时，会有很多连接不会被使用，将来可能也不会使用，造成资源的浪费，我想的是产生一个线程，每隔一段时间动态扫描，计算这个百分比，然后关闭一定数量的连接。更好的是根据连接请求的统计，动态增长和关闭连接，比如用户对网站的请求，凌晨的访问肯定会比白天要少，凌晨就可以关闭一些连接，白天就适当的增加连接数。

主要代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ConnectionPool {
	private String driverName;
	private String dbURL;
	private String userName;
	private String userPwd;
	private String testTableName;
	private int minConnectionSize = 10;// 连接池最小个数
	private int maxConnectionSize = 100;
	private int addConnectionSize = 5;// 每次递增的数量
	private List<MyConnection> connections;// 盛装链接的容器
	private static int count = 0;// 可用连接数

	public ConnectionPool(String driverName, String dbURL, String userName,
			String userPwd, String testTableName) {
		this.driverName = driverName;
		this.dbURL = dbURL;
		this.userName = userName;
		this.userPwd = userPwd;
		this.testTableName = testTableName;
	}

	/**
	 * 初始化,加载驱动等
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void createConnectionPool() throws ClassNotFoundException,
			SQLException {
		Class.forName(driverName);
		connections = new ArrayList<MyConnection>();
		addConnection(minConnectionSize);
	}

	/**
	 * 从连接池中取出一个可用连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public synchronized Connection getConnection() throws SQLException {
		for (MyConnection conn : connections) {
			if (!conn.isBusy()) {
				conn.setBusy(true);
				if (!testConnection(conn.getConnection())) {// 连接不可用时生成新的连接
					conn.setConnection(newConnection());
				}
				this.count--;
				return conn.getConnection();
			}
		}
		if (connections.size() < maxConnectionSize && maxConnectionSize > 0) {// 没有可用链接时候，生成一些连接
			addConnection(addConnectionSize);
		} else {// 当连接数达到最大数量时，负荷状态
			return null;
		}
		return getConnection();
	}

	/**
	 * 测试某个连接是否可用
	 * 
	 * @param connection
	 * @return
	 */
	private boolean testConnection(Connection connection) {
		try {
			Statement s = connection.createStatement();
			ResultSet rs = s.executeQuery("select count(*) from "
					+ testTableName);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 增加一定数量的连接
	 * 
	 * @param num
	 * @throws SQLException
	 */
	private void addConnection(int num) throws SQLException {
		if (num + connections.size() > maxConnectionSize
				&& maxConnectionSize > 0) {
			num = maxConnectionSize - connections.size();
		}
		for (int i = 0; i < num; i++) {
			connections.add(new MyConnection(newConnection()));
		}
	}

	/**
	 * 生成新的连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection newConnection() throws SQLException {
		Connection connection = DriverManager.getConnection(dbURL, userName,
				userPwd);
		this.count++;
		return connection;
	}

	/**
	 * 释放连接
	 * 
	 * @param connection
	 */
	public synchronized void freeConnection(Connection connection) {
		for (MyConnection mc : connections) {
			if (mc.getConnection() == connection) {
				mc.setBusy(false);
				this.count++;
				break;
			}
		}
	}

	/**
	 * 关闭连接
	 * 
	 * @param connection
	 * @throws SQLException
	 */
	public synchronized void closeConnection(Connection connection)
			throws SQLException {
		for (MyConnection mc : connections) {
			if (mc.getConnection() == connection) {
				connection.close();
				this.count--;
				connections.remove(mc);
				break;
			}
		}
	}

	/**
	 * 清空连接池
	 * 
	 * @throws SQLException
	 */
	public void clearConnections() throws SQLException {
		for (MyConnection mc : connections) {
			mc.getConnection().close();
		}
		connections.clear();
		this.count = 0;
	}

	public String toString() {
		return "最大连接数" + maxConnectionSize + "，最小连接数:" + minConnectionSize
				+ "，当前连接数" + (connections.size() - count) + ",可用连接数:" + count
				+ "，总连接数:" + connections.size();
	}

	class MyConnection {
		private Connection connection;// 链接
		private boolean busy = false;// 是否在使用

		public MyConnection(Connection connection) {
			this.connection = connection;
		}

		public Connection getConnection() {
			return connection;
		}

		public boolean isBusy() {
			return busy;
		}

		public void setBusy(boolean busy) {
			this.busy = busy;
		}

		public void setConnection(Connection connection) {
			this.connection = connection;
		}

	}

	public String getTestTableName() {
		return testTableName;
	}

	public void setTestTableName(String testTableName) {
		this.testTableName = testTableName;
	}

	public int getMinConnectionSize() {
		return minConnectionSize;
	}

	public void setMinConnectionSize(int minConnectionSize) {
		this.minConnectionSize = minConnectionSize;
	}

	public int getMaxConnectionSize() {
		return maxConnectionSize;
	}

	public void setMaxConnectionSize(int maxConnectionSize) {
		this.maxConnectionSize = maxConnectionSize;
	}

	public int getAddConnectionSize() {
		return addConnectionSize;
	}

	public void setAddConnectionSize(int addConnectionSize) {
		this.addConnectionSize = addConnectionSize;
	}

}

测试代码：

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class Test {
	private static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static String dbURL = "jdbc:sqlserver://192.168.92.3:1433; DatabaseName=message_list_system";
	private static String userName = "sa";
	private static String userPwd = "123";
	private static String tableName = "user_info";

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException, InterruptedException {
		ConnectionPool connectionPool = new ConnectionPool(driverName, dbURL,
				userName, userPwd, tableName);
		connectionPool.createConnectionPool();
		List<Connection> lists = new ArrayList<Connection>();
		for (int i = 0; i < 120; i++) {// 模拟多次连接请求
			System.out.println(connectionPool);
			Connection connection = connectionPool.getConnection();
			lists.add(connection);
			Random random = new Random();
			int r = random.nextInt(2);// 产生0-2的随机数模拟释放连接
			if (r == 0) {// 模拟释放连接 ,三分之一的概率释放连接
				for (int j = 0; (j < random.nextInt(2) + 1) && lists.size() > 0; j++) {// 每次释放1-2个连接
					int temp = random.nextInt(lists.size());
					connectionPool.freeConnection(lists.get(temp));// 在获得的所有链接中随机选择一个进行释放连接
					lists.remove(temp);
					System.out.println("释放了第" + temp + "个连接");
				}
			}
			Thread.sleep(200);
		}
	}
}

效果图：