JAVA EE: April 2015

Thursday, 30 April 2015

JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql

🚀 Master Java, JDBC & Web Technologies!

Subscribe to Ram N Java for in-depth, beginner-friendly coding tutorials and architecture guides!

Why Use C3P0 Connection Pooling with MySQL?

Creating database connections on every user request leads to high latency and heavy memory usage. C3P0 solves this problem by maintaining a pool of pre-established database connections. Instead of opening a brand-new connection each time, your Java Servlet borrows an existing connection from the pool, runs the SQL queries, and releases it back to the pool immediately.

Step 1: Add Required Libraries to Tomcat

Make sure the following JAR files are placed inside the Apache Tomcat/lib folder:

c3p0.jar & mchange-commons-java.jar: The core C3P0 connection pooling binaries.
mysql-connector-java.jar: The MySQL JDBC driver to allow Tomcat to communicate with your MySQL server.

Step 2: Configure C3P0 DataSource in context.xml

Define the MySQL connection pool resource inside your Tomcat configuration (META-INF/context.xml or conf/context.xml):

    <Resource name="jdbc/mysqldb"

      auth="Container"

      type="com.mchange.v2.c3p0.ComboPooledDataSource"

      factory="org.apache.naming.factory.BeanFactory"

      driverClass="com.mysql.jdbc.Driver"

      jdbcUrl="jdbc:mysql://localhost:3306/testdb"

      user="root"

      password="root"

      maxPoolSize="50"

      minPoolSize="10"

      acquireIncrement="5" />

Step 3: Declare the Resource Reference in web.xml

Inform your web application about the external DataSource resource in your web.xml deployment descriptor:

    <resource-ref>

      <description>MySQL Connection Pool</description>

      <res-ref-name>jdbc/mysqldb</res-ref-name>

      <res-type>javax.sql.DataSource</res-type>

      <res-auth>Container</res-auth>

    </resource-ref>

Step 4: Perform JNDI Lookup and Query Data in Servlet

Inside your servlet, look up the DataSource using JNDI and fetch records from MySQL:

    Context initContext = new InitialContext();

    Context envContext = (Context) initContext.lookup("java:comp/env");

    DataSource ds = (DataSource) envContext.lookup("jdbc/mysqldb");

    try (Connection conn = ds.getConnection();

         Statement stmt = conn.createStatement();

         ResultSet rs = stmt.executeQuery("SELECT id, username, email FROM users")) {

      while (rs.next()) {

        out.println(rs.getInt("id") + " : " + rs.getString("username"));

      }

    }

📺 Related MySQL & Connection Pooling Tutorials

Check out these related practical guides from our channel:

Click here to watch in Youtube : https://www.youtube.com/watch?v=5gSn9zt5qrA&list=UUhwKlOVR041tngjerWxVccw&index=2

Click the below Image to Enlarge

JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql

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.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

public class CityInfoServlet extends HttpServlet
{

    private static final long serialVersionUID = 1L;

    DataSource dataSource = null;
    
    public void init( ServletConfig config )
    {
        try
        {

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

            /*
             *  Using JDNI lookup get the DataSource.
             */
            
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            dataSource = (DataSource) envContext.lookup("jdbc/worldDB");
            
            System.out.println("Using JDNI lookup got the DataSource : "+ dataSource);

            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 = dataSource.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>/cityInfo</url-pattern>
    </servlet-mapping>

    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/worldDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

</web-app>

index.html

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

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

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

To Download CityInfoDemoTomcatC3POMysqlApp Project Click the below link

https://drive.google.com/file/d/1-oMKh79zw9FBJPsxa34GgX50raN1k12Z/view?usp=drive_web

External Links:

Download the latest c3p0-{version}.jar from : http://sourceforge.net/projects/c3p0/

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql

🚀 Master Java, JDBC & Web Technologies!

Subscribe to Ram N Java for clean, beginner-friendly coding tutorials and architecture guides!

👉 Click Here to Subscribe Now

What is Apache Commons DBCP in Tomcat?

Apache Commons DBCP (Database Connection Pooling) is the default connection pooling implementation packaged with Apache Tomcat. Instead of creating and tearing down database connections on every client request, DBCP manages a pool of active connections. Servlets can quickly borrow, utilize, and return connections to the pool, drastically improving web application performance and throughput.

Step 1: Add the MySQL JDBC Driver to Tomcat

Because Tomcat bundles DBCP natively inside tomcat-dbcp.jar, you only need to ensure the MySQL Connector driver JAR is present in your server's classpath:

Copy mysql-connector-java.jar into the Tomcat/lib directory.
Restart your Tomcat server so it can load the MySQL driver class.

Step 2: Configure DBCP DataSource in context.xml

Define the DBCP DataSource resource in your web application's META-INF/context.xml file:

    <Resource name="jdbc/mysqldb"

      auth="Container"

      type="javax.sql.DataSource"

      factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"

      driverClassName="com.mysql.jdbc.Driver"

      url="jdbc:mysql://localhost:3306/testdb"

      username="root"

      password="root"

      maxTotal="20"

      maxIdle="10"

      minIdle="5"

      maxWaitMillis="10000" />

Step 3: Declare the Resource Reference in web.xml

Add the resource reference inside your web.xml deployment descriptor to map the JNDI lookup name:

    <resource-ref>

      <description>MySQL DBCP Connection Pool</description>

      <res-ref-name>jdbc/mysqldb</res-ref-name>

      <res-type>javax.sql.DataSource</res-type>

      <res-auth>Container</res-auth>

    </resource-ref>

Step 4: Retrieve Connection and Execute Query in Servlet

In your Java Servlet, look up the DataSource via JNDI and execute queries with automatic resource cleanup:

    Context initContext = new InitialContext();

    Context envContext = (Context) initContext.lookup("java:comp/env");

    DataSource ds = (DataSource) envContext.lookup("jdbc/mysqldb");

    try (Connection conn = ds.getConnection();

         Statement stmt = conn.createStatement();

         ResultSet rs = stmt.executeQuery("SELECT user_id, username, email FROM users")) {

      while (rs.next()) {

        out.println(rs.getInt("user_id") + " : " + rs.getString("username"));

      }

    }

📺 Related Connection Pooling Tutorials

Learn more about database architectures and connection pools from our channel:

Click here to watch in Youtube : https://www.youtube.com/watch?v=sXtfWahVEvk&list=UUhwKlOVR041tngjerWxVccw&index=3

Click the below Image to Enlarge

JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql

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.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

public class CityInfoServlet extends HttpServlet
{

    private static final long serialVersionUID = 1L;

    DataSource dataSource = null;
    
    public void init( ServletConfig config )
    {
        try
        {

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

            /*
             *  Using JDNI lookup get the DataSource.
             */
            
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            dataSource = (DataSource) envContext.lookup("jdbc/worldDB");
            
            System.out.println("Using JDNI lookup got the DataSource : "+ dataSource);

            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 = dataSource.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>/cityInfo</url-pattern>
    </servlet-mapping>

    <resource-ref>
        <description>DB Connection</description>
        <res-ref-name>jdbc/worldDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

</web-app>

index.html

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

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

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

To Download CityInfoDemoTomcatDBCPMysqlApp Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/CityInfoDemoTomcatDBCPMysqlApp.zip?attredirects=0&d=1

External Links:

http://commons.apache.org/proper/commons-dbcp/configuration.html

http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle

🚀 Master Java, JDBC & Web Technologies!

Subscribe to Ram N Java for clean, beginner-friendly coding tutorials and architecture guides!

👉 Click Here to Subscribe Now

Why Use Tomcat DBCP with Oracle Database?

Repeatedly opening and tearing down physical database connections in an enterprise application creates significant CPU and network overhead. Apache Tomcat comes pre-configured with Apache Commons DBCP (Database Connection Pooling), allowing web applications to borrow existing, pre-warmed database connections on demand and recycle them back to the pool once requests finish.

Step 1: Add the Oracle JDBC Driver to Tomcat

Tomcat already bundles the DBCP library inside its core libraries. To connect to an Oracle Database, you only need to ensure the Oracle JDBC driver is available globally to Tomcat:

Place your Oracle JDBC driver JAR (e.g., ojdbc6.jar or ojdbc8.jar) directly into the Tomcat/lib folder.
Restart your Apache Tomcat server so it can register the Oracle driver.

Step 2: Configure DBCP DataSource in context.xml

Declare your DBCP DataSource resource in the web application's META-INF/context.xml file:

    <Resource name="jdbc/hrDB"

      auth="Container"

      type="javax.sql.DataSource"

      factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"

      driverClassName="oracle.jdbc.OracleDriver"

      url="jdbc:oracle:thin:@localhost:1521:XE"

      username="hr"

      password="hr"

      maxTotal="25"

      maxIdle="10"

      minIdle="5"

      maxWaitMillis="10000" />

Step 3: Reference the Resource in web.xml

Map the JNDI naming resource inside your application's web.xml deployment descriptor:

    <resource-ref>

      <description>Oracle DBCP Connection Pool</description>

      <res-ref-name>jdbc/hrDB</res-ref-name>

      <res-type>javax.sql.DataSource</res-type>

      <res-auth>Container</res-auth>

    </resource-ref>

Step 4: Perform JNDI Lookup and Query Data in Servlet

Look up the DataSource via JNDI in your Java Servlet, borrow a connection, and execute your SQL queries:

    Context initContext = new InitialContext();

    Context envContext = (Context) initContext.lookup("java:comp/env");

    DataSource ds = (DataSource) envContext.lookup("jdbc/hrDB");

    try (Connection conn = ds.getConnection();

         Statement stmt = conn.createStatement();

         ResultSet rs = stmt.executeQuery("SELECT employee_id, first_name, email FROM employees")) {

      while (rs.next()) {

        out.println(rs.getInt("employee_id") + " - " + rs.getString("first_name"));

      }

    }

📺 Related Oracle & Connection Pooling Tutorials

Explore more database connectivity and connection pool guides from our channel:

Saturday, 25 April 2015

JDBC|Servlets : BoneCP DataSource 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 and Why Use Connection Pooling?

In high-traffic enterprise Java web applications, opening and closing physical database connections on every client request introduces significant latency and resource overhead. Connection Pooling solves this by maintaining a pool of pre-established, reusable database connections.

BoneCP is a high-performance, open-source Java JDBC connection pool library designed to outperform traditional pools like Apache DBCP and C3P0 through lock-free concurrency and multi-threaded partition designs. Configuring BoneCP as a JNDI DataSource within Apache Tomcat enables servlets to efficiently borrow and release MySQL connections.

Configuring BoneCP DataSource in Apache Tomcat

Configuring a BoneCP connection pool for a MySQL database in a Tomcat web application involves defining a JNDI <Resource> inside your context.xml or server.xml:

Driver & URL: Point to com.mysql.jdbc.Driver (or com.mysql.cj.jdbc.Driver) along with the MySQL JDBC connection URL.
Authentication: Provide the database username and password.
Pool Sizing & Partitions: Configure parameters like partitionCount, maxConnectionsPerPartition, and minConnectionsPerPartition to tune throughput.

// Lookup DataSource via JNDI in your Servlet

    Context initContext = new InitialContext();

    Context envContext  = (Context) initContext.lookup("java:/comp/env");

    DataSource ds = (DataSource) envContext.lookup("jdbc/TestDB");


    // Borrow connection from BoneCP pool

    try (Connection conn = ds.getConnection()) {

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

    }

Key Benefits of BoneCP with MySQL

High Performance: Optimized partition-based architecture minimizes thread contention under heavy concurrent workloads.
Clean JNDI Integration: Decouples database connection configuration from servlet application code.
Resource Management: Prevents database server exhaustion by establishing hard limits 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=Kd1HEjLE5qU&index=1&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge

JDBC|Servlets : BoneCP DataSource Mysql - Tomcat

ConnectionManager.java

import com.jolbox.bonecp.BoneCPDataSource;

public class ConnectionManager
{

    private static BoneCPDataSource boneCPDataSource = null;

    public static BoneCPDataSource getBoneCPDataSource()
    {
        return boneCPDataSource;
    }

    public static void setBoneCPDataSource( BoneCPDataSource boneCPDataSource )
    {
        ConnectionManager.boneCPDataSource = boneCPDataSource;
    }

    public static BoneCPDataSource configureBoneCPDataSource()
    {
        try
        {
            /*
             * load the database driver (make sure this is in your classpath!)
             */
            Class.forName("com.mysql.jdbc.Driver");

            /*
             * setup the BoneCPDataSource
             */

            BoneCPDataSource boneCPDataSource = new BoneCPDataSource();

            boneCPDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/world");
            boneCPDataSource.setUsername("root");
            boneCPDataSource.setPassword("root");
            boneCPDataSource.setMinConnectionsPerPartition(5);
            boneCPDataSource.setMaxConnectionsPerPartition(10);
            boneCPDataSource.setPartitionCount(1);

            setBoneCPDataSource(boneCPDataSource);

            System.out
                    .println("contextInitialized.....boneCPDataSource is configured");

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

        return boneCPDataSource;
    }

    public static void closeBoneCPDataSource()
    {

        try
        {
            BoneCPDataSource boneCPDataSource = ConnectionManager
                    .getBoneCPDataSource();
            
            if( boneCPDataSource != null )
            {
                /*
                 * This method must be called only once when the application
                 * stops.
                 */

                boneCPDataSource.close();
                System.out
                        .println("contextDestroyed.....boneCPDataSource is closed!");
            }

        }
        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.configureBoneCPDataSource();
        System.out.println("---------------------------------------------\n");

    }

    @Override
    public void contextDestroyed( ServletContextEvent arg0 )
    {
        System.out.println("\n---------------------------------------------");
        System.out.println("contextDestroyed Method has been Called......");
        ConnectionManager.closeBoneCPDataSource();
        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;

import com.jolbox.bonecp.BoneCPDataSource;

public class CityInfoServlet extends HttpServlet
{

    private static final long serialVersionUID = 1L;

    BoneCPDataSource          boneCPDataSource = null;

    public void init( ServletConfig config )
    {
        try
        {

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

            boneCPDataSource = ConnectionManager.getBoneCPDataSource();

            System.out.println("Got BoneCPDataSource : " + boneCPDataSource);

            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 = boneCPDataSource.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>HelloHttpServlet</title>
</head>
<body>
<p>
<h3>Show City Information</H3>
<p></p>
<ul>

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

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