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

以上就是本文的全部內容，希望對大家的學習有所幫助。