Thursday, August 29, 2013

Tomcat JDBC Connection Pool : Java Connection Pooling Part - 1


What is Connection Pooling? 

One of the most expensive database-related tasks is the initial creation of the connection. Once the connection has been made the transaction often takes place very quickly. A connection pool maintains a pool of opened connections so the application can simply grab one when it needs to, use it, and then hand it back, eliminating much of the long wait for the creation of connections.

There are so many Connection Pooling library available in Java, Some of them are as follows ;
  • Tomcat JDBC Connection Pool
  • Snaq DBPool
  • C3P0
  • BoneCP
Today we will go through with Tomcat JDBC Connection Pool. I'll give a brief introduction with basic example (not in-depth).

Introduction :

JDBC connection pooling is conceptually similar to any other form of object pooling. Database connections are often expensive to create because of the overhead of establishing a network connection and initializing a database connection session in the back end database. In turn, connection session initialization often requires time consuming processing to perform user authentication, establish transactional contexts and establish other aspects of the session that are required for subsequent database usage.

Additionally, the database's ongoing management of all of its connection sessions can impose a major limiting factor on the scalability of your application. Valuable database resources such as locks, memory, cursors, transaction logs, statement handles and temporary tables all tend to increase based on the number of concurrent connection sessions.

All in all, JDBC database connections are both expensive to initially create and then maintain over time. Therefore, as we shall see, they are an ideal resource to pool.

you can checkout the detail description and features of JDBC Connection Pool here and here.



Lets move to a simple example :

We will create a simple java web application in Eclipse which will retrieve the list of user from MySQL database. i guess most of you know about how to create project in Eclipse, so we will directly focus on Coding portion.

Now we will create a schema and a simple user table with dummy data.

CREATE DATABASE  IF NOT EXISTS `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `testdb`;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`userID`),
  UNIQUE KEY `userID_UNIQUE` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
INSERT INTO `user` VALUES (1,'John','Miller','john@example.com','john'),(2,'Aaron','Taylor','aaron@example.com','aaron'),(3,'Derek','Thompson','derek@example.com','derek'),(4,'Jimmy','Walker','jimmy@example.com','jimmy'),(5,'Peter','Young','peter@example.com','peter'),(6,'Rafael','Green','rafael@example.com','rafael');
UNLOCK TABLES;


Below is the Project Structure, that we need to create.




Project web.xml structure



  TomcatJDBCExample
  
    index.jsp
  
  
    TestServlet
    com.test.TestServlet
  
  
    TestServlet
    /TestServlet
  


Create a index.jsp page in Web Content Directory

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>



   Database Details
   
   
   
   
   


 
 

User Details :

User ID First Name Last Name Email ID Password


We need to create a context.xml file under META-INF for JDBC connection pool parameters


 



Now, create a servlet under com.test package.

package com.test;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
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 TestServlet extends HttpServlet {
     
 private static final long serialVersionUID = 1L;
 private DataSource dataSource;
    private Connection connection;
    private Statement statement;
     
    public void init() throws ServletException {
        try {
            // Get DataSource
            Context initContext  = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:/comp/env");
            dataSource = (DataSource)envContext.lookup("jdbc/testdb");
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
 
    public void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
         
        ResultSet resultSet = null;
        String returnString = "";
        PrintWriter pw = resp.getWriter();
        ResponseParseFactory responseParse =  new ResponseParseFactory();
        try {
            // Get Connection and Statement
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            String query = "SELECT * FROM USER";
            resultSet = statement.executeQuery(query);
            ResultSetMetaData rsmetadata = resultSet.getMetaData();
            returnString = responseParse.createJsonFile(resultSet,rsmetadata,"userRecords");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try { if(null!=resultSet)resultSet.close();} catch (SQLException e) 
            {e.printStackTrace();}
            try { if(null!=statement)statement.close();} catch (SQLException e) 
            {e.printStackTrace();}
            try { if(null!=connection)connection.close();} catch (SQLException e) 
            {e.printStackTrace();}
        }
        pw.println(returnString);
    }
}


We are sending response in JSON so, we will create a class which converts data in to JSON format.

package com.test;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.LinkedHashMap;

import org.json.simple.JSONValue;

public class ResponseParseFactory {
 
 /**
  * this method will generate JSON String from the ResultSet
  * set system status and service status success if it works properly.
  * @param rs
  * @param rsMetaData
  * @param methodName
  * @return jsonString
  */
 public String createJsonFile(ResultSet rs, ResultSetMetaData rsMetaData, String methodName){
  String jsonString = "";
  ArrayList l1 = new ArrayList();
  
  LinkedHashMap data = new LinkedHashMap();
  LinkedHashMap service_status = new LinkedHashMap();
  LinkedHashMap list = new LinkedHashMap();
  try{
         if(rs!=null){
          while(rs.next()){
           LinkedHashMap datauser = new LinkedHashMap();
           int totColumns = rsMetaData.getColumnCount();
           for(int i=1;i<=totColumns;i++){
            String columnName = rsMetaData.getColumnName(i);
            Object columnValue = rs.getObject(i);
            if(columnValue!=null && !"".equalsIgnoreCase(columnValue.toString()))
             datauser.put(columnName, columnValue.toString());
            else
             datauser.put(columnName, "");
           }
           l1.add(datauser);
          }
         }else{
          
         }
         data.put("data", l1);
         service_status.put("service_status", "success");
         service_status.put("result", data);
         service_status.put("system_status", "true");
         list.put(methodName, service_status);
         jsonString = JSONValue.toJSONString(list);
         System.out.println(jsonString);
         return jsonString;
  }catch(Exception ex){
   System.out.println("Error while json creation ::"+ex.toString());
         return jsonString;
  }
 }
}


All coding part is done.
Now run this project in Tomcat and you will see the index.jsp page with no records.



just click on Get Data and it will call TestServlet servlet and it will get the data from database and put in jQuery table.



You can get the project zip from here. Extract and import it to eclipse and you are good to go.. :)

All Done!

References :
Apache JDBC Connection Pool
Eclipse IDE
JavaRanch

Share:

2 comments:

  1. Thank you for this tutorials..
    I have a question that which Connection pooling Api is best to use among listed below?

    1. Tomcat JDBC Connection Pool
    2. Snaq DBPool
    3. C3P0
    4. BoneCP

    ReplyDelete
    Replies
    1. i have used Snaq DBPool for more than 3 Years but currently the development is not in progress so i think BoneCP is good and its is widely used in market.

      Delete