Configure JNDI Connection Pooling:

Configure JNDI Connection Pooling:

For establishing the connection pool i have created a dynamic web-project with SampleConn, with MySql Database and loggers. So, make sure we have the mysql.jar in tomcat working lib directory and log4j.jar in your project library.

Here i am listing out the steps to follow:

------------------------------------------------------------------------------------------
Step 1) Configure web.xml file

Here i am invoking a servlet on startup of server (GetConnPool)

---------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>SampleConn</display-name>
  <resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/JIT</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
  <servlet>
    <servlet-name>Connection</servlet-name>
    <servlet-class>com.jit.servlets.GetConnPool</servlet-class>;
    <load-on-startup>1</load-on-startup>
  </servlet>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>
----------------------------------------------------------------

Step 2) Create a context.xml in META-INF of your Project

-----------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<Context privileged="true" antiResourceLocking="false"
    antiJARLocking="false" debug="1" reloadable="true" path="">
   
     <!-- maxActive: Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL dB username and password for dB connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->
   
    <!-- url: The JDBC connection url for connecting to your MySQL dB.
         -->
   
   
    <Resource name="jdbc/JIT" auth="Container" type="javax.sql.DataSource"
         username="root"
         password="root" maxActive="-1" maxIdle="-1" maxWait="-1"
         driverClassName="com.mysql.jdbc.Driver"
         url="jdbc:mysql://localhost:3306/sample?autoReconnect=true"
         />
   
</Context>

--------------------------------------------------------------------

Step 3) Configure your log4j.properties file in your project source directory

--------------------------------------------------------------------

# Set root category priority to INFO and its only appender to CONSOLE.
log4j.rootCategory=DEBUG, CONSOLE
#log4j.rootCategory=INFO, CONSOLE, LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.Threshold=DEBUG
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=[%d{dd MMM yyyy HH:mm:ss}] %l - %m%n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=axis.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.Threshold=DEBUG
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=[%d{dd MMM yyyy HH:mm:ss}] %l - %m%n

log4j.logger.org.apache.commons.digester.Digester=info


--------------------------------------------------------------------
Step 4)  GetConnPool.java
Here in the init() i am invoking & closing the connection

---------------------------------------------------------------------
package com.jit.servlets;
/**
 * @author Vinay Guntaka
 */
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jit.connections.ConnectionUtil;

/**
 * Servlet implementation class GetConnPool
 */
public class GetConnPool extends HttpServlet {
    private static final long serialVersionUID = 1L;
      
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetConnPool() {
        super();
        // TODO Auto-generated constructor stub
    }
     @Override
    public void init() throws ServletException {
        // TODO Auto-generated method stub
        super.init();
       
        ConnectionUtil.closeConnection(ConnectionUtil.getConnection());
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

}

------------------------------------------------
Step 5) ConnectionUtil.Java

This is the main program that we are fetching the JNDI Connection Pool Object
------------------------------------------------
package com.jit.connections;
/**
 * @author Vinay Guntaka
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

import org.apache.log4j.Logger;

public class ConnectionUtil
{
    //Initlizing the Logger
    private static final Logger logger = Logger.getLogger(ConnectionUtil.class);
   
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context) initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource) envCtx.lookup("jdbc/JIT");
            conn = ds.getConnection();
            if (conn != null) {
                logger.info("Successfully Obtained Connection From ConnectionPool");
            }
            return conn;
        } catch (SQLException se) {
            logger.error(se.getMessage());
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return conn;
    }
    public static Connection closeConnection(Connection conn){
        try {
            if(conn != null){
                conn.close();
                conn = null;
                logger.info("Successfully Closed Connection From ConnectionPool");
               
            }
        } catch (SQLException se) {
            logger.error(se.getMessage());
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return conn;
    }

   
}
----------------------------------------------------------------------

Retrieving multiple result sets from a stored procedure in a JDBC application

Hello,

Retrieving multiple result sets and OUT Parameters by passing IN Parameter from a stored procedure in a JDBC application.

Connection File:

package com.jit;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * @author Vinay Guntaka
 * Retrieving multiple result sets from a stored procedure in a JDBC application
 * All Connections are Configured in this Class
 */
public class ConnectionUtil {
  

    public static Connection closeConnection(Connection conn){
        try {
            if(conn != null){
                conn.close();
                conn = null;
            }
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    public static CallableStatement closeCallableStatement(CallableStatement clst){
        try{
            if(clst !=null){
                clst.close();
                clst = null;
            }
            }catch (SQLException e) {
                System.err.println(e.getMessage());
            }
            return clst;
        }
      
  

    public static ResultSet closeResultSet(ResultSet resultSet){
        try {
            if(resultSet != null){
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        return resultSet;
    }

  

    public static Connection getLocalConnection() {
        Connection conn = null;
        try {
          
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://localhost:3306/sample";
            conn = DriverManager.getConnection(url, "root", "root");

            if (conn != null) {
                System.out.println("Successfully Obtained Connection From ConnectionPool");
            }
            return conn;
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }
}


Main Program To Execute Procedure:

package com.jit;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
/**
 * @author Vinay Guntaka
 * We are Fetching the Results from MySQL Procedure by passing
 * one IN Param and fetching One OUT Param with Multiple Result sets.
 */
public class CallProcedure {
    public static void main(String[] args) {
        Connection conn =null;
        CallableStatement callStmt = null;
        ResultSet rs =null;
      
        try {
            conn = ConnectionUtil.getLocalConnection();
            callStmt = conn.prepareCall("call proc_getData(?,?)");
            //Passing param to Execute First Query
            callStmt.setInt(1, 22);
            //Registering the Out Parameter
            callStmt.registerOutParameter(2,Types.VARCHAR);
            //Fetching the First RS of OUT Param
            callStmt.execute();
            String outParam = callStmt.getString(2);           ; // OUT parameter
            System.out.println("First Result Set Out Param:-"+outParam);
            //Fetching the Second Result Set from Procedure
            rs = callStmt.executeQuery();
            while(rs.next()){
                System.out.println("Second Query Result Set");
                System.out.println(rs.getString(1));
                System.out.println(rs.getString(2));
                System.out.println(rs.getString(3));
                System.out.println(rs.getString(4));
                System.out.println(rs.getString(5));
            }
            //Checking is there any more result sets
            if(callStmt.getMoreResults())
            {
                System.out.println("One More Result Set is there");
                //Fetching the Thrid Result Set from the Procedure
                rs = callStmt.executeQuery();
                while(rs.next()){
                    System.out.println("Thrid Query Result Set");
                    System.out.println(rs.getString(1));
                    System.out.println(rs.getString(2));
                    System.out.println(rs.getString(3));
                    System.out.println(rs.getString(4));
                    System.out.println(rs.getString(5));
                }
            }else
            {
                System.out.println("No More Results Sets");
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }finally
        {  
            //Closing all connections
            ConnectionUtil.closeConnection(conn);
            ConnectionUtil.closeCallableStatement(callStmt);
            ConnectionUtil.closeResultSet(rs);
        }
      
    }
}

Here is the procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sample`.`proc_getData`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_getData`(in user_id int,out user_name varchar(64))
BEGIN
    select user_name from users where id=user_id into user_name ;
    select * from employee;
    select * from users;
   
    END$$

DELIMITER ;