Creating a Connection object in mysql using Connection Pooling

Create a connection obj using Connection pooling in mysql.

Connection pooling is the maintenance of a group of database connections for reuse by applications on an application server. It is part of the JDBC 2.0 Optional Package API. Another part of the Optional Package API provides for the use of the Java Naming and Directory Interface (JNDI) and DataSource objects instead of JDBC 1.0 DriverManager objects to access relational data.

Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven Web application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.


To create the Connection pooling in server first we should know about context.xml

You can find the context.xml file in ApacheTomcat server.

C:\Program Files\Apache Software Foundation\Tomcat 5.5\conf\context.xml

add the Resource tag in context.xml to tell the server while initializing.This will contains all the information about the database in context object.

<Resource name="jdbc/TestDB"
              auth="Container"
              type="javax.sql.DataSource"
              username="root"
              password="password"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mysql?autoReconnect=true"

              testWhileIdle="true"
              testOnBorrow="true"
              testOnReturn="false"
              validationQuery="SELECT 1"
              validationInterval="30000"
              timeBetweenEvictionRunsMillis="5000"
              maxActive="100"
              minIdle="10"
              maxWait="10000"
              initialSize="10"
              removeAbandonedTimeout="60"
              removeAbandoned="true"
              logAbandoned="true"
              minEvictableIdleTimeMillis="30000"
              jmxEnabled="true"
              jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
                     org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;
                     org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx(threshold=10000)"/>

Procedure to create a connection object in java application using  connection pooling.

#1). Initialize the Context
     

Context objContext = new InitialContext();

#2). Lookup for the datasource
    

DataSource objDataSource = (DataSource)objContext.lookup("java:/comp/env/jdbc/projname");

#3). get the Connection object
     

Connection con = objDataSource .getConnection();

Create a class with DbUtility to create a connection with Database from our java application.This class having two methods to create a connection and to close the connection object.


Program for Connection Pooling:

package com;

import java.sql.Connection;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DbUtility {

    private static  Context objContext = null;
    private static  DataSource objDataSource = null;
    
     static
     {
         System.out.println("inside the static block of dbutility");
        try
        {
            objContext = new InitialContext();
             objDataSource =(DataSource)  objContext.lookup("java:/comp/env/jdbc/strutsdemo");
             System.out.println("objDataSource is: "+objDataSource);
        }
        catch(Exception e)
        {
            e.printStackTrace();
            System.out.println("Error while in datasource;");
        }    
     }
     
    public static Connection getConnection() throws Exception{
        
        Connection con = null;
        
        if(objDataSource == null)
        {
            System.out.println("error while getting the datasource");
        }
        else
        {
            con= objDataSource.getConnection();
        }
        return con;
    }
    
    public static void closeConnecton(Connection objConnection) throws Exception
    {
        System.out.println("inside closing the connection");
        if(objConnection != null)
        {
            objConnection.close();
        }
    }
}

For Refence Books: