Java实现数据库连接池简易教程-eolink官网

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 idleConnectionQueue;

private Vector busyConnectionVector;

.ConnectionPoolFactory - 连接池管理类

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

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

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

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

private static Map 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 build();

}

.ConnectionParam

package databewoDWQkrXtase;

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 {

// 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 idleConnectionQueue;

private Vector 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 unwrap(Class 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 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 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 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"));

}

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

Iterator与LIstIterator接口在java中的区别有哪些

311 2023-07-22

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

Flask接口签名sign原理与实例代码浅析

zookeeper python接口实例详解

Iterator与LIstIterator接口在java中的区别有哪些

推荐文章

接口调用是什么意思？几种常用接口调用方式

接口设计原则

8款在线 API 接口文档管理工具

api管理系统是什么？

什么是接口调试？接口调试的步骤有哪些？

api 接口管理系统有哪些？

接口测试有几种测试方法

API文档生成工具有哪些？

微服务和api网关区别

交换机配置步骤

最近发表

热评文章

在线接口文档管理工具推荐，支持在线测试，HTTP接口

开源的在线接口文档wiki工具Mindoc的介绍与使

如何优雅的进行接口设计？接口设计的六大原则是什么？

什么是API测试,api检测公司

软件接口设计怎么做？前后端分离软件接口设计思路

接口管理平台推荐，几大接口管理平台总有一款适合你！