Java JDBC Datasource Example using Apache DBCP with H2, MySQL and DB2

In this tutorial we will discuss how to set up a Java JDBC Datasource using Apache DBCP (Database Connection Pooling) various databases including H2, MySQL and DB2.

Why are the DataSources preferred over Database DriverManager?

One of the main reasons for using Java DataSource over DriverManager is that the underlying application need not know the underlying details about the attached data source.
In addition, DataSource objects are closely associated with Java Naming and Directory Interface(JNDI) and can be managed independently from the Java application. The other main reason for using DataSource objects is that they are generally associated with connection pooling which further enhances application performance.

Creating a new connection for each request or each user can be a time consuming process often taking many clock cycles to execute. By using a connection pool we are able to enhance application performance when it comes to connecting to a database. Connection pools define a cache of opened connections that may be reused when future requests to the database are needed. If all connections are in use itmay open additional connections and add those to the existing pool of connections. Usually, connection pools allow you to define the minimum and maximum number of connections available to the pool.

Advantages of using Connection Pools

• Reduces wait time in establishing a connection to the database since database operations are expensive and time consuming.
• Reduces the number of times new connections are physically created
• Promotes connection object reuse
• Minimizes the number of stale connections

Commonly used SharedPoolDataSource Methods

• void close()
  Close the connection pool being maintained by this datasource
• Connection getConnection()
  Returns a database connection
• Connection getConnection(String username, String password)
  Returns a database connection using username and password
• int getDefaultMaxTotal()
  Gets the default value for maximum connections per user pool
• int getNumActive()
  Get the number of active connections in the pool
• int getMaxTotal()
  Gets the maximum connections for this pool
• void setMaxTotal(int maxTotal)
  Set the maximum number of connections for this pool
• int getNumIdle()
  Get the number of idle connections in the pool
• int getMinEvictableIdleTimeMillis()
  Returns the minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any)
• int getTimeBetweenEvictionRunsMillis()
  Returns the number of milliseconds to sleep between runs of the idle object evictor thread
• void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis)
  Sets the minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor
• void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis)
  Sets the number of milliseconds to sleep between runs of the idle object evictor thread
• int getMaxWait()
  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
• void setMaxWait(int maxWait)
  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

Getting Started

In order to run this tutorial yourself, you will need the following:

• Java JDK 1.6 or greater
• Favorite IDE Spring Tool Suite (STS), Eclipse IDE or NetBeans (I happen to be using STS because it comes with a Tomcat server built-in)
• H2 Database
• MySQL Database
• IBM DB2 Database
• JTOpen Java Toolbox
• Jackson Mapper for Object to JSON and vice-versa serialization/deserialization

Required Libraries

Copy all of the following jars to WebContent->WEB-INF->lib folder.

commons-dbcp-1.4.jar
commons-pool-1.6.jar
h2-1.4.177.jar
jackson-core-asl-1.9.13.jar
jackson-core-lgpl-1.9.13.jar
jackson-jaxrs-1.9.13.jar
jackson-mapper-asl-1.9.13.jar
jt400.jar
mysql-connector-java-5.1.22.jar

Complete Project Overview

I have added the project overview to give you a full view of the structure and show you all files contained in this sample project.

The Customer Model (Customer.java)

This will be used to as the object which we store and retrieve in order to test out our application. I added it because I wanted my web service to store and retrieve some Java object.

package com.avaldes.model;

import java.util.Date;

import org.codehaus.jackson.annotate.JsonProperty;
import org.codehaus.jackson.map.annotate.JsonSerialize;

import com.avaldes.util.JsonDateTimeSerializer;

public class Customer {
  private int customerId;
  private String firstName;
  private String lastName;
  private String address;
  private String city;
  private String state;
  private String zipCode;
  private int isActive;
  private String createdBy;
  private Date createdDate;
  
  @JsonProperty(value = "customer-id")
  public int getCustomerId() {
    return customerId;
  }
  
  public void setCustomerId(int customerId) {
    this.customerId = customerId;
  }
  
  @JsonProperty(value = "first-name")
  public String getFirstName() {
    return firstName;
  }
  
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  
  @JsonProperty(value = "last-name")
  public String getLastName() {
    return lastName;
  }
  
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  
  @JsonProperty(value = "address")
  public String getAddress() {
    return address;
  }
  
  public void setAddress(String address) {
    this.address = address;
  }
  
  @JsonProperty(value = "city")
  public String getCity() {
    return city;
  }
  
  public void setCity(String city) {
    this.city = city;
  }
  
  @JsonProperty(value = "state")
  public String getState() {
    return state;
  }
  
  public void setState(String state) {
    this.state = state;
  }
  
  @JsonProperty(value = "zip-code")
  public String getZipCode() {
    return zipCode;
  }
  
  public void setZipCode(String zipCode) {
    this.zipCode = zipCode;
  }
  
  @JsonProperty(value = "is-active")
  public int getIsActive() {
    return isActive;
  }
  
  public void setIsActive(int isActive) {
    this.isActive = isActive;
  }
  
  @Override
  public String toString() {
    return "Customer [customerId=" + customerId + ", firstName=" 
        + firstName + ", lastName=" + lastName + ", address=" 
        + address + ", city=" + city + ", state=" + state 
        + ", zipCode=" + zipCode + ", isActive=" + isActive 
        + "]";
  }
}

Creating the Shared Pool DataSource for MySQL

package com.avaldes.util;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS;
import org.apache.commons.dbcp.datasources.SharedPoolDataSource;

public class ApacheMySQLDataSource {
  
  private DataSource getApacheDataSource() throws ClassNotFoundException {
    DataSource ds;
    
    DriverAdapterCPDS driver = new DriverAdapterCPDS();
    
    driver.setDriver("org.gjt.mm.mysql.Driver");
    driver.setUrl("jdbc:mysql://localhost:3306/tutorialdb");
    driver.setUser("webuser");
    driver.setPassword("EasyAs123!");

    SharedPoolDataSource sharedPoolDS = new SharedPoolDataSource();
    sharedPoolDS.setConnectionPoolDataSource(driver);
    sharedPoolDS.setMaxActive(10);
    sharedPoolDS.setMaxWait(50);
    sharedPoolDS.setTestOnBorrow(true);
    sharedPoolDS.setValidationQuery("SELECT 1");
    sharedPoolDS.setTestWhileIdle(true);
    ds = sharedPoolDS;
    
    return ds;
  }
  
  public Connection getConnection() 
			throws ClassNotFoundException, SQLException {
			
    DataSource ds = getApacheDataSource();
    
    return ds.getConnection(); 
  }
}

Creating the Shared Pool DataSource for H2

package com.avaldes.util;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS;
import org.apache.commons.dbcp.datasources.SharedPoolDataSource;

public class ApacheH2DataSource {
  
  private DataSource getDataSource() throws ClassNotFoundException {
    DataSource ds;
    
    DriverAdapterCPDS driver = new DriverAdapterCPDS();
      
    driver.setDriver("org.h2.Driver");
    driver.setUrl("jdbc:h2:~/test");
    driver.setUser("sa");
    driver.setPassword("");
    
    SharedPoolDataSource sharedPoolDS = new SharedPoolDataSource();
    sharedPoolDS.setConnectionPoolDataSource(driver);
    sharedPoolDS.setMaxActive(10);
    sharedPoolDS.setMaxWait(50);
    sharedPoolDS.setTestOnBorrow(true);
    sharedPoolDS.setValidationQuery("SELECT 1");
    sharedPoolDS.setTestWhileIdle(true);
    ds = sharedPoolDS;
    
    return ds;
  }
  
  public Connection getConnection() 
			throws ClassNotFoundException, SQLException {
			
    DataSource ds = getDataSource();
    
    return ds.getConnection(); 
  }
}

Creating the Shared Pool DataSource for DB2

package com.avaldes.util;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS;
import org.apache.commons.dbcp.datasources.SharedPoolDataSource;

public class ApacheDB2DataSource {
  
  private DataSource getDataSource() throws ClassNotFoundException {
    DataSource ds;
    
    DriverAdapterCPDS driver = new DriverAdapterCPDS();
    
    driver.setDriver("com.ibm.as400.access.AS400JDBCDriver");
    driver.setUrl("jdbc:as400://devAS400.avaldes.com:50000/TUTORIAL");
    driver.setUser("webuser");
    driver.setPassword("webuser");
    
    SharedPoolDataSource sharedPoolDS = new SharedPoolDataSource();
    sharedPoolDS.setConnectionPoolDataSource(driver);
    sharedPoolDS.setMaxActive(10);
    sharedPoolDS.setMaxWait(50);
    sharedPoolDS.setTestOnBorrow(true);
    sharedPoolDS.setValidationQuery("SELECT 1 FROM sysibm.sysdummy1");
    sharedPoolDS.setTestWhileIdle(true);
    ds = sharedPoolDS;
    
    return ds;
  }
  
  public Connection getConnection() throws 
			ClassNotFoundException, SQLException {
			
    DataSource ds = getDataSource();
    
    return ds.getConnection(); 
  }
}

ApacheDBCPPojoExample Application (ApacheDBCPPojoExample.java)

package com.avaldes.tutorial;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.codehaus.jackson.JsonGenerationException;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;

import com.avaldes.model.Customer;
import com.avaldes.util.ApacheDB2DataSource;
import com.avaldes.util.ApacheH2DataSource;
import com.avaldes.util.ApacheMySQLDataSource;

public class ApacheDBCPPojoExample {
  public static void main(String[] args) throws JsonGenerationException, 
    JsonMappingException, ClassNotFoundException, SQLException, IOException {
    
    System.out.println("Starting ApacheDBCPPojoExample Application...");
    
    testMySQLConnection();
    testH2Connection();
    testDB2Connection();
  }
  
  public static void testMySQLConnection() 
      throws ClassNotFoundException, SQLException, 
        JsonGenerationException, JsonMappingException, IOException {
  
    ApacheMySQLDataSource ds = new ApacheMySQLDataSource();
    
    Connection con = ds.getConnection();
    
    testDBConnection(con, "Testing MySQL Connectivity...");
  }
  
  public static void testH2Connection() throws 
		ClassNotFoundException, SQLException,JsonGenerationException, 
																JsonMappingException, IOException {
  
    ApacheH2DataSource ds = new ApacheH2DataSource();
    
    Connection con = ds.getConnection();

    testDBConnection(con, "Testing H2 Connectivity...");

  }
  
  public static void testDB2Connection() throws 
		ClassNotFoundException, SQLException,JsonGenerationException, 
																JsonMappingException, IOException {
    
    ApacheDB2DataSource ds = new ApacheDB2DataSource();

    Connection con = ds.getConnection();
    
    testDBConnection(con, "Testing DB2 Connectivity...");
  }
  
  private static void testDBConnection(Connection con, String msg) 
			throws SQLException, JsonGenerationException, 
																JsonMappingException, IOException {
    
    System.out.println(msg);
    
    String sql = "select customer_id, first_name, last_name, " 
        + "address, city, state, zip_code, is_active from Customers";
    
    List<Customer> allCustomers = new ArrayList<Customer>();
    
    PreparedStatement ps = con.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
    
    while (rs.next()) {
      Customer cust = new Customer();
      cust.setCustomerId(rs.getInt("customer_id"));
      cust.setFirstName(rs.getString("first_name"));
      cust.setLastName(rs.getString("last_name"));
      cust.setAddress(rs.getString("address"));
      cust.setCity(rs.getString("city"));
      cust.setState(rs.getString("state"));
      cust.setZipCode(rs.getString("zip_code"));
      cust.setIsActive(rs.getInt("is_active"));
      allCustomers.add(cust);
    }
    
    ObjectMapper mapper = new ObjectMapper();
    String jsonString = mapper.writeValueAsString(allCustomers);
    
    System.out.println("Customers:  " + jsonString);
  }
}

Creating the Customer Table in MySQL

CREATE TABLE `tutorialdb`.`customers` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  `city` VARCHAR(30) NULL,
  `state` VARCHAR(20) NULL,
  `zip_code` VARCHAR(10) NULL,
  `is_active` TINYINT(1) NULL,
  PRIMARY KEY (`customer_id`)
);

Inserting sample data into Customer Table in MySQL

I have included a sample insert statement using SQL to ease you database work and makeit easier for you to get started sooner.

INSERT INTO `tutorialdb`.`customers`
  (`first_name`, `last_name`, `address`, `city`, `state`, 
		`zip_code`, `is_active`)
VALUES
  ('Melanie', 'Jones', '871 Maple Drive', 'Newark', 'New Jersey', 
		'07272', 1);

INSERT INTO `tutorialdb`.`customers`
  (`first_name`, `last_name`, `address`, `city`, `state`, 
		`zip_code`, `is_active`)
VALUES
  ('Greg', 'Strupp', '178 McNair Place', 'Paterson', 'New Jersey', 
		'01822', 1);

Creating the Customer Table in DB2

CREATE TABLE TUTORIAL.CUSTOMERS ( 
	CUSTOMER_ID FOR COLUMN CUSTO00001 INTEGER 
	GENERATED ALWAYS AS IDENTITY ( 
	START WITH 1 INCREMENT BY 1
	CACHE 20 ) , 
	FIRST_NAME VARCHAR(45) CCSID 37 DEFAULT NULL , 
	LAST_NAME VARCHAR(45) CCSID 37 DEFAULT NULL , 
	ADDRESS VARCHAR(45) CCSID 37 DEFAULT NULL , 
	CITY VARCHAR(30) CCSID 37 DEFAULT NULL , 
	STATE VARCHAR(20) CCSID 37 DEFAULT NULL , 
	ZIP_CODE VARCHAR(10) CCSID 37 DEFAULT NULL , 
	IS_ACTIVE NUMERIC(1, 0) DEFAULT NULL , 
	CONSTRAINT TUTORIAL.PK_CUSTOMERS PRIMARY KEY( CUSTOMER_ID ) )   
	; 
  
GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , 
SELECT , UPDATE ON TUTORIAL.CUSTOMERS TO TUTORIAL 
WITH GRANT OPTION ;

GRANT DELETE , INSERT , SELECT , UPDATE   
ON TUTORIAL.CUSTOMERS TO PUBLIC ;

Inserting sample data into Customer Table in DB2

I have included a sample insert statement using SQL to ease you database work and make it easier for you to get started sooner.

INSERT INTO TUTORIAL.CUSTOMERS
  (FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP_CODE, IS_ACTIVE) 
VALUES
  ('Albert', 'Peters', '1888 Hillsview Drive', 'Florence', 'New Jersey',
   '08172', 1);

INSERT INTO TUTORIAL.CUSTOMERS
  (FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP_CODE, IS_ACTIVE) 
VALUES
  ('Harry', 'Spellman', '892 Lance Road', 'Kingston', 'New Jersey',
   '08333', 1);

Creating the Customer Table in H2 Database

CREATE TABLE CUSTOMERS (
  customer_id INTEGER(10) NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NULL,
  last_name VARCHAR(45) NULL,
  address VARCHAR(45) NULL,
  city VARCHAR(30) NULL,
  state VARCHAR(20) NULL,
  zip_code VARCHAR(10) NULL,
  is_active TINYINT(1) NULL,
  PRIMARY KEY (customer_id)
);

Inserting sample data into Customer Table in H2

I have included a sample insert statement using SQL to ease you database work and make it easier for you to get started sooner.

INSERT INTO CUSTOMERS
  (FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP_CODE, IS_ACTIVE) 
VALUES
	('James', 'Mooney', '1829 Randolf Street', 'Wayne', 'New Jersey', 
   '07470', 1);
	
INSERT INTO CUSTOMERS
  (FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP_CODE, IS_ACTIVE)
VALUES
	('Jessica', 'Silva', '1285 South Street', 'Clifton', 'New Jersey', 
   '07013', 1);	

Testing out our ApacheDBCPPojoExample Application

Starting ApacheDBCPPojoExample Application...

Testing MySQL Connectivity...
Customers:  [{"address":"871 Maple Drive","city":"Newark","state":
"New Jersey","customer-id":5,"first-name":"Melanie","last-name":
"Jones","zip-code":"07272","is-active":1},{"address":"178 McNair Place",
"city":"Paterson","state":"New Jersey","customer-id":6,"first-name":
"Greg","last-name":"Strupp","zip-code":"01822","is-active":1}]

Testing H2 Connectivity...
Customers:  [{"address":"1829 Randolf Street","city":"Wayne","state":
"New Jersey","customer-id":1,"first-name":"James","last-name":"Mooney",
"zip-code":"07470","is-active":1},{"address":"1285 South Street",
"city":"Clifton","state":"New Jersey","customer-id":2,"first-name":
"Jessica","last-name":"Silva","zip-code":"07013","is-active":1}]

Testing DB2 Connectivity...
Customers:  [{"address":"1888 Hillsview Drive","city":"Florence","state":
"New Jersey","customer-id":1,"first-name":"Albert","last-name":"Peters",
"zip-code":"08172","is-active":1},{"address":"892 Lance Road","city":
"Kingston","state":"New Jersey","customer-id":2,"first-name":"Harry",
"last-name":"Spellman","zip-code":"08333","is-active":1}]

Download the Complete Source Code

Download Datasource Example using Apache DBCP with H2, MySQL and DB2

1 file(s) 7.87 MB

Download

That’s It!

I hope you enjoyed this tutorial. It was certainly a lot of fun putting it together and testing it out. Please continue to share the love and like us so that we can continue bringing you quality tutorials. Happy Coding!!!

Related Spring Posts

• Creating Hello World Application using Spring MVC on Eclipse IDE
  In this tutorial we will go into some detail on how to set up your Eclipse IDE environment so that you can develop Spring MVC projects. In this post, we will create our first Spring MVC project with the all to familiar “Hello World” sample program.
• Spring MVC Form Handling Example
  The following tutorial will guide you on writing a simple web based application which makes use of forms using Spring Web MVC framework. With this web application you will be able to interact with the customer entry form and enter all of the required values and submit them to the backend processes. I have taken the liberty of using CSS to beautify and transform the HTML page from a standard drab look and feel to a more appealing view.
• Spring @RequestHeader Annotation Example
  In this tutorial, we will discuss the different ways that Spring MVC allow us to access HTTP headers using annotation. We will discuss how to access individual header fields from the request object as well accessing all the headers by supplying Map and then iterating through the LinkedHashMap collection. We will also show you how to set the headers in the response object.
• Spring MVC Exception Handling using @ExceptionHandler with AngularJS GUI
  Good exception handling is a essential part of any well developed Application Framework and Spring MVC is no exception — pardon the pun. Spring MVC provides several different ways to handle exceptions in our applications. In this tutorial, we will cover Controller Based Exception Handling using the @ExceptionHandler annotation above the method that will handle it.
• Spring RESTful Web Service Example with JSON and Jackson using Spring Tool Suite
  For this example, I will be using Spring Tool Suite (STS) as it is the best integrated development environment for building the Spring framework projects. Spring is today's leading framework for building Java, Enterprise Edition (Java EE) applications. One additional feature that makes Spring MVC so appealing is that it now also supports REST (REpresentational State Transfer) for build Web Services.
• Spring MVC RESTful Web Service Example with Spring Data for MongoDB and ExtJS GUI
  This post will show another example of how to build a RESTful web service using Spring MVC 4.0.6, Spring Data for MongoDB 1.6.1 so that we can integrate the web application with a highly efficient datastore (MongoDB 2.6). In this tutorial we will walk you through building the web service and NoSQL database backend and show you how to implement CRUD (Create, Read, Update and Delete) operations.
• Building DHTMLX Grid Panel User Interface with Spring MVC Rest and MongoDB Backend
  In this tutorial we will show how easy it is to use DHTMLX dhtmlxGrid component while loading JSON data with Ajax pulling in data from the Spring MVC REST web service from our MongoDB data source. You will see how simple it is to create a visually appealing experience for your client(s) with minimal javascript coding.
• Spring MVC with JNDI Datasource for DB2 on AS/400 using Tomcat
  In this tutorial we will discuss how to set up Spring MVC web services and configure a JNDI Datasource using Tomcat and connect to IBM DB2 Database on a AS/400. JNDI (Java Naming and Directory Interface) provides and interface to multiple naming and directory services.
• Java Spring MVC Email Example using Apache Velocity
  In this tutorial we will discuss how to set up a Java Spring MVC RESTful Webservice with Email using Apache Velocity to create a Velocity template that is used to create an HTML email message and embed an image, as shown below, using MIME Multipart Message.
• Implementing Basic and Advanced Search using Angular Material Design, Grid-UI, Spring MVC REST API and MongoDB Example
  In this tutorial we will discuss how to implement basic and advanced search techniques in MongoDB using AngularJS and Google’s Material Design with Spring MVC REST API backend. The advanced search user interface (UI) will use logical operators and build a JSON object which contains the search field name, boolean or logical operator and the search value.
• Spring MVC Interceptor using HandlerInterceptorAdapter Example
  In this tutorial we will discuss how to use the HandlerInterceptorAdapter abstract class to create a Spring MVC interceptor. These interceptors are used to apply some type of processing to the requests either before, after or after the complete request has finished executing.

Please Share Us on Social Media