In this post we’ll see how to configure connection pooling in your application using Apache DBCP.
Why connection pool is needed
If a DB connection is created every time user connects to DB and closed later it results in a lot of time getting wasted in creating connection object itself to perform a database transaction that might take few milliseconds. Rather than opening a connection per user it is much more efficient to create a pool of connections that is shared among the users.
User can retrieve an idle connection object from the connection pool whenever user has to connect to DB and once done connection is returned to the pool to be used by another user. You can also impose an upper limit to the number of connections in the pool, in that case if the maximum open connection limit for the pool has been reached any further requests for the connection are queued to get the connection when it is released by other user.
Maven dependency for Apache DBCP
If you are using Maven then you can add the following dependency in pom.xml<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.8.0</version> </dependency>
You can also download the jars directly from the following location and put them in application’s classpath.
https://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi
Apache DBCP Connection pooling Java example
In the example DB used is MySQL it connects to knpcode schema and table is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT.
Apache DBCP provides a BasicDataSource class which creates a PoolingDataSource. Steps for creating a DataSource instance using BasicDataSource and configuring connection pool are as follows-
- Create an object of BasicDataSource class.
- Set the DB related configurations like Driver class, URL, user name and password.
- Specify the required connection pool related configuration out of the following-
- initialSize- The initial number of connections that are created when the pool is started. Default is 0.
- maxTotal- The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. Default is 8.
- maxIdle- The maximum number of connections that can remain idle in the pool. Default is 8.
- minIdle- The minimum number of connections that can remain idle in the pool. Default is 0.
- maxWaitMillis- The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception. Default waiting time is indefinitely.
Following class is used to create a BasicDataSource.
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class DSCreator {
private static BasicDataSource basicDS;
static {
try {
basicDS = new BasicDataSource();
Properties properties = new Properties();
// Loading properties file from classpath
InputStream inputStream = DSCreator.class
.getClassLoader()
.getResourceAsStream("db.properties");
if(inputStream == null){
throw new IOException("File not found");
}
properties.load(inputStream);
basicDS.setDriverClassName(properties.getProperty("DB.DRIVER_CLASS"));
basicDS.setUrl(properties.getProperty("DB.DB_URL"));
basicDS.setUsername(properties.getProperty("DB.DB_USER"));
basicDS.setPassword(properties.getProperty("DB.DB_PASSWORD"));
//The initial number of connections that are created when the pool is started.
basicDS.setInitialSize(Integer.parseInt(properties.getProperty("DB.INITIAL_POOL_SIZE")));
//The maximum number of active connections that can be allocated from this pool at the same time
basicDS.setMaxTotal(Integer.parseInt(properties.getProperty("DB.MAX_POOL_SIZE")));
}catch(IOException e) {
e.printStackTrace();
}
}
public static DataSource getDataSource() {
return basicDS;
}
}
Properties file used by the program to read DB properties (db.properties) make sure that this properties file is in the classpath.
DB.DRIVER_CLASS=com.mysql.cj.jdbc.Driver DB.DB_URL=jdbc:mysql://localhost:3306/knpcode DB.DB_USER=root DB.DB_PASSWORD=admin DB.INITIAL_POOL_SIZE=5 DB.MAX_POOL_SIZE=5
Test class that creates a connection using the returned DataSource object and queries the DB.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class DSDemo {
public static void main(String[] args) {
DSDemo dsDemo = new DSDemo();
dsDemo.displayEmployeeById(16);
}
private void displayEmployeeById(int id){
Connection connection = null;
String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
PreparedStatement prepStmt = null;
try {
DataSource ds = DSCreator.getDataSource();
connection = ds.getConnection();
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id: " + rs.getInt("id"));
System.out.println("First Name: " + rs.getString("FIRST_NAME"));
System.out.println("Last Name: " + rs.getString("LAST_NAME"));
System.out.println("Department: " + rs.getString("DEPARTMENT"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
That's all for the topic Apache DBCP Connection Pooling Java Example. If something is missing or you have something to share about the topic please write a comment.
You may also like
No comments:
Post a Comment