DBCP Connection Pooling Example

By admin | December 24, 2013 | Updated : April 28, 2016 connectionpool, database, dbcp, mysql, 1 Comments

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

DBCP

Following jar must be in classpath

commons-dbcp.jar
commons-pool.jar
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();
    }

}

DBCPDataSourceExample.java

package com.dataSource.dbcp;

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

public class DBCPDataSourceExample {

    public static void main(String[] args) throws PropertyVetoException, SQLException, IOException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DataSource.getInstance().getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from employee");
             while (resultSet.next()) {
                 System.out.println("employeeid: " + resultSet.getString("employeeid"));
                 System.out.println("employeename: " + resultSet.getString("employeename"));
             }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) try { resultSet.close(); } catch (SQLException e) {e.printStackTrace();}
            if (statement != null) try { statement.close(); } catch (SQLException e) {e.printStackTrace();}
            if (connection != null) try { connection.close(); } catch (SQLException e) {e.printStackTrace();}
        }

    }

}

Output

employeeId: 1
employeename: Rockey
employeeId: 2
employeename: Jose

You might also like following tutorials :

1 Responses to "DBCP Connection Pooling Example"

Chad Paul 2018-02-09 19:33:24.0
```
Wow, this is great
```