JAVA EE: JDBC DBCP DataSource

Friday, 12 September 2014

JDBC DBCP DataSource - Oracle

🚀 Master Java database programming, connection pooling, and Oracle JDBC!

What Is Apache Commons DBCP DataSource?

Opening and closing physical database connections directly via DriverManager for every client request creates significant CPU and network overhead. In enterprise applications, connection pooling is used to manage and reuse a pool of active database connections.

Apache Commons DBCP (Database Connection Pool) provides the BasicDataSource class (from org.apache.commons.dbcp), which implements javax.sql.DataSource. It encapsulates database credentials and connection parameters, efficiently maintaining a pool of pre-established connections for reuse.

Required Dependencies

To use DBCP connection pooling in your Java project, ensure the following JAR files are present on your classpath:

commons-dbcp.jar
commons-pool.jar
ojdbc.jar (Oracle JDBC Driver)

Complete Java Source Code Example

The following Java program creates and configures a BasicDataSource, gets a connection from the pool, queries the Oracle EMPLOYEES table in the HR schema, and displays employee records:

package com.ram.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;

public class DBCPDataSourceDemo {

    static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
    static final String USER = "hr";
    static final String PASS = "hr";

    // Method to create and configure the BasicDataSource instance
    public static DataSource getDataSource() {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(JDBC_DRIVER);
        ds.setUrl(DB_URL);
        ds.setUsername(USER);
        ds.setPassword(PASS);
        return ds;
    }

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // Obtain DataSource
            DataSource dataSource = getDataSource();

            // Borrow connection from DBCP pool
            conn = dataSource.getConnection();

            stmt = conn.createStatement();
            String sql = "SELECT employee_id, first_name, last_name, email, phone_number FROM employees";
            rs = stmt.executeQuery(sql);

            System.out.println("ID | Name | Email | Phone");
            System.out.println("----------------------------------------");
            while (rs.next()) {
                int empId = rs.getInt("employee_id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");
                String email = rs.getString("email");
                String phone = rs.getString("phone_number");

                System.out.println(empId + " | " + firstName + " " + lastName + " | " + email + " | " + phone);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Clean up resources (closing connection returns it back to the pool)
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}

Key Steps & Best Practices

Configure BasicDataSource: Set the driver class name, JDBC URL, username, and password via setter methods on BasicDataSource.
Program to Interfaces: Keep the return type as standard javax.sql.DataSource to decouple application code from specific connection pooling libraries.
Connection Recycling: Calling conn.close() on a pooled connection does not terminate the physical database link; it returns the connection back to the DBCP pool for reuse.
Always ensure ResultSet, Statement, and Connection are closed in a finally block to avoid connection starvation in the pool.

Related Tutorials You Might Enjoy

Click here to watch in Youtube : https://www.youtube.com/watch?v=s_h54qI0npE

Click the below Image to Enlarge

JDBC DBCP DataSource - Oracle

DbcpBasicDataSource.java

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;

public class DbcpBasicDataSource
{

    public static DataSource getDbcpBasicDataSource()
    {

        BasicDataSource basicDataSource = new BasicDataSource();
        try
        {
            basicDataSource.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
            basicDataSource.setUsername("hr");
            basicDataSource.setPassword("hr");
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }
        return basicDataSource;
    }

    
}

JDBCDataSourceDemo.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

public class JDBCDataSourceDemo
{

    public static void main( String[] args )
    {
        JDBCDataSourceDemo jdbcDataSourceDemo = new JDBCDataSourceDemo();
        jdbcDataSourceDemo.getEmployeeInformation();

    }

    private void getEmployeeInformation()
    {
        Connection connection = null;
        Statement stmt = null;
        /*
         * Get the dbcp2 BasicDataSource
         */
        DataSource dataSource = DbcpBasicDataSource.getDbcpBasicDataSource();
        try
        {
            /*
             *  Get connection from the DataSource
             */
            connection = dataSource.getConnection();
            
            /*
             *  Execute the Query
             */
            
            stmt = connection.createStatement();
            String sql = "select employee_id,first_name,last_name,email,phone_number from employees";
            ResultSet rs = stmt.executeQuery(sql);

            /*
             *  Iterate the ResultSet and get each row
             *  Information.
             */
            while( rs.next() )
            {
                /*
                 * Retrieve by column name
                 */
                int id = rs.getInt("employee_id");
                String firstName = rs.getString("first_name");
                String lastName = rs.getString("last_name");
                String email = rs.getString("email");
                String phoneNumber = rs.getString("phone_number");

                /*
                 * Display values
                 */
                System.out.print("employee_id: " + id);
                System.out.print(", first_name: " + firstName);
                System.out.print(", last_name: " + lastName);
                System.out.print(", email: " + email);
                System.out.println(", phone_number: " + phoneNumber);
            }
            rs.close();     
        }
        catch( SQLException se )
        {
            se.printStackTrace();
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if( stmt != null )
                {
                    stmt.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }
            try
            {
                if( connection != null )
                {
                    connection.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }

        }

    }
}

Output

employee_id: 100, first_name: Steven, last_name: King, email: SKING, phone_number: 515.123.4567
employee_id: 101, first_name: Neena, last_name: Kochhar, email: NKOCHHAR, phone_number: 515.123.4568
employee_id: 102, first_name: Lex, last_name: De Haan, email: LDEHAAN, phone_number: 515.123.4569
employee_id: 103, first_name: Alexander, last_name: Hunold, email: AHUNOLD, phone_number: 590.423.4567
employee_id: 104, first_name: Bruce, last_name: Ernst, email: BERNST, phone_number: 590.423.4568
employee_id: 105, first_name: David, last_name: Austin, email: DAUSTIN, phone_number: 590.423.4569
employee_id: 106, first_name: Valli, last_name: Pataballa, email: VPATABAL, phone_number: 590.423.4560
employee_id: 107, first_name: Diana, last_name: Lorentz, email: DLORENTZ, phone_number: 590.423.5567
employee_id: 108, first_name: Nancy, last_name: Greenberg, email: NGREENBE, phone_number: 515.124.4569

---
---