JAVA EE: JDBC|Servlets : BoneCP Connection Pooling

Tuesday, 23 September 2014

JDBC|Servlets : BoneCP Connection Pooling - Mysql- 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?

In high-traffic Java enterprise applications, establishing a new physical database connection for every incoming HTTP request is an expensive operation that degrades application throughput. Connection Pooling addresses this challenge by maintaining a pool of pre-created, reusable database connections.

BoneCP is a lightning-fast, open-source Java JDBC connection pool library engineered for maximum performance. Built around a lock-free, partitioned concurrency architecture, BoneCP significantly minimizes thread contention compared to older pooling implementations, making it an excellent choice for production servlet applications connecting to MySQL.

Setting Up BoneCP with MySQL and Tomcat

Integrating BoneCP within an Apache Tomcat web application involves setting up the BoneCP libraries, adding the MySQL JDBC driver, and configuring the pool settings:

Dependencies: Ensure bonecp.jar, slf4j-api.jar, guava.jar, and the MySQL Connector JAR are included in the runtime classpath or Tomcat's lib directory.
Pool Configuration: Define core connection settings including the JDBC URL, driver class, credentials, and tuning parameters such as partitionCount and maxConnectionsPerPartition.
Acquiring Connections: Borrow a connection directly from the BoneCPDataSource instance inside your servlet, execute SQL queries, and close the connection to return it back to the pool.

// Initialize BoneCP Configuration

    BoneCPConfig config = new BoneCPConfig();

    config.setJdbcUrl("jdbc:mysql://localhost:3306/testdb");

    config.setUsername("root");

    config.setPassword("password");

    config.setMinConnectionsPerPartition(5);

    config.setMaxConnectionsPerPartition(10);

    config.setPartitionCount(1);


    // Setup connection pool and acquire connection

    BoneCP connectionPool = new BoneCP(config);

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

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

    }

Key Advantages of Using BoneCP

Optimized Concurrency: Partitioning minimizes lock bottlenecks when multiple threads request connections simultaneously.
Reduced Latency: Eliminates the costly handshake and network overhead of opening and terminating physical database connections repeatedly.
Resource Safety: Protects your MySQL database against overload by enforcing configurable thresholds on maximum active connections.

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=ETF12Oc9E_I

Click the below Image to Enlarge

JDBC|Servlets : BoneCP Connection Pooling - Mysql- Tomcat

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("com.mysql.jdbc.Driver");

            /*
             * setup the connection pool
             */

            BoneCPConfig boneCPConfig = new BoneCPConfig();
            boneCPConfig.setJdbcUrl("jdbc:mysql://localhost:3306/world");
            boneCPConfig.setUsername("root");
            boneCPConfig.setPassword("root");
            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");

    }

}

CityInfoServlet.java

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

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 CityInfoServlet 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 = "City Information From Mysql Database";
        out.print("<html><body bgcolor=\"#f0f0f0\">");
        out.print("<h1 align=\"center\">" + title + "</h1>\n");

        showCityInformation(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 showCityInformation( PrintWriter out )
    {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try
        {

            String sql = "select * from city limit ?";

            /*
             * Get connection from the DataSource
             */

            connection = boneCPConnectionPool.getConnection();

            /*
             * Execute the query
             */
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 10);

            ResultSet rs = preparedStatement.executeQuery();

            while( rs.next() )
            {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String countryCode = rs.getString(3);
                String district = rs.getString(4);
                int population = rs.getInt(5);

                /*
                 * Display values
                 */
                out.print("ID: " + id + "<br>");
                out.print("Name: " + name + "<br>");
                out.print("CountryCode: " + countryCode + "<br>");
                out.print("District: " + district + "<br>");
                out.println("Population: " + population + "<br>");
                out.println("--------------------------------------" + "<br>");
            }

            rs.close();

        }

        catch( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if( preparedStatement != null )
                {
                    preparedStatement.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>City Info Demo</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>cityInfoServlet</servlet-name>
        <servlet-class>CityInfoServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>cityInfoServlet</servlet-name>
        <url-pattern>/cityInfoServlet</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>Show City Information</title>
</head>
<body>
<p>
<h3>Show City Information</H3>
<p></p>
<ul>

<li><a href="cityInfoServlet">Show City Information</a></li>

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