In this post we’ll see how to configure connection pooling in your Java application using Apache DBCP datasource. The DB we are connecting to is MySQL.
Jars needed
If you are using Maven then you can add the following dependency.
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.8.0</version> </dependency>
Alternatively you can download the JARs and put them in the project’s classpath, check the versions as per your Java and DB versions. You will need the following JARs
commons-dbcp2-2.8.0.jar commons-pool2-2.8.0.jar commons-logging-1.2.jar
Connection pooling using Apache DBCP - Java Example
Properties file that is used to read DB configuration.
resources/db.properties
DRIVER_CLASS=com.mysql.jdbc.Driver DB_CONNECTION_URL=jdbc:mysql://localhost:3306/netjs DB_USER=root DB_PWD=admin
In the Java example code for connection pooling using Apache DBCP there are two Java classes. We have a PooledDataSource class with a static block to create an instance of DBCP's BasicDataSource.
There is another class DSConnection where we get the instance of dbcp2 BasicDataSource and use it to get the Connection object.
PooledDataSource.java
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
public class PooledDataSource {
private static BasicDataSource basicDS;
static {
try {
basicDS = new BasicDataSource();
Properties properties = new Properties();
// Loading properties file
InputStream inputStream = new FileInputStream("resources/db.properties");
properties.load(inputStream);
basicDS.setDriverClassName(properties.getProperty("DRIVER_CLASS")); //loads the jdbc driver
basicDS.setUrl(properties.getProperty("DB_CONNECTION_URL"));
basicDS.setUsername(properties.getProperty("DB_USER"));
basicDS.setPassword(properties.getProperty("DB_PWD"));
// Parameters for connection pooling
basicDS.setInitialSize(10);
basicDS.setMaxTotal(10);
}catch(IOException e) {
e.printStackTrace();
}
}
public static DataSource getDataSource() {
return basicDS;
}
}
In this class, apart from setting the DB properties, we have set some of the parameters for the connection pool like setInitialSize() that sets the initial size of the connection pool. These many connection will immediately be created and put to connection pool.
There are more configurations like setting the maximum and minimum number of idle connections that should be there in the connection pool (setMaxIdle() and setMinIdle()), maximum size of the connection pool (setMaxTotal()), maximum number of milliseconds that the pool will wait for a connection to be returned before throwing an exception (getMaxWaitMillis()).
DSConnection.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class DSConnection {
public static void main(String[] args) {
DSConnection dsCon = new DSConnection();
try {
dsCon.displayEmployee(37);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void displayEmployee(int id) throws SQLException{
Connection connection = null;
String selectSQL = "Select * from employee where id = ?";
PreparedStatement prepStmt = null;
try {
DataSource ds = PooledDataSource.getDataSource();
// getting connection
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") + " Name: "
+ rs.getString("name") + " Age: " + rs.getInt("age"));
}
}finally{
if(prepStmt != null){
prepStmt.close();
}
if(connection != null){
connection.close();
}
}
}
}
That's all for this topic Connection Pooling Using Apache DBCP in Java. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Java Programs Page
Related Topics
You may also like-