Java实现数据库连接池简易教程

一、引言

池化技术在Java中应用的很广泛，简而论之，使用对象池存储某个实例数受限制的实例，开发者从对象池中获取实例，使用完之后再换回对象池，从而在一定程度上减少了系统频繁创建对象销毁对象的开销。Java线程池和数据库连接池就是典型的应用，但并非所有的对象都适合拿来池化，对于创建开销比较小的对象拿来池化反而会影响性能，因为维护对象池也需要一定的资源开销，对于创建开销较大，又频繁创建使用的对象，采用池化技术会极大提高性能。

业界有很多成熟的数据库连接池，比如C3P0，DBCP，Proxool以及阿里的Druid。很多以及开源，在GitHub可以找到源码，开发者可以根据自己的需求结合各种连接池的特点和性能进行选择。本文仅是为了了解学习池化技术，实现的一个简单的数据库连接池，如有错误，还望批评指正。

二、设计

主要类和接口

.ConnectionParam - 数据库连接池参数类，负责配置数据库连接以及连接池相关参数。使用Builder实现。

driver url user password - 连接数据库所需

minConnection - 最小连接数

maxConnection - 最大连接数

minIdle - 最小空闲连接数

maxWait - 最长等待时间  

private final String driver;

private final String url;

private final String user;

private final String password;

private final int minConnection;

private final int maxConnection;

private final int minIdle;

private final long maxWait;

.ConnectionPool - 数据库连接池

ConnectionPool构造方法声明为保护，禁止外部创建，交由ConnectionPoolFactory统一管理。

ConnectionPool实现DataSource接口，重新getConnection()方法。

ConnectionPool持有两个容器 - 一个Queue存储空闲的Connection，另一个Vector（考虑到同步）存储正在使用的Connection。

当开发者使用数据库连接时，从Queue中获取，没有则返回空；使用完成close连接时，则放回Vector。

ConnectionPool提供了一个简单的基于minIdle和maxConnection的动态扩容机制。

private static final int INITIAL_SIZE = 5;

private static final String CLOSE_METHOD = "close";

private static Logger logger;

private int size;

private ConnectionParam connectionParam;

private ArrayBlockingQueue<Connection> idleConnectionQueue;

private Vector<Connection> busyConnectionVector;

.ConnectionPoolFactory - 连接池管理类

ConnectionPoolFactory持有一个静态ConcurrentHashMap用来存储连接池对象。

ConnectionPoolFactory允许创建多个不同配置不同数据库的连接池。

开发者首次需要使用特定的名称注册（绑定）连接池，以后每次从指定的连接池获取Connection。

如果连接池不再使用，开发者可以注销（解绑）连接池。

private static Map<String, ConnectionPool> poolMap = new ConcurrentHashMap<>();

public static Connection getConnection(String poolName) throws SQLException {
 nameCheck(poolName);
 ConnectionPool connectionPool = poolMap.get(poolName);
 return connectionPool.getConnection();
}

public static void registerConnectionPool(String name, ConnectionParam connectionParam) {
 registerCheck(name);
 poolMap.put(name, new ConnectionPool(connectionParam));
}

// Let GC
public static void unRegisterConnectionPool(String name) {
 nameCheck(name);
 final ConnectionPool connectionPool = poolMap.get(name);
 poolMap.remove(name);
 new Thread(new Runnable() {
  @Override
  public void run() {
   connectionPool.clear();
  }
 }).start();
}

核心代码

 数据库连接池核心代码在于getConnection()方法，通常，开发者处理完数据库操作后，都会调用close()方法，Connection此时应该被关闭并释放资源。而在数据库连接池中，用户调用close()方法，不应直接关闭Connection，而是要放回池中，重复使用，这里就用到Java * 机制，getConnection返回的并不是“真正”的Connection，而是自定义的代理类(此处使用匿名类)，当用户调用close()方法时，进行拦截，放回池中。有关 * ，可以参看另一篇博客《Java * 简单应用》

@Override
public Connection getConnection() throws SQLException {
 try {
  final Connection connection = idleConnectionQueue.poll(connectionParam.getMaxWait(), TimeUnit.MILLISECONDS);
  if (connection == null) {
   logger.info(emptyMsg());
   ensureCapacity();
   return null;
  }
  busyConnectionVector.add(connection);
  return (Connection) Proxy.newProxyInstance(this.getClass().getClassLoader(),
    new Class[]{Connection.class}, new InvocationHandler() {
     @Override
     public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
      if (!method.getName().equals(CLOSE_METHOD)) {
       return method.invoke(connection, args);
      } else {
       idleConnectionQueue.offer(connection);
       busyConnectionVector.remove(connection);
       return null;
      }
     }
    });
 } catch (InterruptedException e) {
  e.printStackTrace();
 }
 return null;
}

二、使用

首先用户构建数据库连接池参数（ConnectionParam），包括driver、url、user、password必须项，可以自定义minConnection、maxConnection等可选项，如果不设置，则使用系统默认值，这是使用Builder构建含有大量属性的好处，其中包括必须属性和可选属性。然后向ConnectionPoolFactory使用特定的名称注册连接池，最后通过调用ConnectionPoolFactory静态工厂方法获取Connection。   

String driver = "com.mysql.jdbc.Driver";
 String url = "jdbc:mysql://localhost:3306/test";
 String user = "root";
 String password = "root";

ConnectionParam connectionParam = new ConnectionParam.ConnectionParamBuilder(driver, url, user, password).build();
 ConnectionPoolFactory.registerConnectionPool("test", connectionParam);
 Connection connection = ConnectionPoolFactory.getConnection("test");

三、代码

.ParamConfiguration

package database.config;

import java.io.Serializable;

/**
* DataBase Connection Parameters
* Created by Michael Wong on 2016/1/18.
*/
public class ParamConfiguration implements Serializable {

public static final int MIN_CONNECTION = 5;

public static final int MAX_CONNECTION = 50;

public static final int MIN_IDLE = 5;

public static final long MAX_WAIT = 30000;

private ParamConfiguration() {}

}

.Builder

package database;

/**
* Builder
* Created by Michael Wong on 2016/1/18.
*/
public interface Builder<T> {

T build();

}

.ConnectionParam

package database;

import database.config.ParamConfiguration;

/**
* DataBase Connection Parameters
* Created by Michael Wong on 2016/1/18.
*/
public class ConnectionParam {

private final String driver;

private final String url;

private final String user;

private final String password;

private final int minConnection;

private final int maxConnection;

private final int minIdle;

private final long maxWait;

private ConnectionParam(ConnectionParamBuilder builder) {
 this.driver = builder.driver;
 this.url = builder.url;
 this.user = builder.user;
 this.password = builder.password;
 this.minConnection = builder.minConnection;
 this.maxConnection = builder.maxConnection;
 this.minIdle = builder.minIdle;
 this.maxWait = builder.maxWait;
}

public String getDriver() {
 return this.driver;
}

public String getUrl() {
 return this.url;
}

public String getUser() {
 return this.user;
}

public String getPassword() {
 return this.password;
}

public int getMinConnection() {
 return this.minConnection;
}

public int getMaxConnection() {
 return this.maxConnection;
}

public int getMinIdle() {
 return this.minIdle;
}

public long getMaxWait() {
 return this.maxWait;
}

public static class ConnectionParamBuilder implements Builder<ConnectionParam> {

// Required parameters
 private final String driver;

private final String url;

private final String user;

private final String password;

// Optional parameters - initialized to default value
 private int minConnection = ParamConfiguration.MIN_CONNECTION;

private int maxConnection = ParamConfiguration.MAX_CONNECTION;

private int minIdle = ParamConfiguration.MIN_IDLE;

// Getting Connection wait time
 private long maxWait = ParamConfiguration.MAX_WAIT;

public ConnectionParamBuilder(String driver, String url, String user, String password) {
  this.driver = driver;
  this.url = url;
  this.user = user;
  this.password = password;
 }

public ConnectionParamBuilder minConnection(int minConnection) {
  this.minConnection = minConnection;
  return this;
 }

public ConnectionParamBuilder maxConnection(int maxConnection) {
  this.maxConnection = maxConnection;
  return this;
 }

public ConnectionParamBuilder minIdle(int minIdle) {
  this.minIdle = minIdle;
  return this;
 }

public ConnectionParamBuilder maxWait(int maxWait) {
  this.maxWait = maxWait;
  return this;
 }

@Override
 public ConnectionParam build() {
  return new ConnectionParam(this);
 }

}

}

.ConnectionPool

package database.factory;

import database.ConnectionParam;

import javax.sql.DataSource;
import java.io.PrintWriter;
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.sql.SQLFeatureNotSupportedException;
import java.util.Vector;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.logging.Logger;

/**
* Connection Pool
* Created by Michael Wong on 2016/1/18.
*/
public class ConnectionPool implements DataSource {

private static final int INITIAL_SIZE = 5;

private static final String CLOSE_METHOD = "close";

private static Logger logger;

private int size;

private ConnectionParam connectionParam;

private ArrayBlockingQueue<Connection> idleConnectionQueue;

private Vector<Connection> busyConnectionVector;

protected ConnectionPool(ConnectionParam connectionParam) {
 this.connectionParam = connectionParam;
 int maxConnection = connectionParam.getMaxConnection();
 idleConnectionQueue = new ArrayBlockingQueue<>(maxConnection);
 busyConnectionVector = new Vector<>();
 logger = Logger.getLogger(this.getClass().getName());
 initConnection();
}

private void initConnection() {
 int minConnection = connectionParam.getMinConnection();
 int initialSize = INITIAL_SIZE < minConnection ? minConnection : INITIAL_SIZE;
 try {
  Class.forName(connectionParam.getDriver());
  for (int i = 0; i < initialSize + connectionParam.getMinConnection(); i++) {
   idleConnectionQueue.put(newConnection());
   size++;
  }
 } catch (Exception e) {
  throw new ExceptionInInitializerError(e);
 }
}

@Override
public Connection getConnection() throws SQLException {
 try {
  final Connection connection = idleConnectionQueue.poll(connectionParam.getMaxWait(), TimeUnit.MILLISECONDS);
  if (connection == null) {
   logger.info(emptyMsg());
   ensureCapacity();
   return null;
  }
  busyConnectionVector.add(connection);
  return (Connection) Proxy.newProxyInstance(this.getClass().getClassLoader(),
    new Class[]{Connection.class}, new InvocationHandler() {
     @Override
     public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
      if (!method.getName().equals(CLOSE_METHOD)) {
       return method.invoke(connection, args);
      } else {
       idleConnectionQueue.offer(connection);
       busyConnectionVector.remove(connection);
       return null;
      }
     }
    });
 } catch (InterruptedException e) {
  e.printStackTrace();
 }
 return null;
}

private Connection newConnection() throws SQLException {
 String url = connectionParam.getUrl();
 String user = connectionParam.getUser();
 String password = connectionParam.getPassword();
 return DriverManager.getConnection(url, user, password);
}

protected int size() {
 return size;
}

protected int idleConnectionQuantity() {
 return idleConnectionQueue.size();
}

protected int busyConnectionQuantity() {
 return busyConnectionVector.size();
}

private void ensureCapacity() throws SQLException {
 int minIdle = connectionParam.getMinIdle();
 int maxConnection = connectionParam.getMaxConnection();
 int newCapacity = size + minIdle;
 newCapacity = newCapacity > maxConnection ? maxConnection : newCapacity;
 int growCount = 0;
 if (size < newCapacity) {
  try {
   for (int i = 0; i < newCapacity - size; i++) {
    idleConnectionQueue.put(newConnection());
    growCount++;
   }
  } catch (InterruptedException e) {
   e.printStackTrace();
  }
 }
 size = size + growCount;
}

protected void clear() {
 try {
  while (size-- > 0) {
   Connection connection = idleConnectionQueue.take();
   connection.close();
  }
 } catch (InterruptedException | SQLException e) {
  e.printStackTrace();
 }
}

private String emptyMsg() {
 return "Database is busy, please wait...";
}

@Override
public Connection getConnection(String username, String password) throws SQLException {
 return null;
}

@Override
public PrintWriter getLogWriter() throws SQLException {
 return null;
}

@Override
public void setLogWriter(PrintWriter out) throws SQLException {

}

@Override
public void setLoginTimeout(int seconds) throws SQLException {

}

@Override
public int getLoginTimeout() throws SQLException {
 return 0;
}

@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
 return null;
}

@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
 return null;
}

@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
 return false;
}

}

.ConnectionPoolFactory

package database.factory;

import database.ConnectionParam;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
* Connection Pool Factory
* Created by Michael Wong on 2016/1/18.
*/
public class ConnectionPoolFactory {

private ConnectionPoolFactory() {}

private static Map<String, ConnectionPool> poolMap = new ConcurrentHashMap<>();

public static Connection getConnection(String poolName) throws SQLException {
 nameCheck(poolName);
 ConnectionPool connectionPool = poolMap.get(poolName);
 return connectionPool.getConnection();
}

public static void registerConnectionPool(String name, ConnectionParam connectionParam) {
 registerCheck(name);
 poolMap.put(name, new ConnectionPool(connectionParam));
}

// Let GC
public static void unRegisterConnectionPool(String name) {
 nameCheck(name);
 final ConnectionPool connectionPool = poolMap.get(name);
 poolMap.remove(name);
 new Thread(new Runnable() {
  @Override
  public void run() {
   connectionPool.clear();
  }
 }).start();
}

public static int size(String poolName) {
 nameCheck(poolName);
 return poolMap.get(poolName).size();
}

public static int getIdleConnectionQuantity(String poolName) {
 nameCheck(poolName);
 return poolMap.get(poolName).idleConnectionQuantity();
}

public static int getBusyConnectionQuantity(String poolName) {
 nameCheck(poolName);
 return poolMap.get(poolName).busyConnectionQuantity();
}

private static void registerCheck(String name) {
 if (name == null) {
  throw new IllegalArgumentException(nullName());
 }
}

private static void nameCheck(String name) {
 if (name == null) {
  throw new IllegalArgumentException(nullName());
 }
 if (!poolMap.containsKey(name)) {
  throw new IllegalArgumentException(notExists(name));
 }
}

private static String nullName() {
 return "Pool name must not be null";
}

private static String notExists(String name) {
 return "Connection pool named " + name + " does not exists";
}

}

四、测试
JUnit单元测试

package database.factory;

import database.ConnectionParam;
import org.junit.Test;

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

import static org.junit.Assert.*;

/**
* ConnectionPoolFactory Test
* Created by Michael Wong on 2016/1/20.
*/
public class ConnectionPoolFactoryTest {

@Test
public void testGetConnection() throws SQLException {

String driver = "com.mysql.jdbc.Driver";
 String url = "jdbc:mysql://localhost:3306/test";
 String user = "root";
 String password = "root";

ConnectionParam connectionParam = new ConnectionParam.ConnectionParamBuilder(driver, url, user, password).build();
 ConnectionPoolFactory.registerConnectionPool("test", connectionParam);

List<Connection> connectionList = new ArrayList<>();

for(int i = 0; i < 12; i++) {
  connectionList.add(ConnectionPoolFactory.getConnection("test"));
 }

print();

close(connectionList);

print();

connectionList.clear();

for(int i = 0; i < 12; i++) {
  connectionList.add(ConnectionPoolFactory.getConnection("test"));
 }

print();

close(connectionList);

ConnectionPoolFactory.unRegisterConnectionPool("test");

}

@Test(expected = IllegalArgumentException.class)
public void testException() {
 try {
  ConnectionPoolFactory.getConnection("test");
 } catch (SQLException e) {
  e.printStackTrace();
 }
}

private void close(List<Connection> connectionList) throws SQLException {
 for(Connection conn : connectionList) {
  if (conn != null) {
   conn.close();
  }
 }
}

private void print() {
 System.out.println("idle: " + ConnectionPoolFactory.getIdleConnectionQuantity("test"));
 System.out.println("busy: " + ConnectionPoolFactory.getBusyConnectionQuantity("test"));
 System.out.println("size: " + ConnectionPoolFactory.size("test"));
}

}