DBCP Connection Pooling Example

DBCP Connection Pooling Example exaplains about how to create and configure a Connection pool using DBCP Datasource

Many Apache projects support interaction with a relational database, DBCP one among them.

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 unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large. 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. The application itself logs into the DBMS, and handles any user account issues internally

Reference -> http://commons.apache.org/proper/commons-dbcp/

Note

Different Connection Pool Implementation examples

1)    DBCP Connection Pooling Example
2)    C3P0 Connection Pooling Example
3)    BoneCP Connection Pooling Example
4)    H2 Database Connection Pool Example
Required Libraries

You need to download

  1. DBCP

Following jar must be in classpath

  1. commons-dbcp.jar
  2. commons-pool.jar
  3. mysql-connector-java-5.1.28-bin.jar

Create Table Structure

CREATE TABLE `employee` (
  `EMPLOYEEID` bigint(20) NOT NULL AUTO_INCREMENT,
  `EMPLOYEENAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEEID`)
);

Project Structure

DBCP Connection Pooling Example

DataSource.java

package com.dataSource.dbcp;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

public class DataSource {

   
private static DataSource     datasource;
   
private BasicDataSource ds;

   
private DataSource() throws IOException, SQLException, PropertyVetoException {
       
ds = new BasicDataSource();
        ds.setDriverClassName
("com.mysql.jdbc.Driver");
        ds.setUsername
("root");
        ds.setPassword
("root");
        ds.setUrl
("jdbc:mysql://localhost/test");
       
    
// the settings below are optional -- dbcp can work with defaults
       
ds.setMinIdle(5);
        ds.setMaxIdle
(20);
        ds.setMaxOpenPreparedStatements
(180);

   
}

   
public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
       
if (datasource == null) {
           
datasource = new DataSource();
           
return datasource;
       
} else {
           
return datasource;
       
}
    }

   
public Connection getConnection() throws SQLException {
       
return this.ds.getConnection();
   
}

}
1 2 Next





Your email address will not be published. Required fields are marked *