Thursday, August 23, 2018

Connection Pooling With Apache DBCP Spring Example

This post shows how to provide JDBC connection pooling using Apache DBCP data source in Spring framework. DB used in this example is MySQL.

Maven dependency for DBCP

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.1</version>
</dependency>

Alternatively you can download the following jars and put them in the classpath.

commons-dbcp2-2.1.1.jar
commons-pool2-2.5.0.jar
commons-logging-1.2.jar

Connection pooling with DBCP Spring example

For configuring datasource you need to set up some properties. It is better to use a properties file for storing those properties and refer that properties file while configuring datasource.

db.properties

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=user
db.password=password
pool.initialSize=5

Description for the properties used here is as-

driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.

Url– You need to provide url to access your DB server. The schema that is used here is called netjs and DB is running on the same system so url is- jdbc:mysql://localhost:3306/netjs.

Username and password for the DB.

IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.

In this example Spring JdbcTemplate is used to query the DB. If you are using Spring XML configuration then configuration for DataSource and JDBCTemplate is as follows.

Spring XML Configuration (appContext.xml)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx.xsd">
    
  <context:component-scan base-package="org.netjs.daoimpl" />
  <!--  For reading properties files --> 
  <context:property-placeholder location="classpath:config/db.properties" />
    
  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
    <property name="dataSource" ref="dataSource"></property>  
  </bean>   
  <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value = "${db.driverClassName}" />
    <property name="url" value = "${db.url}" />
    <property name="username" value = "${db.username}" />
    <property name="password" value = "${db.password}" />
    <property name="initialSize" value = "${pool.initialSize}" />
  </bean>
</beans>

In this XML configuration, <context:property-placeholder> tag is used to give the path to db.properties file.

For DBCP datasource configuration class is “org.apache.commons.dbcp2.BasicDataSource”.

DataSource bean has to be provided as a reference in JDBCTemplate.

DBTable

Database table used in this example.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Java classes

EmployeeDAO interface

public interface EmployeeDAO {
    public List<Employee> findAllEmployees();
}

EmployeeDAOImpl.java

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
  @Autowired
  private JdbcTemplate jdbcTemplate; 
  @Override
  public List<Employee> findAllEmployees() {
    final String SELECT_ALL_QUERY = "SELECT * from EMPLOYEE";
    return this.jdbcTemplate.query(SELECT_ALL_QUERY, new EmployeeMapper());
  }

  private static final class EmployeeMapper implements RowMapper<Employee> {
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
      Employee emp = new Employee();
      emp.setEmpId(rs.getInt("id"));
      emp.setEmpName(rs.getString("name"));
      emp.setAge(rs.getInt("age"));
      return emp;
    }
  }
}

Employee.java

public class Employee {
 private int empId;
 private String empName;
 private int age;
 public int getEmpId() {
  return empId;
 }
 public void setEmpId(int empId) {
  this.empId = empId;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
}

You can run this example using the following code.

public class App {
  public static void main(String[] args) {
    ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("appcontext.xml");

    EmployeeDAO dao = (EmployeeDAO)context.getBean("employeeDAOImpl");  
    List<Employee> empList = dao.findAllEmployees();
    System.out.println("Name - "+ empList.get(0).getEmpName() + " 
       Age - " + empList.get(0).getAge());
    context.registerShutdownHook();   
  }    
}

DBCP datasource with Spring Java config

If you want to use Spring Java Configuration then you can create an object of BasicDataSource and set the properties.

 @Bean
 public BasicDataSource basicDataSource(){
  BasicDataSource ds = new BasicDataSource();
  ds.setDriverClassName("com.mysql.jdbc.Driver");
  ds.setUrl("jdbc:mysql://localhost:3306/netjs");
  ..
  ..
  return ds;
 }

That's all for this topic Connection Pooling With Apache DBCP Spring Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Spring Tutorial Page


Related Topics

  1. Data Access in Spring Framework
  2. JDBCTemplate With ResultSetExtractor Example in Spring
  3. Spring Batch Processing With List of Objects in batchUpdate() Method
  4. Spring Transaction Management JDBC Example Using @Transactional Annotation
  5. Spring MVC Example With @PathVaribale - Creating Dynamic URL

You may also like-

  1. How to Inject Prototype Scoped Bean in Singleton Bean
  2. Lazy Initializing Spring Beans
  3. ServiceLocatorFactoryBean in Spring
  4. Excluding Bean From Autowiring in Spring
  5. Functional interface annotation in Java 8
  6. Stream API in Java 8
  7. BigDecimal in Java
  8. Shuffle And Sort Phases in Hadoop MapReduce