Java实现数据库连接池简易教程
作者：PerfectCoder 发布时间：2024-01-20 01:30:29
标签：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"));
}
}
0
投稿猜你喜欢
使用Python进行数据分析，大家都会多少学习一本经典教材《利用Python进行数据分析》，书中作者使用了Ipython的交互环境进行了书中- 于是写测试程序。。。不行 下载最新的ODBC。。。还是不行 通过sql plus查询。。。咦？竟然也查不到。。。 于是，折腾。。。折腾。。。
- 本文实例讲述了Python实现简单的获取图片爬虫功能。分享给大家供大家参考，具体如下：简单Python爬虫，获得网页上的照片#coding=
- DesktopNexus 是我最喜爱的一个壁纸下载网站，上面有许多高质量的壁纸，几乎每天必上， 每月也必会坚持分享我这个月来收集的壁纸但是
- Create PROCEDURE Batch_Delete @TableName nvarchar(100), --表名 @FieldNam
- 本文实例讲述了Python通过递归遍历出集合中所有元素的方法。分享给大家供大家参考。具体实现方法如下：''''
- 本文实例讲述了python threading和multiprocessing模块基本用法。分享给大家供大家参考，具体如下：前言这两天为了做
- 官方文档：【https://docker-py.readthedocs.io/en/stable/images.html】众所周知，Dock
- 前言：泛型是静态类型语言的基本特征，允许将类型作为参数传递给另一个类型、函数、或者其他结构。TypeScript 支持泛型作为将类型安全引入
- 如果你看过YUI的RAW源码，会发现很多跟javadoc语法类似的注释。据说(via)是使用JSDoc这个工具。但我探索了一遍，发现YUI多
- python的annotate函数annotate函数该函数的详细参数可调用内置属性__doc__查看。import matplotlib.
- 1、创建表 createtableTest_Increase( useridnumber(10)NOTNULLprimarykey,/*主键
- php获取图片的exif信息，php自带一个exif_read_data函数可以用来读取图片的exif信息，代码来自php手册<?ph
- strConnString = "driver={MySQL ODBC 3.51
- parse_dates : boolean or list of ints or names or list of lists or dic
- 下载地址：安装包可以从这里下载：http://www.itellyou.cn/SQL Server 2016 Enterprise with
- 前情提要好久没有写Vue了，略有生疏，这个东西还是得多用。下午看到一个需求，选择相册图片作为轮播图显示。接口返回相册列表，用户选一下再扔回去
- 引言Python中的并发编程允许你同时执行多个任务，提高程序的运行效率。在本文中，我们将介绍Python中的asyncio库，它是一个基于异
- 一、SQL模式SQL的模式匹配允许你使用“_”匹配任何单个字符，而“％”匹配任意数目字符（包括零个字符）。在 MySQL中，SQL的模式缺省
- Appium是移动端的自动化测试工具，类似于前面所说的Selenium，利用它可以驱动Android、iOS等设备完成自动化测试，比如模拟点
