commons

org.apache.commons.dbcp2.BasicDataSource Example

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:

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.

Download
You can download the full source code of this example here: BasicDataSourceExampleCode.zip

Ashraf Sarhan

Ashraf Sarhan is a passionate software engineer, an open source enthusiast, has a Bsc. degree in Computer and Information Systems from Alexandria University. He is experienced in building large, scalable and distributed enterprise applications/service in multiple domains. He also has a keen interest in JavaEE, SOA, Agile and Big Data technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button