I tried to find the connection pool which allows to get connection by jdbc url but failed. Hikari connection pool doesn't allow do it, the same situation in c3po. My use case is:
ConnectionPool.getConnection(jdbcUrl);
Does anybody know such connection pool in java world?
A Simple Guide to Connection Pooling in Java
Author - Baeldung
A Simple Implementation
To better understand the underlying logic of connection pooling, let's create a simple implementation.
Let's start out with a loosely-coupled design, based on just one single interface:
public interface ConnectionPool {
Connection getConnection();
boolean releaseConnection(Connection connection);
String getUrl();
String getUser();
String getPassword();
}
The ConnectionPool interface defines the public API of a basic connection pool.
Now, let's create an implementation, which provides some basic functionality, including getting and releasing a pooled connection:
public class BasicConnectionPool
implements ConnectionPool {
private String url;
private String user;
private String password;
private List<Connection> connectionPool;
private List<Connection> usedConnections = new ArrayList<>();
private static int INITIAL_POOL_SIZE = 10;
public static BasicConnectionPool create(
String url, String user,
String password) throws SQLException {
List<Connection> pool = new ArrayList<>(INITIAL_POOL_SIZE);
for (int i = 0; i < INITIAL_POOL_SIZE; i++) {
pool.add(createConnection(url, user, password));
}
return new BasicConnectionPool(url, user, password, pool);
}
// standard constructors
@Override
public Connection getConnection() {
Connection connection = connectionPool
.remove(connectionPool.size() - 1);
usedConnections.add(connection);
return connection;
}
@Override
public boolean releaseConnection(Connection connection) {
connectionPool.add(connection);
return usedConnections.remove(connection);
}
private static Connection createConnection(
String url, String user, String password)
throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public int getSize() {
return connectionPool.size() + usedConnections.size();
}
// standard getters
}
Connection pooling is a well-known data access pattern, whose main purpose is to reduce the overhead involved in performing database connections and read/write database operations.
In a nutshell, a connection pool is, at the most basic level, a database connection cache implementation, which can be configured to suit specific requirements.
In this tutorial, we'll make a quick roundup of a few popular connection pooling frameworks, and we'll learn how to implement from scratch our own connection pool.
Why Connection Pooling?
The question is rhetorical, of course.
If we analyze the sequence of steps involved in a typical database connection life cycle, we'll understand why:
Opening a connection to the database using the database driver Opening a TCP socket for reading/writing data Reading / writing data over the socket Closing the connection Closing the socket It becomes evident that database connections are fairly expensive operations, and as such, should be reduced to a minimum in every possible use case (in edge cases, just avoided).
Here's where connection pooling implementations come into play.
By just simply implementing a database connection container, which allows us to reuse a number of existing connections, we can effectively save the cost of performing a huge number of expensive database trips, hence boosting the overall performance of our database-driven applications.
JDBC Connection Pooling Frameworks
From a pragmatic perspective, implementing a connection pool from the ground up is just pointless, considering the number of “enterprise-ready” connection pooling frameworks available out there.
From a didactic one, which is the goal of this article, it's not.
Even so, before we learn how to implement a basic connection pool, let's first showcase a few popular connection pooling frameworks.
Apache Commons DBCP
public class DBCPDataSource {
private static BasicDataSource ds = new BasicDataSource();
static {
ds.setUrl("jdbc:h2:mem:test");
ds.setUsername("user");
ds.setPassword("password");
ds.setMinIdle(5);
ds.setMaxIdle(10);
ds.setMaxOpenPreparedStatements(100);
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
private DBCPDataSource(){ }
}
In this case, we've used a wrapper class with a static block to easily configure DBCP's properties.
Here's how to get a pooled connection with the DBCPDataSource class:
connection con = DBCPDataSource.getConnection();
HikariCP
public class HikariCPDataSource {
private static HikariConfig config = new HikariConfig();
private static HikariDataSource ds;
static {
config.setJdbcUrl("jdbc:h2:mem:test");
config.setUsername("user");
config.setPassword("password");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
ds = new HikariDataSource(config);
}
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
private HikariCPDataSource(){}
}
Similarly, here's how to get a pooled connection with the HikariCPDataSource class:
Connection con = HikariCPDataSource.getConnection();
