自己实现一个mini的数据库连接池
注意点:
- 数据库连接池中存放的就是数据库操作管道,不仅仅是存放,而且应该是管理这些管道;
- 应该提供外部配置文件去初始化数据库连接池;
- 如果一个数据库操作管道已经被占用,那么其他请求是否应该得到这个管道,也就是说我们要考虑多线程并发下,管道的分配问题;
- 如果做到管道的复用?放回池子中,标示可用,并不是真正的关闭管道;
/**
* @ClassName XML
* @Description 代表xml,配置文件类
* @Author lzq
* @Date 2019/7/8 02:10
* @Version 1.0
**/
public class XML {
public static final String Driver = "com.mysql.jdbc.Driver";
public static final String Url = "jdbc:mysql://127.0.0.1:3306/test4";
public static final String User = "root";
public static final String Password = "123456";
//数据库连接池初始化大小
public static final int initCount = 2;
//连接池连接不足的时候新创建的连接数量
public static final int step = 2;
//连接池的最大数量
public static final int maxCount = 10;
public static final int maxTime = 200;
}
import java.sql.Connection;
/**
* @ClassName GovernConnection
* @Description 管理连接通道的类
* @Author lzq
* @Date 2019/7/8 02:18
* @Version 1.0
**/
public class GovernConnection {
private Connection connection;
private boolean sign = false; //标记通道是否被占用 false:没有被占用
private long startTime = 0;
public GovernConnection(Connection connection,boolean sign,long startTime) {
this.connection = connection;
this.sign = sign;
this.startTime = startTime;
}
public void setSign(boolean sign) {
this.sign = sign;
}
public boolean isSign() {
return sign;
}
public Connection getConnection() {
return connection;
}
public void setConnection(Connection connection) {
this.connection = connection;
}
public void setStartTime(long startTime) {
this.startTime = startTime;
}
public long getStartTime() {
return startTime;
}
/**
* 关闭通道,那么标记这个通道,以示其他线程可以用了
*/
public void close() {
this.sign = false;
setStartTime(System.currentTimeMillis());
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.locks.ReentrantLock;
/**
* @ClassName MyConnectionPool
* @Description 数据库连接池
* 加载驱动、初始化变量
* 创建、获取连接
* 用集合存储连接
* @Author lzq
* @Date 2019/7/8 02:20
* @Version 1.0
**/
public class MyConnectionPool {
private ReentrantLock lock = new ReentrantLock();
private List<GovernConnection> myPool = new ArrayList<>();
private List<GovernConnection> remove = new ArrayList<>();
private static String url;
private static String user;
private static String password;
private static int initCount;
private static int step;
private static int maxCount;
private static int maxTime;
public MyConnectionPool() {
//初始化参数
init();
//加载驱动
try {
Class.forName(XML.Driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 初始化连接池配置
*/
private void init() {
url = XML.Url;
user = XML.User;
password = XML.Password;
initCount = XML.initCount;
step = XML.step;
maxCount = XML.maxCount;
maxTime = XML.maxTime;
}
/**
* 创建操作通道的类
* @param count
*/
private synchronized void createConnection(int count) {
count = count+myPool.size() < maxCount ? count : maxCount-myPool.size();
for (int i = 0; i < count; i++) {
try {
Connection connection = DriverManager.getConnection(url,user,password);
GovernConnection governConnection = new GovernConnection(connection,false,System.currentTimeMillis());
myPool.add(governConnection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取操作通道的类
* @return
*/
public GovernConnection getConnection() {
GovernConnection governConnection = null;
try {
lock.lock();
if(myPool.size() < 1) {
//初始化指定数量的数据库连接池管道
createConnection(initCount);
}
governConnection = getGovernConnection();
while (governConnection == null) {
System.out.println("创建新链接");
createConnection(step);
governConnection = getGovernConnection();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
lock.unlock();
}
return governConnection;
}
/**
* 在连接池里面找一个未被使用的连接
* @return
*/
private GovernConnection getGovernConnection() throws SQLException {
boolean flag = false;
Iterator<GovernConnection> iterator = myPool.iterator();
while (iterator.hasNext()) {
GovernConnection governConnection = iterator.next();
Connection connection = governConnection.getConnection();
//如果当前通道已经关闭或超时,在连接池中去除它
if(connection.isClosed() || System.currentTimeMillis()-governConnection.getStartTime() > maxTime) {
System.out.println("出现关闭或超时的连接");
remove.add(governConnection);
flag = true;
}
//未超时,也未被占用
else if(!governConnection.isSign()) {
governConnection.setStartTime(System.currentTimeMillis());
governConnection.setSign(true);
return governConnection;
}
}
if(flag) {
remove();
}
return null;
}
/**
* 删除超时的和关闭的连接
* 但不能删除到比最小连接数还小(剩下那部分重置)
*/
private void remove() {
int removeCount = remove.size();
if(myPool.size()-removeCount < initCount) {
delete(myPool.size()-initCount);
reset();
}else {
delete(myPool.size()-removeCount);
}
}
/**
* 删除指定个数连接
* @param count
*/
private void delete(int count) {
Iterator<GovernConnection> iterator = remove.iterator();
while (iterator.hasNext() && count > 0) {
GovernConnection governConnection = iterator.next();
myPool.remove(governConnection);
iterator.remove();
count--;
}
}
/**
* 重置连接
*/
private void reset() {
Iterator<GovernConnection> iterator = remove.iterator();
while (iterator.hasNext()) {
GovernConnection governConnection = iterator.next();
governConnection.setStartTime(System.currentTimeMillis());
governConnection.setSign(false);
iterator.remove();
}
}
/**
* 打印一下现存的连接
*/
public void show() {
for (GovernConnection governConnection: myPool) {
System.out.println(governConnection+"\t"+governConnection.getConnection());
}
System.out.println(myPool.size());
}
}
/**
* @ClassName ConnectionsPool
* @Description 获取连接池的单例对象
* @Author lzq
* @Date 2019/7/8 02:23
* @Version 1.0
**/
enum MyConnectionsPool {
MY_POOL;
private MyConnectionPool myPool;
MyConnectionsPool() {
this.myPool = new MyConnectionPool();
}
public MyConnectionPool getMyDBPool() {
return myPool;
}
}
public class ConnectionsPool {
public static MyConnectionPool getMyConnectionPool() {
return MyConnectionsPool.MY_POOL.getMyDBPool();
}
}
import java.sql.Connection;
/**
* @ClassName TestDemo10
* @Description 测试类
* @Author lzq
* @Date 2019/7/8 02:54
* @Version 1.0
**/
public class TestDemo10 {
public static void main(String[] args) throws InterruptedException {
MyConnectionPool myConnectionPool = ConnectionsPool.getMyConnectionPool();
show2(myConnectionPool);
}
private static void show2(MyConnectionPool myConnectionPool) throws InterruptedException {
TestDemo10 demo10 = new TestDemo10();
for (int i = 0; i < 50; i++) {
TestDemo10.MyTestThread myTestThread = demo10.new MyTestThread(myConnectionPool);
myTestThread.start();
myTestThread.join();
}
myConnectionPool.show();
}
private class MyTestThread extends Thread {
private MyConnectionPool myConnectionPool;
public MyTestThread(MyConnectionPool myConnectionPool) {
this.myConnectionPool = myConnectionPool;
}
@Override
public void run() {
GovernConnection governConnection = myConnectionPool.getConnection();
for (int i = 0; i < 5; i++) {
String sql = "select * from student";
Connection connection = governConnection.getConnection();
}
System.out.println(Thread.currentThread().getName()+"\t"+governConnection
+"\t"+governConnection.getConnection());
governConnection.close();
}
}
}
运行结果:
我这个连接池里面封装了一个GovernConnection类,它的作用是管理连接,并记录当前连接是否已经被占用,以及它的起始时间(后面用来判断是否需要超时回收的),虽然让逻辑简单了一点,但是它却增加了学习成本,因为像c3p0、dbcp、druid这些连接池返回的直接是一个Connection对象,然后操作这个Connection对象的,但是思想还是可以借鉴的,你只需要连接池就是这么个原理就好了。
那么还有一种实现连接池的方式是,定义两个集合存放连接,其中一个存放的连接是不能被回收的(它存放的是连接池最小连接数的连接,也就是连接池的连接数不能比这个还小了),对于它里面的超时连接,只能重置,而另一个集合存放的连接是可以被超时回收的,也就是大于连接池最小存在数而小于最大存在数的连接全放在它里面,然后其他操作与以上代码思想类似;