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"));
 }

}

以上就是本文的全部内容，希望对大家的学习有所帮助。