实现一个简单的连接池

介绍

数据库连接是一种关键的、有限的、昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。
数据库连接池不仅避免了并发数过多导致连接数溢出的问题，而且能够有效的利用连接资源，对数据库的操作无需每次建立连接，只需从池中获取连接即可。

代码实现(Java)

获取配置信息

import java.io.IOException;
import java.util.Properties;

public class Config {
	private volatile static Config config = null;
	private Properties properties;
	private Config(){}
	static{
		if(config == null){
			synchronized (Config.class) {
				if(config == null){
					config = new Config();
					config.properties = new Properties();
					try {
						config.properties.load(Config.class.getClassLoader().getResourceAsStream("config.properties"));
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	public static Config getInstance(){
		return config;
	}
	public String getConfig(String name){
		return properties.getProperty(name);
	}
}

config.properties配置文件

jdbcDriver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.80.128:3306/test
username=root
password=123456

创建连接类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionDriver {
	public static Connection createConnection(){
		String jdbcDriver = Config.getInstance().getConfig("jdbcDriver");
		String url = Config.getInstance().getConfig("url");
		String username = Config.getInstance().getConfig("username");
		String password = Config.getInstance().getConfig("password");
		Connection connection = null;
		try {
			Class.forName(jdbcDriver);
			connection = DriverManager.getConnection(url, username, password); 
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
}

连接池类

import java.sql.Connection;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.Condition;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;


public class ConnectionPool {
	private LinkedList<Connection> pool = new LinkedList<Connection>();
	private Lock lock = new ReentrantLock();
	private Condition condition = lock.newCondition();
	/**
	 * 初始化连接池
	 * @param initSize	初始连接数
	 */
	public ConnectionPool(int initSize){
		if(initSize < 0){
			throw new IllegalArgumentException("初始连接数不能小于0");
		}
		for (int i = 0; i < initSize; i++) {
			pool.add(ConnectionDriver.createConnection());
		}
	}
	
	/**
	 * 使用完连接，放回到连接池
	 * @param connection
	 */
	public void release(Connection connection){
		if(connection != null){
			try{
				lock.lock();
				pool.add(connection);
				condition.signalAll();
			}finally{
				lock.unlock();
			}
		}
	}
	/**
	 * 规定时间内获取连接
	 * @param mills
	 * @return
	 */
	public Connection fecth(long mills){
		try{
			lock.lock();
			if(mills <= 0){
				while(pool.isEmpty()){
					condition.await();
				}
				return pool.removeFirst();
			}else{
				long future = System.currentTimeMillis()+mills;
				long remain = mills;
				while(pool.isEmpty() && remain > 0){
					condition.await(remain,TimeUnit.MILLISECONDS);
					remain = future-System.currentTimeMillis();
				}
				if(!pool.isEmpty()){
					return pool.removeFirst();
				}
			}
		} catch (InterruptedException e) {
			e.printStackTrace();
		}finally{
			lock.unlock();
		}
		return null;
	}
	
	/**
	 * 关闭连接池
	 */
	public void close(){
		try{
			lock.lock();
			for (Connection connection : pool) {
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
					connection = null;
				}
			}			
		}finally{
			lock.unlock();
		}
	}
}

客户端测试类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test {
	public static void main(String[] args) {
		ConnectionPool pool = new ConnectionPool(10);
		Connection connection = pool.fecth(100);
		String sql = "insert into user values(null,?,?)";
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "asd");
			preparedStatement.setString(2, "asd");
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(preparedStatement != null)preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			pool.release(connection);
		}
	}
}

数据库结果