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

Saturday 13 September 2014

JDBC|Servlets : BoneCP Connection Pooling - Oracle - Tomcat

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>