Thursday 30 April 2015

JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql

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
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
JDBC|Servlets : Tomcat C3PO Connection Pooling - Mysql
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

    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
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    JDBC|Servlets : Tomcat Connection Pooling - DBCP - Mysql
    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

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

    Click the below Image to Enlarge
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    JDBC|Servlets : Tomcat Connection Pooling - DBCP- Oracle
    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.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 EmployeeInfoServlet 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/HRDB");
    
                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 = "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 DataSource
                 */
    
                connection = dataSource.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("-------------------------------------------");
                }
                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>
    
        <resource-ref>
            <description>DB Connection</description>
            <res-ref-name>jdbc/HRDB</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>Employee Information</title>
    </head>
    <body>
    <p>
    <h3>Employee Information</H3>
    <p></p>
    <ul>
    
    <li><a href="employeeInfo">Show Employee Information</a></li>
    
    </ul>
    </body></html>
    
    
    To Download EmployeeInfoDemoTomcatDBCPOracleApp Project Click the below link

    https://sites.google.com/site/javaee4321/jdbc/EmployeeInfoDemoTomcatDBCPOracleApp.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
  • Saturday 25 April 2015

    JDBC|Servlets : BoneCP DataSource Mysql - Tomcat

    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
    JDBC|Servlets : BoneCP DataSource Mysql - Tomcat
    JDBC|Servlets : BoneCP DataSource Mysql - Tomcat
    JDBC|Servlets : BoneCP DataSource Mysql - Tomcat
    JDBC|Servlets : BoneCP DataSource Mysql - Tomcat
    JDBC|Servlets : BoneCP DataSource Mysql - Tomcat
    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>
    
    
    Environment Used 

    JDK version :1.7.0_51
    Tomcat version : 7.0.50

    To Download CityInfoDemoBoneCPDSMysqlApp Project Click the below link

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

    External Links 

    http://jolbox.com/index.html 

    http://mvnrepository.com/artifact/com.jolbox/bonecp

    http://mvnrepository.com/artifact/com.jolbox/bonecp/0.8.0.RELEASE

    See also:

  • All JavaEE Viedos Playlist
  • All JavaEE Viedos
  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • Java Collection Framework Tutorial