JAVA EE: JDBC|Servlets : BoneCP Connection Pooling - Oracle

Saturday, 13 September 2014

JDBC|Servlets : BoneCP Connection Pooling - Oracle - Tomcat

Master Core Java, JDBC & Servlets!

Subscribe to Ram N Java for beginner-friendly coding walkthroughs, database connection pooling demos, and practical backend architecture guides!

🔔 Subscribe to Ram N Java

What is BoneCP Connection Pooling with Oracle?

In production Java web applications connected to an Oracle Database, creating a fresh physical database connection for every incoming user request introduces heavy network latency and severe CPU overhead. Connection Pooling solves this bottleneck by managing a pool of pre-established, reusable connections.

BoneCP is a high-speed, open-source JDBC connection pool library engineered for maximum performance. Utilizing a partitioned concurrency design and lock-free algorithms, BoneCP delivers superior throughput compared to legacy pooling frameworks when connecting servlets to an Oracle database running under Apache Tomcat.

Setting Up BoneCP with Oracle and Apache Tomcat

Setting up BoneCP with an Oracle database in Apache Tomcat involves configuring the pool parameters and loading the required database driver:

Dependencies: Ensure bonecp.jar, slf4j-api.jar, guava.jar, and the Oracle JDBC driver (such as ojdbc.jar) are placed in Tomcat's lib directory or application classpath.
Driver & URL: Use the Oracle thin driver (oracle.jdbc.driver.OracleDriver) and point to your Oracle listener URL (e.g., jdbc:oracle:thin:@localhost:1521:xe).
Pool Sizing & Partitioning: Adjust pool configuration properties like partitionCount, minConnectionsPerPartition, and maxConnectionsPerPartition to tune throughput for your database workload.

// Initialize BoneCP Configuration for Oracle

    BoneCPConfig config = new BoneCPConfig();

    config.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:xe");

    config.setUsername("system");

    config.setPassword("oracle");

    config.setMinConnectionsPerPartition(5);

    config.setMaxConnectionsPerPartition(10);

    config.setPartitionCount(1);


    // Setup BoneCP Connection Pool and borrow connection

    BoneCP connectionPool = new BoneCP(config);

    try (Connection connection = connectionPool.getConnection()) {

        System.out.println("BoneCP Oracle Connection acquired successfully!");

    }

Key Advantages of BoneCP with Oracle

Lock-Free Concurrency: Partitioning minimizes thread contention, allowing high concurrent request volume without blocking.
Fast Connection Acquisition: Eliminates database handshake delays by reusing existing open Oracle connections.
Resource Protection: Enforces controlled connection ceilings, safeguarding the Oracle database against thread exhaustion.

Recommended Related Tutorials

Continue exploring JDBC connection pooling and Tomcat integration with these tutorials from Ram N Java:

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

Click the below Image to Enlarge

BoneCP Connection Pooling

ConnectionManager.java

import com.jolbox.bonecp.BoneCPConfig;

public class ConnectionManager
{

    private static com.jolbox.bonecp.BoneCP boneCPConnectionPool = null;

    public static com.jolbox.bonecp.BoneCP getConnectionPool()
    {
        return boneCPConnectionPool;
    }

    public static void setConnectionPool(com.jolbox.bonecp.BoneCP connectionPool)
    {
        ConnectionManager.boneCPConnectionPool = connectionPool;
    }

    public static void configureBoneCPConnectionPool()
    {
        try
        {
            /*
             * load the database driver (make sure this is in your classpath!)
             */
            Class.forName("oracle.jdbc.OracleDriver");

            /*
             * setup the connection pool
             */

            BoneCPConfig boneCPConfig = new BoneCPConfig();
            boneCPConfig.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:xe");
            boneCPConfig.setUsername("hr");
            boneCPConfig.setPassword("hr");
            boneCPConfig.setMinConnectionsPerPartition(5);
            boneCPConfig.setMaxConnectionsPerPartition(10);
            boneCPConfig.setPartitionCount(1);
            boneCPConnectionPool = new com.jolbox.bonecp.BoneCP(boneCPConfig);

            setConnectionPool(boneCPConnectionPool);

            System.out
                    .println("contextInitialized.....Connection Pooling is configured");

        }
        catch (Exception exe)
        {
            exe.printStackTrace();
        }

    }

    public static void shutdownBoneCPConnectionPool()
    {

        try
        {
            com.jolbox.bonecp.BoneCP connectionPool = ConnectionManager
                    .getConnectionPool();
            if (connectionPool != null)
            {
                /*
                 * This method must be called only once when the application
                 * stops. you don't need to call it every time when you get a
                 * connection from the Connection Pool
                 */

                connectionPool.shutdown();
                System.out
                        .println("contextDestroyed.....Connection Pooling shut downed!");
            }

        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

}

ContextListener.java

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

public class ContextListener implements ServletContextListener
{

    @Override
    public void contextInitialized( ServletContextEvent arg0 )
    {
        System.out.println("\n---------------------------------------------");
        System.out.println("ContextInitialized Method has been Called......");
        ConnectionManager.configureBoneCPConnectionPool();
        System.out.println("---------------------------------------------\n");

    }

    @Override
    public void contextDestroyed( ServletContextEvent arg0 )
    {
        System.out.println("\n---------------------------------------------");
        System.out.println("contextDestroyed Method has been Called......");
        ConnectionManager.shutdownBoneCPConnectionPool();
        System.out.println("---------------------------------------------\n");

    }

}

EmployeeInfoServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class EmployeeInfoServlet extends HttpServlet
{

    private static final long serialVersionUID = 1L;

    com.jolbox.bonecp.BoneCP boneCPConnectionPool = null;

    public void init(ServletConfig config)
    {
        try
        {

            System.out
                    .println("-----------------------------------------------------");
            System.out
                    .println("init method has been called and servlet is initialized");

            boneCPConnectionPool = ConnectionManager.getConnectionPool();

            System.out.println("Got BoneCPConnectionPool : "
                    + boneCPConnectionPool);

            System.out
                    .println("-----------------------------------------------------");
        }

        catch (Exception exe)
        {
            exe.printStackTrace();
        }

    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException
    {

        System.out
                .println("-----------------------------------------------------");
        System.out.println("doGet method has been called");
        System.out
                .println("-----------------------------------------------------");

        response.setContentType("text/html");

        PrintWriter out = response.getWriter();
        String title = "Employee Information From Oracle Database";
        out.print("<html><body bgcolor=\"#f0f0f0\">");
        out.print("<h1 align=\"center\">" + title + "</h1>\n");

        showEmployeeInformation(out);

        out.print("</body></html>");
    }

    public void destroy()
    {
        System.out
                .println("-----------------------------------------------------");
        System.out
                .println("destroy method has been called and servlet is destroyed");

        System.out
                .println("-----------------------------------------------------");
    }

    private void showEmployeeInformation(PrintWriter out)
    {
        Connection connection = null;
        Statement stmt = null;
        try
        {

            /*
             * Get connection from the boneCPConnectionPool
             */

            connection = boneCPConnectionPool.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
                 */
                out.print("employee_id: " + id + "<br>");
                out.print("first_name: " + firstName + "<br>");
                out.print("last_name: " + lastName + "<br>");
                out.print("email: " + email + "<br>");
                out.println("phone_number: " + phoneNumber + "<br>");
                out.println("-------------------------------------------"
                        + "<br>");
            }
            rs.close();

        }

        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();
            }
        }

    }

}

web.xml

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://java.sun.com/xml/ns/javaee"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    metadata-complete="true" version="3.0">
    <display-name>EmployeeInfoDemo</display-name>
    <description>
        This is a simple web application with a source code organization
        based on the recommendations of the Application Developer's Guide.
    </description>
    <servlet>
        <servlet-name>employeeInfoServlet</servlet-name>
        <servlet-class>EmployeeInfoServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>employeeInfoServlet</servlet-name>
        <url-pattern>/employeeInfo</url-pattern>
    </servlet-mapping>

    <listener>
        <listener-class>ContextListener</listener-class>
    </listener>

</web-app>

index.html

<!DOCTYPE HTML><html lang="en"><head>
<meta charset="UTF-8">
<title>EmployeeInfoServlet</title>
</head>
<body>
<p>
<h3>EmployeeInfoServlet</H3>
<p></p>
<ul>

<li><a href="employeeInfo">Show Employee Information</a></li>

</ul>
</body></html>