A simple tutorial for implementing database connection pooling in Java

  • 2020-04-01 04:38:07
  • OfStack

One, the introduction

Pooling technology is widely used in Java. In short, using an object pool to store an instance with a limited number of instances, the developer gets the instance from the object pool and then returns to the object pool after using it, thus reducing the overhead of frequently creating and destroying objects in the system to some extent. Java thread pool and the database connection pool is a typical application, but not all objects are good for pooling, costs for creating smaller object pooling instead affects performance, because the maintenance overhead object pool also need certain resources, for creating overhead, and often used to create objects, by pooling technology can greatly improve the performance.

There are many established database connection pools in the industry, such as C3P0, DBCP, Proxool, and Druid for ali. Many as well as open source, the source code can be found on GitHub, and developers can choose according to their own needs in combination with the characteristics and performance of various connection pools. This article is only to understand the study of pooling technology, to achieve a simple database connection pool, if there is an error, also hope to criticize.

Second, the design

Main classes and interfaces

. ConnectionParam - Database connection pool parameter class, which is responsible for configuring database connections and connection pool related parameters. Implementation using Builder.

Driver url user password - required to connect to the database

MinConnection - minimum number of connections

MaxConnection - maximum number of connections

MinIdle - minimum number of idle connections

MaxWait - longest waiting time    


 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;

The ConnectionPool - Database connection pool

The ConnectionPool constructor is declared protected, external creation is prohibited, and is managed uniformly by ConnectionPoolFactory.

ConnectionPool implements the DataSource interface and resets the connection () method.

ConnectionPool holds two containers - a Queue to store idle connections and a Vector (for synchronization purposes) to store in-use connections.

When a developer USES a database connection, it gets it from the Queue and returns nothing if there is none. When close is completed, the Vector is returned.

ConnectionPool provides a simple dynamic scaling mechanism based on minIdle and 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 - Connection pool management class

ConnectionPoolFactory holds a static ConcurrentHashMap to store connection pool objects.

ConnectionPoolFactory allows you to create multiple connection pools for different databases with different configurations.

The developer first needs to register (bind) the Connection pool with a specific name and then get the Connection each time from the specified Connection pool.

If the connection pool is no longer in use, the developer can log off (unbind) the connection pool.


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

The core code

  The core of the database Connection pool code is the getConnection() method, which is typically called by the developer after a database operation, and the Connection should be closed and the resource released. In the database Connection pool, user call close () method, which should not be directly close Connection, but will be returned to the pool, repeated use, here use Java dynamic proxy mechanism, getConnection returns are not "real" Connection, but the custom of the proxy class (used here anonymous classes), when a user calls close () method, to intercept, back into the pool. For more on dynamic proxy, see another blog post, Java dynamic proxy simple applications


 @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;
 }

Second, the use of

First, the user builds database connection pool parameters (ConnectionParam), including driver, url, user, password must items, can customize minConnection, maxConnection and other options, if not set, use the system default value, which is the advantage of using Builder to build with a lot of properties, including must properties and optional properties. The Connection pool is then registered with the ConnectionPoolFactory with a specific name, and finally the Connection is obtained by calling the ConnectionPoolFactory static factory method.      


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


Three, code,

. 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() {}

}

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

 }

}

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

}

Four, test,
JUnit unit tests


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

}

The above is the entire content of this article, I hope to help you with your study.


Related articles: