In this example we shall show you how to make use of BasicDataSource class of Apache Commons DBCP. Nowadays, many web applications interacts with a relational database, Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. Opening a connection per user can be infeasible in a large internet application where the number of simultaneous users can be very huge.
Accordingly, developers often wish to share a pool of open connections between all of the application’s current users. The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required.
Connection Pooling is a pattern used by software applications to connect to databases using a pre-created set of reusable connection objects. When a new connection is required, an existing connection is retrieved from the pool. When the thread using the connection has completed, it is placed back in pool for use by another thread. This pattern reduces the overhead of connecting to a database by decreasing network traffic, limiting the cost of creating new connections, and reducing the load on the garbage collector.
Many Java Application Frameworks include their own connection pooling APIs. But the principles used to configure all frameworks are generally the same. In this article, you’ll learn how to create a database connection pool using the Java Database Connectivity (JDBC) API and the Apache Commons DBCP pooling library.
Dependencies:
<!-- Apache Commons DBCP -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.0.1</version>
</dependency>
<!-- MySQL Driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.34</version>
</dependency>
Example:
DataSource.java:
package com.jcg;
import org.apache.commons.dbcp2.BasicDataSource;
/**
* @author ashraf
*
*/
public class DataSource {
private static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
private static final String DB_URL = "jdbc:mysql://localhost:3306/emp";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "root123";
private static final int CONN_POOL_SIZE = 5;
private BasicDataSource bds = new BasicDataSource();
private DataSource() {
//Set database driver name
bds.setDriverClassName(DRIVER_CLASS_NAME);
//Set database url
bds.setUrl(DB_URL);
//Set database user
bds.setUsername(DB_USER);
//Set database password
bds.setPassword(DB_PASSWORD);
//Set the connection pool size
bds.setInitialSize(CONN_POOL_SIZE);
}
private static class DataSourceHolder {
private static final DataSource INSTANCE = new DataSource();
}
public static DataSource getInstance() {
return DataSourceHolder.INSTANCE;
}
public BasicDataSource getBds() {
return bds;
}
public void setBds(BasicDataSource bds) {
this.bds = bds;
}
}
DataSource is a bill pugh singleton class which provides a single BasicDataSource instance with the following properties:
- Using
setDriverClassName(String driverClassName)method to set the JDBC driver class name. - Using
setUrl(String url)method to set the database URL. - Using
setUsername(String username)to set the database username. - Using
setPassword(String password)to set the database user password. - Using
setInitialSize(int initialSize)to set connection pool size.
BasicDataSourceDemo.java:
package com.jcg;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp2.BasicDataSource;
/**
* @author ashraf
*
*/
public class BasicDataSourceDemo {
/**
* @param args
*/
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
BasicDataSource bds = DataSource.getInstance().getBds();
connection = bds.getConnection();
statement = connection
.prepareStatement("select e.id, e.firstName, e.lastName, e.salary, d.name as deptName from emp.employee as e inner join emp.department as d on e.departmentId = d.id limit 10");
resultSet = statement.executeQuery();
System.out
.println("employeeId, employeeName, employeeSalary, employeeDepartment");
while (resultSet.next()) {
System.out.println(resultSet.getString("id") + ", "
+ resultSet.getString("firstName") + " "
+ resultSet.getString("lastName") + ", "
+ resultSet.getString("salary") + ", "
+ resultSet.getString("deptName"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
BasicDataSourceDemo class gets a BasicDataSource instance using the returned DataSource instance from the getInstance() method of the DataSource class, then it calls the getConnection() method of BasicDataSource to get the Connection for the database, After that it creates a PreparedStatement object to send a SQL query to the database using the prepareStatement(String sql) method of the newly created Connection. Finally, it executes the SQL query in this PreparedStatement object using executeQuery() method of PreparedStatement and prints the values of the ResultSet. Also, it closes all the resources (ResultSet, PreparedStatement and Connection).
Output:
employeeId, employeeName, employeeSalary, employeeDepartment 1, Angel Forrest, 22569, Information Technology 2, Michael Moreno, 38908, Information Technology 3, Clara Williams, 28431, Information Technology 4, Jennifer Soltero, 14229, Information Technology 5, Kirk Baker, 39939, Information Technology 6, Julia Kersten, 48575, Information Technology 7, Dale Copenhaver, 7904, Information Technology 8, Genevieve Evans, 17125, Information Technology 9, Beverly Barnes, 30910, Information Technology 10, Raymond McFall, 16315, Information Technology
Download the Source Code of this example:
This was an example of how to use Apache Commons DBCP BasicDataSource class.
You can download the full source code of this example here: BasicDataSourceExampleCode.zip
