JAVA EE: JDBC|Servlets : BoneCP Connection Pooling

Tuesday 23 September 2014

JDBC|Servlets : BoneCP Connection Pooling - Mysql- Tomcat

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>