服务器之家

服务器之家 > 正文

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

时间:2020-03-23 12:46     来源/作者:PerfectCoder

一、引言

  池化技术在Java中应用的很广泛,简而论之,使用对象池存储某个实例数受限制的实例,开发者从对象池中获取实例,使用完之后再换回对象池,从而在一定程度上减少了系统频繁创建对象销毁对象的开销。Java线程池和数据库连接池就是典型的应用,但并非所有的对象都适合拿来池化,对于创建开销比较小的对象拿来池化反而会影响性能,因为维护对象池也需要一定的资源开销,对于创建开销较大,又频繁创建使用的对象,采用池化技术会极大提高性能。

  业界有很多成熟的数据库连接池,比如C3P0,DBCP,Proxool以及阿里的Druid。很多以及开源,在GitHub可以找到源码,开发者可以根据自己的需求结合各种连接池的特点和性能进行选择。本文仅是为了了解学习池化技术,实现的一个简单的数据库连接池,如有错误,还望批评指正。

二、设计

主要类和接口

.ConnectionParam - 数据库连接池参数类,负责配置数据库连接以及连接池相关参数。使用Builder实现。

    driver url user password - 连接数据库所需

    minConnection - 最小连接数

    maxConnection - 最大连接数

    minIdle - 最小空闲连接数

    maxWait - 最长等待时间  

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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的动态扩容机制。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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。

  如果连接池不再使用,开发者可以注销(解绑)连接池。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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动态代理简单应用》

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@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。   

?
1
2
3
4
5
6
7
8
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

?
1
2
3
4
5
6
7
8
9
10
11
package database;
 
/**
 * Builder
 * Created by Michael Wong on 2016/1/18.
 */
public interface Builder<T> {
 
 T build();
 
}

.ConnectionParam

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
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

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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单元测试

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
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"));
 }
 
}

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

标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
返回顶部