Database Connection Pool in Spring
Database Connection Pool in Spring
In a database driven application, creating a new connection object is a costly operation (cumulative time in establishing connections, network traffic, burden on the garbage collector etc).
A typical database connection cycle will follow the following steps;
- An application requests for a database connection (via a DataSource using a database driver).
- A new database connection is created, and a TCP socket is opened for read/write operations.
- The application then performs read and write operation to the database.
- When a database connection is no longer need, it is closed.
- The TCP socket is finally closed.
For most enterprise database intense application, the cost of creating new database connection, the network traffic will impact on the overall performance of the application without employing a database connection pooling pattern.
Database Connection pooling is a pattern used by software applications to connect to databases using already created set of reusable connection objects. The connection pool acts as a cache of the open database connections.
When a new connection is requested, an existing available connection is retrieved from the pool instead of creating a new one. Similarly, a connection is placed back to the pool when it is no longer needed.
Connection pooling reduces the database management system I/O overhead of connecting to a database by decreasing network traffic and ensuring that new connections are only created when there is no available connection in the pool.
Every new connection requires some database resources, so in order to prevent the database resources from being depleted, the connection pool acts a circular or bounded buffer for incoming new connection requests.
JDBC Connection pooling
There are several JDBC connection pooling implementations but the most popularly used ones are HikariCP, Tomcat JDBC Connection Pool, Apache Commons DBCP 2 and C3P0.
1. C3P0
c3p0 is a mature, highly concurrent JDBC Connection pooling library, with support for caching and reuse of PreparedStatements.
Using C3P0
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
C3P0 javax.sql.DataSource
config
@Bean
public DataSource dataSource() {
ComboPooledDataSource pooledDataSource = new ComboPooledDataSource();
pooledDataSource.setJdbcUrl(configProperties.getUrl());
pooledDataSource.setUser(configProperties.getUsername());
pooledDataSource.setPassword(configProperties.getPassword());
return pooledDataSource;
}
2. Apache Commons DBCP 2.
DBCP 2 is based on Commons Pool 2 and provides increased performance, JMX support as well as numerous other new features compared to DBCP 1.x.
Using Apache DBCP 2
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.7.0</version>
</dependency>
Apache DBCP 2 javax.sql.DataSource
config
@Bean
public DataSource dataSource() {
BasicDataSource dbcpDataSource = new BasicDataSource();
dbcpDataSource.setUrl(configProperties.getUrl());
dbcpDataSource.setUsername(configProperties.getUsername());
dbcpDataSource.setPassword(configProperties.getPassword());
dbcpDataSource.setInitialSize(1);
return dbcpDataSource;
}
3. Tomcat JDBC Connection Pool
The JDBC Connection Pool org.apache.tomcat.jdbc.pool
is a replacement or an alternative to the Apache Commons DBCP connection pool.
Apache Commons DBCP 1.x is single threaded, slow, uses static interfaces and over 60 Java classes and hence the need for the Tomcat JDBC connection pool that is simpler and offers numerous feature advantages over Apache Commons DBCP connection pool as discussed on apache tomcat website.
Using Tomcat JDBC Connection Pool
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>9.0.36</version>
</dependency>
Tomcat JDBC Connection Pool javax.sql.DataSource
config
@Bean
public DataSource dataSource() {
PoolProperties p = new PoolProperties();
p.setUrl(configProperties.getUrl());
p.setDriverClassName("com.mysql.cj.jdbc.Driver");
p.setUsername(configProperties.getUsername());
p.setPassword(configProperties.getPassword());
//more setting
p.setJmxEnabled(true);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1");
p.setTestOnReturn(false);
p.setValidationInterval(30000);
p.setTimeBetweenEvictionRunsMillis(30000);
p.setMaxActive(100);
p.setInitialSize(10);
p.setMaxWait(10000);
p.setRemoveAbandonedTimeout(60);
p.setMinEvictableIdleTimeMillis(30000);
p.setMinIdle(10);
p.setLogAbandoned(true);
p.setRemoveAbandoned(true);
p.setJdbcInterceptors(
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
org.apache.tomcat.jdbc.pool.DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource(p);
return ds;
}
4. HikariCP
A lightweight, fast, reliable and arguable the most recommended high performance, production ready JDBC connection pooling implementation.
Using HikariCP
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.4.5</version>
</dependency>
HikariCP javax.sql.DataSource
config
@Bean
public DataSource dataSource() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl(configProperties.getUrl());
config.setUsername(configProperties.getUsername());
config.setPassword(configProperties.getPassword());
//more config
config.setPoolName("example-pool");
config.addDataSourceProperty("cachePrepStmts", true);
config.addDataSourceProperty("prepStmtCacheSize", 250);
config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
config.addDataSourceProperty("useServerPrepStmts", true);
config.addDataSourceProperty("rewriteBatchedStatements", true);
config.addDataSourceProperty("cacheResultSetMetadata", true);
config.addDataSourceProperty("cacheServerConfiguration", true);
config.addDataSourceProperty("elideSetAutoCommits", true);
config.addDataSourceProperty("maintainTimeStats", false);
HikariDataSource ds = new HikariDataSource(config);
return ds;
}
Testing the Connections
We can test our configured javax.sql.DataSource as configured in the profiles.
@SpringJUnitConfig({ ConfigProperties.class, PoolConfig.class})
public class PoolConfigTest {
@Autowired
private DataSource dataSourceHikariCP;
@Autowired
private DataSource dataSourceDBCP;
@Autowired
private DataSource dataSourceCp30;
@Autowired
private DataSource dataSourceTomcatJdbc;
/**
* Test of dataSourceCp30 method, of class PoolConfig.
*/
@Test
public void testDataSourceCp30() {
assertThat(dataSourceCp30).isNotNull();
assertThat(dataSourceCp30).isInstanceOf(ComboPooledDataSource.class);
}
/**
* Test of dataSourceDBCP method, of class PoolConfig.
*/
@Test
public void testDataSourceDBCP2() {
assertThat(dataSourceDBCP).isNotNull();
assertThat(dataSourceDBCP).isInstanceOf(BasicDataSource.class);
}
/**
* Test of dataSourceHikariCP method, of class PoolConfig.
*/
@Test
public void testDataSourceHikariCP() {
assertThat(dataSourceHikariCP).isNotNull();
assertThat(dataSourceHikariCP).isInstanceOf(HikariDataSource.class);
}
/**
* Test of dataSourceTomcatJdbc method, of class PoolConfig.
* @throws java.sql.SQLException
*/
@Test
public void testDataSourceTomcatJdbc() throws SQLException {
assertTrue(dataSourceTomcatJdbc.getConnection().isValid(1));
assertThat(dataSourceTomcatJdbc).isInstanceOf(org.apache.tomcat.jdbc.pool.DataSource.class);
}
}
The example code does not use spring boot and is available on GitHub.
Reference
Apache tomcat JDBC pool - https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html
Apache commons DBCP - https://commons.apache.org/proper/commons-dbcp/
HikariCP - https://github.com/brettwooldridge/HikariCP
Similar Posts ..
Comments (1)
@Amelia Brown on April 1, 2024 at 10:04 PM Reply | |
|
Hi there,
We run a YouTube growth service, which increases your number of subscribers both safely and practically.
- We guarantee to gain you 700+ new subscribers per month.
- People subscribe because they are interested in your videos/channel, increasing video likes, comments and interaction.
- All actions are made manually by our team. We do not use any 'bots'.
The price is just $60 (USD) per month, and we can start immediately.
If you'd like to see some of our previous work, let me know, and we can discuss it further.
Kind Regards,
Amelia,Hi there,
We run a YouTube growth service, which increases your number of subscribers safely and practically.
We aim to gain you 700+ real human subscribers per month, with all actions safe as they are made manually (no bots).
The price is just $60 (USD) per month, and we can start immediately.
Let me know if you wish to see some of our previous work.
Kind Regards,
Amelia
|