Connection Pool

JDBC Driver

• JDBC(Java Database Connectivity) is an API defines how a client may access any kind of relational databases. It acts as a middle-layer interface between Java applications and databases.
• JDBC divers convert requests from Java programs to protocol that the DBMS can understand.

DriverManager

• Most basic way to manage JDBC drivers.
• Does not support Connection pooling and distributed transactions(분산 트랜잭션).
• In earlier version of JDBC (before 4.0), applications had to mannually register JDBC dirvers by useing Class.forName() method before requestion Connections.

Class.forName("org.h2.Driver");
Connection connection = DriverManager.getConnection(H2_URL, USER, PASSWORD);

• With JDBC 4.0, DriverManager automatically finds an appropriate JDBC dirver when the application requests a Connection.

Connection connection = DriverManager.getConnection(H2_URL, USER, PASSWORD)

DataSource

• An interface that applications use to connect to physical data sources such as databases or files.
• An alternative to the DriverManager for establishing a connection with a data source.
• Driver vendors provide DataSource implementations.

Main adavantages of using a DataSource object instead of DriverManager

• Changes can be made to a data source's properties, so do not need to modify application code when something about the data source or dirver changes.
• Connection pooling and distrivuted transactions are available through a DataSource object. DriverManager do not have them.

JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL(H2_URL);
dataSource.setUser(USER);
dataSource.setPassword(PASSWORD);

Connection connection = dataSource.getConnection();

Connection Pooling

• Connections are made in advance via the DataSource object.
• This can dramatically improve performance because creating new connections is very expensive.
• Connection pooling allows a connection to be used and reused(재사용), significantly reducing the number of new connections that need to be created.

JdbcConnectionPool

• The implementation of DataSource is provided by H2.

JdbcConnectionPool jdbcConnectionPool = JdbcConnectionPool.create(H2_URL, USER, PASSWORD);

Connection connection = jdbcConnectionPool.getConnection();

HikariCP

• Since Spring Boot 2.0, HikariCP has been adopted as the default DataSource.
• dataSourceClassName or jdbcUrl, username and password are essential.

HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setJdbcUrl(H2_URL);
hikariConfig.setUsername(USER);
hikariConfig.setPassword(PASSWORD);

Set maximum pool size

hikariConfig.setMaximumPoolSize(5);

Get HikariDataSource implementation of DataSource

DataSource dataSource = new HikariDataSource(hikariConfig);

How to set the pool size of HikariCP

• Once the number of threads exceeds the number of CPU cores, you're going slower by adding more threads, not faster.

connections = ((core_count * 2) + effective_spindle_count)

the minimum required pool size to avoid deadlock

pool size = Tn * (Cm - 1) + 1

• Tn : maximum number of threads
• Cm : maximum number of simultaneous connections held by a single thread

---

🔗 References

• https://www.geeksforgeeks.org/jdbc-drivers/
• https://docs.oracle.com/javadb/10.8.3.0/ref/rrefjdbc4_0summary.html
• https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/javax/sql/package-summary.html
• https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#data.sql.datasource.connection-pool
• https://github.com/brettwooldridge/HikariCP#rocket-initialization
• https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing