Snaq DBPool Connection Pool : Java Connection Pooling Part

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

We have already gone through with Tomcat JDBC Connection Pool example, today we will check Snaq DBPool connection pooling.

What is DBPool?

A Java-based database connection pooling utility, supporting time-based expiry, statement caching, connection validation, and easy configuration using a pool manager.

How do I use it?

To use DBPool you need to have the JAR file in a location where it's available for use by the host system you are using. For standalone applications this usually means in the CLASSPATH, and for application servers there is usually a specific directory is recommended for JAR libraries (for example, when used with Apache Tomcat it can be placed in the /WEB-INF/lib directory).
Usually DBPool is used in two different ways:

1. Direct use of individual connection pools.
2. Using the ConnectionPoolManager to manage one or more connection pools.

If you have never used DBPool before it's recommended that you start by simply integrating asingle connection pool into your application/applet to see how it works and performs. This provides the simplest direct support for pooled database connections and will get you up and running quickly. Once you learn exactly how it behaves and the benefits it can give you can try the pool manager approach to manage multiple pools if necessary.

Benefit or Features of DBPool :

Logging configuration
Connection validation
Disabling statement caching
Password encryption
Shutdown Hook
Asynchronous/forced pool release
Pool listeners
PoolTracer

You can read brief description of each features Snaq DBPool.

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.

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


  DBPoolTest
  
    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

Now, we need to create a properties file for database connection with DBPool.

# Local Database Details
# -----------------------------------------
name=pool-mysql
drivers=com.mysql.jdbc.Driver

# Local MySQL server :
pool-mysql.url=jdbc:mysql://localhost:3306/testdb
pool-mysql.user=root
pool-mysql.password=

pool-mysql.validator=snaq.db.AutoCommitValidator
pool-mysql.minpool=1
pool-mysql.maxpool=50
pool-mysql.maxsize=100
pool-mysql.idleTimeout=10

We will also create Logger properties file for print logs in console

# =========================================================================
#                   Log4j Configuration File for DBPool.
#
# This shows an example log4j.properties file and how it works with DBPool.
# It needs to be in the CLASSPATH to be found.
# For more information on how to configure log4j, visit the website:
#     http://logging.apache.org/log4j/
# =========================================================================

# -----------------------------
# Appender to write to console.
# -----------------------------
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.Target=System.out
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
log4j.appender.CONSOLE.file =${catalina.base}/logs/regularLogging.log
log4j.appender.CONSOLE.MaxFileSize=100KB

# ---------------------------------------------
# Appender to write to application log.
# ---------------------------------------------
log4j.appender.APPLICATIONLOG=org.apache.log4j.DailyRollingFileAppender
log4j.appender.APPLICATIONLOG.File=${catalina.base}/logs/DBPoolTest.log
log4j.appender.APPLICATIONLOG.DatePattern='.'yyyy-MM-dd
#log4j.appender.APPLICATIONLOG.File=/home/devphp/public_html/restletframework/logs/APPLICATION-LOG.log
log4j.appender.APPLICATIONLOG.Encoding=UTF-8
log4j.appender.APPLICATIONLOG.layout=org.apache.log4j.PatternLayout
log4j.appender.APPLICATIONLOG.layout.ConversionPattern=%d [%5p]: %m%n

# ---------------------------------------------
# Appender to write to shared pool manager log.
# ---------------------------------------------
#log4j.appender.POOLMANAGERS=org.apache.log4j.FileAppender
#log4j.appender.POOLMANAGERS.File=${catalina.base}/webapps/restletframework/logs/DBPool-POOLMANAGERS.log
#log4j.appender.POOLMANAGERS.File=/home/devphp/public_html/restletframework/logs/DBPool-POOLMANAGERS.log
#log4j.appender.POOLMANAGERS.Encoding=UTF-8
#log4j.appender.POOLMANAGERS.layout=org.apache.log4j.PatternLayout
#log4j.appender.POOLMANAGERS.layout.ConversionPattern=%d [%5p]: %m%n

# -------------------------------------
# Appender to write to shared pool log.
# -------------------------------------
#log4j.appender.POOLS=org.apache.log4j.FileAppender
#log4j.appender.POOLS.File=${catalina.base}/webapps/restletframework/logs/DBPool-POOLS.log
#log4j.appender.POOLS.File=/home/devphp/public_html/restletframework/logs/DBPool-POOLS.log
#log4j.appender.POOLS.Encoding=UTF-8
#log4j.appender.POOLS.layout=org.apache.log4j.PatternLayout
#log4j.appender.POOLS.layout.ConversionPattern=%d [%5p]: %m%n

# Appender for specific pool (pool-local).
#log4j.appender.LOCAL=org.apache.log4j.FileAppender
#log4j.appender.LOCAL.File=DBPool-LOCAL.log
#log4j.appender.LOCAL.Encoding=UTF-8
#log4j.appender.LOCAL.layout=org.apache.log4j.PatternLayout
#log4j.appender.LOCAL.layout.ConversionPattern=%d [%5p]: %m%n


# Turn off root logging.
#log4j.rootLogger=info, APPLICATIONLOG
log4j.rootLogger=info, CONSOLE , APPLICATIONLOG 


# ---------------------------------------------------------------------------
# Enable pool manager logging.
# To enable logging for a specific pool manager instance, specify it by name:
#     log4j.logger.snaq.db.ConnectionPoolManager.=...
# where  is the name property set in the properties file.
# 
# The example below logs trace pool manager activity to both the POOLMANAGER
# and CONSOLE appenders (i.e. System.out and to file "DBPool-managers.log").
# ---------------------------------------------------------------------------
#log4j.logger.snaq.db.ConnectionPoolManager=trace, POOLMANAGERS, CONSOLE

# ---------------------------------------------------------------------------
# Enable pool logging.
# To enable logging for a specific pool instance, specify it by name:
#     log4j.logger.snaq.db.ConnectionPool.=...
# where  is the name of the pool as declared in the properties file.
# 
# The example below logs normal pool activity (from all pools) to both the
# POOLS and CONSOLE appenders (i.e. System.out and to file "DBPool-pools.log").
# Un-commenting the 2nd line would also write a debug log for the 'pool-local'
# pool specified (must also un-comment related 'LOCAL' appender above).
# ---------------------------------------------------------------------------
#log4j.logger.snaq.db.ConnectionPool=info, POOLS, CONSOLE
#log4j.logger.snaq.db.ConnectionPool.pool-local=debug, LOCAL

Now, we will connect database with DBPooling with this class

package com.test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import snaq.db.ConnectionPoolManager;

/**
 * DBPool class file will be used to get new Database connection with the help of ConnectionPoolManager. 

 * It will fetch all the DB Connection properties from DBPool.properties file and it will create new connection.
 */
public class DBPool {
 
 protected Connection conn;  
    protected ConnectionPoolManager connManager;
    private static DBPool dbPool;
    
    static Logger logger = Logger.getLogger(DBPool.class);
    
    // Name of the database connection name from DBPool.properties file.
    static final String databaseName = "pool-mysql";
    
    /** 
     * Class constructor creates ConnectionPoolManager object
     * @exception properties file not found.
     */
    public DBPool(){
     try {  
            connManager = ConnectionPoolManager.getInstance("DBPool.properties");  
        } catch (IOException ex) {  
         logger.info("Error While Connecting with DBPool Properties file :=> "+ex.toString()); 
        }  
    }
    
    
    /**
     * Creates/Provides the instance of the Pool.
     * @return DBPool
     */
    public static DBPool getInstance(){
     if(dbPool==null)
      dbPool = new DBPool();
     return dbPool;
    }
    
    /**
     * Sets the connection object.
     * @exception cannot get connection.
     */
    public Connection getConn() {  
        Connection con = null;  
        try {  
            con = connManager.getConnection(databaseName);
            logger.info("Connection Created: " + con.toString());  
        } catch (SQLException ex) {  
         logger.info("Error While Creating Connection :=> "+ex.toString());  
        }  
        if (con != null) {  
            this.conn = con;  
            logger.info("Connection Released: "+ this.conn.toString());  
            return con;  
        } else {  
            return con;  
        }  
    } 
}

Now, create a servlet to made connection with Database through DBPool and fetch user details

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.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
public class TestServlet extends HttpServlet {
     
 private static final long serialVersionUID = 1L;
 private DBPool dbPool;
    private Connection connection;
    private Statement statement;
     
    public void init() throws ServletException {
        try {
         // Get Connection and Statement
         dbPool = DBPool.getInstance();
            connection = dbPool.getConn();
        } catch (Exception 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 {
         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.