Updated April 18, 2023
Definition of JDBC Connection Pool
JDBC connection pool is nothing but the data access pattern, the main purpose of JDBC connection pool is to decrease the overhead of the database server connections which was performing the read-write operations. We can also say the JDBC connection pool is the basic level of cache implementation for database connection. In JDBC we can implement the connection pooling using a data source, HikariCP, c3p0 and we can also implement the connection pooling using the simple implementation method. Basically, connection pooling is performed in the background so it will not be affecting the code of the application.
Syntax:
Below is the syntax of JDBC connection pool are as follows.
1) JDBC connection pooling using Data Source –
Connection con_object = DBCPDataSource.getConnection();
2) JDBC connection pooling using HikariCP –
Connection con_object = HikariCPDataSource.getConnection();
3) JDBC connection pooling using C3p0 –
Connection con_object = C3p0DataSource.getConnection();
4) JDBC connection pooling using basic connection pool class –
ConnectionPool conpool_object = Basic ConnectionPool.create("Connection string");
Parameter description syntax of JDBC connection pool –
1) Connection and connection object – This is used to establish the connection using JDBC connection pooling. For establishing connection we need a connection object to call in the programs.
2) DBCPDataSource – This is the framework of JDBC connection pooling. Using this framework we can set up a connection to the database server. Basically, the data source is used in connection pooling. The context lookup is used in data source connection pooling, it will return the connection from the available connection objects. If suppose we have not found any available connection then a lookup is creating the new connection.
3) getConnection – This method is used to create the object of connection, which was used in SQL statement creation.
4) HikariCPDataSource – This framework is used to create connection pooling with the database server. This is a very lighting and fast framework of JDBC connection pooling.
5) C3p0DataSource – This framework is also used to create connection pooling with the database server. This is a very useful JDBC4 connection framework which was used in connection pooling.
6) ConnectionPool – This is a basic method of connection pooling which was used to create connections in java using JDBC. We can use the basic connection pool class to implement connection pooling in java.
7) ConnectionString –This is nothing but the database connection information which was used to connect the application to the database server. This parameter contains the information of hostname, we can also use IP of the database server instead of hostname. It will also contain the information of the database port and name.
How Connection pool works in JDBC?
- To establish a connection using JDBC is very resource expensive as compared to connection pooling.
- Simple JDBC connection contains the following steps, but this step is not involved in connection pooling.
1) Using database driver open the connection with the database server.
2) For reading and writing data open the TCP socket.
3) Using socket read and write the data.
4) After successful database operation close the connection.
5) Close the socket.
- As per the above database steps, we can conclude that JDBC simple connection is expensive as compared to the JDBC connection pool.
- Basically, JDBC connection pool will reuse the connection from the database server. We can improve the database performance using the JDBC connection pool.
- Below are the JDBC connection pooling frameworks available in java. We can implement JDBC connection pooling using this framework.
1) Apache common DBCP JDBC framework.
2) HikariCP framework.
3) C3p0 framework.
4) Simple implementation of JDBC connection pool.
5) Using Basic connection pool class
- Apache common DBCP framework is a very important framework used to implement JDBC connection pooling in java API.
- Using JDBC connection pooling application only uses the object of data source instead of using driver manager class.
- The class which was we have implementing can or cannot provide the connection pooling.
- Basically object of the data source is registered with the naming service of JNDI. Naming service is the way to implement the JDBC connection pooling.
- For connection reuse database connection of memory, the cache is called the connection pool. This is maintained by the module of connection pooling.
Examples
Below is the example of connection pooling in JDBC are as follows.
1) JDBC connection pooling by using data source –
The below example shows JDBC connection pooling by using data sources.
Code:
public class Hello {
private static Hello data_source = new Hello();
static
{
data_source.setUrl ("jdbc:postgresql://localhost:5432/jdbc_tran");
data_source.setUsername ("postgres");
data_source.setPassword ("postgres");
data_source.setMinIdle (30);
data_source.setMaxIdle (60);
data_source.setMaxOpenPreparedStatements (50);
System.out.println ("JDBC Connection Pool Created");
}
public static Connection /* get connection method */ getConnection() throws SQLException
{
return data_source.getConnection();
}
private void setMaxOpenPreparedStatements(int m) {
}
private void setMaxIdle(int m) {
}
private void setMinIdle(int m) {
}
private void setPassword(String string) {
}
private void setUsername(String string) {
}
private void setUrl(String string) {
}
private void DBCPDataSource(){ }
}
2) JDBC connection pooling by using HikariCP framework –
The below example shows JDBC connection pooling by using HikariCP framework.
Code:
private static HikariCP hik_ds = new HikariCP();
static {
hik_ds.setJdbcUrl ("jdbc:postgresql://localhost:5432/jdbc_tran");
hik_ds.setUsername ("postgres");
hik_ds.setPassword ("postgres");
System.out.println("JDBC connection pool created");
}
public static Connection /* get connection method */ getConnection()throws SQLException {
return hik_ds. GetConnection();
}
private void addDataSourceProperty (String string, String string2) {
}
private void setPassword(String string) {
}
private void setUsername(String string) {
}
private void setJdbcUrl(String string) {
}
private HikariCP(){}
}
3) JDBC connection pooling by using c3p0 framework –
Below example shows JDBC connection pooling by using c3p0 framework.
Code:
public class c3p0 {
private static c3p0 c3 = new c3p0();
static {
c3.setJdbcUrl("jdbc:postgresql://localhost:5432/jdbc_tran");
c3.setUser("postgres");
c3.setPassword("postgres");
System.out.println("JDBC connection pool implemented using c3p0 framework");
}
public static Connection /* get connection method */ getConnection() throws SQLException {
return c3.getConnection();
}
private void setPassword(String string) {
}
private void setUser(String string) {
}
private void setJdbcUrl(String string) {
}
private c3p0(){}
}
Conclusion
Connection pooling is nothing but the reuse of the connection of the database server. Connection pooling is performed in the background so it will not affect application coding. We can implement the JDBC connection pooling by using apache data source framework, HikariCP framework, C3p0 framework and using basic connection pooling class.
Recommended Articles
This is a guide to JDBC Connection Pool. Here we discuss the definition, syntax, parameters and working of connection pool in JDBC along with code. You may also have a look at the following articles to learn more –