Connection pooling issue in Apache 6.0

[ujjwal soni]

This code is working fine without connection pooling but when I am using connection pooling I am facing some problem .Please check the code once I have given comment.

1) Connection pool code in apache server

public static Connection getConnection() throws SQLException
    {
    	
    	try
    	{
    	String jndiname="jdbc/min";
    	Context initContxt = new InitialContext();
    	DataSource ds = (DataSource) initContxt.lookup("java:/comp/env/"+jndiname); 
    	connection= ds.getConnection();
    	}
    	catch(Exception e)
    	{}
    	
    	return connection;
    }

2)

Insert image code

	public void imageInsert(int FILE_ID,String File_image ) throws Exception
 	{
 		
 		  		 		
 		Statement stmt = connection.createStatement ();
 		connection.setAutoCommit (false);
 		
 	    try {stmt.execute ("drop table IMAGE_DOCUMENT");}catch(Exception e){}
 	    stmt.execute ("create table IMAGE_DOCUMENT (name varchar2 (30), picture blob)");
 	    stmt.execute ("insert into IMAGE_DOCUMENT values ('"+FILE_ID+"', EMPTY_BLOB())");
 	    ResultSet rset = stmt.executeQuery ("select picture from IMAGE_DOCUMENT where name = '"+FILE_ID+"'");
 	    System.out.println("Wrapped ResultSet class:" + rset.getClass().getName());
 	    //ResultSet rs =((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet) rset).getInnermostDelegate();
 		//System.out.println("Underlying ResultSet class:" + rs.getClass().getName());
 		//ResultSet rs1 = (OracleResultSet) rs;
 		//System.out.println("Oracle ResultSet class:" + rs1.getClass().getName());

 	    if (rset.next ())
 	    {	
 	      
 	        System.out.println("insert into image 111 ");
/** This extra line is add for connection pool */
 	 	  DelegatingResultSet dRS = (DelegatingResultSet) rset; 
 	 	  System.out.println("insert into image 222 ");
/** I am getting error in this line */
 	 	  OracleResultSet oRS = (OracleResultSet) dRS.getInnermostDelegate();
 	 	  System.out.println("insert into image 333 "); 

 	        BLOB blob = ((OracleResultSet)oRS).getBLOB (1);
 		  //BLOB blob = ((OracleResultSet)rset).getBLOB (1);
 		  File binaryFile = new File (File_image);
 		  FileInputStream istream = new FileInputStream (binaryFile);
 		  OutputStream ostream = blob.getBinaryOutputStream ();
 		  byte[] buffer = new byte[9073];
 	      int length = 0;
 	      while ((length = istream.read(buffer)) != -1)
 	      ostream.write(buffer, 0, length);
 	      istream.close();
 	      ostream.close();
 	     
 	    }
 	    rset.close();
 	    stmt.close();
 	    //connection.close(); 
 		
 	
 	}

The Error is:

java.lang.ClassCastException: oracle.jdbc.driver.OracleResultSetImpl
at com.itisl.jpmorgan.objects.FileUploadMaster.imageInsert(FileUploadMaster.java:268)
at com.itisl.jpmorgan.objects.FileUploadMaster.Insert(FileUploadMaster.java:195)
at com.itisl.jpmorgan.servlets.docupload.filename.DocUpload_Save.doPost(DocUpload_Save.java:121)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)



--------------------Here are the contents for server.xml and context files used in tomcat 6.0

Server.xml
In this I changed :type="javax.sql.DataSource" as
type:”oracle.jdbc.pool.OracleDataSource”

<GlobalNamingResources>
    <Environment name="simpleValue" type="java.lang.Integer" value="30"/>
    <Resource auth="Container" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" name="UserDatabase" pathname="conf/tomcat-users.xml" type="org.apache.catalina.UserDatabase"/>
    <Resource driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="4" maxIdle="2" maxWait="5000" name="jdbc/min" password="tiger" type="javax.sql.DataSource" url="jdbc:oracle:thin:@192.168.1.171:1521:ITISL" username="scott"/>
  </GlobalNamingResources>

context.xml

<ResourceLink global="jdbc/MIN" name="jdbc/MIN" type="oracle.jdbc.pool.OracleDataSource"/>

web.xml

<resource-ref>
    <description>Oracle Datasource example</description>
    <res-ref-name>jdbc/min</res-ref-name>
    <res-type>oracle.jdbc.pool.OracleDataSource</res-type>
    <res-auth>Container</res

-auth>
</resource-ref>


Commons.java

public static Connection getConnection() throws SQLException
    {
    	
    	try
    	{
    	String jndiname="jdbc/min";
    	Context initContxt = new InitialContext();
    	OracleDataSource ds = (OracleDataSource) initContxt.lookup("java:/comp/env/"+jndiname); 
    	System.out.println("connected");
    	connection= ds.getConnection();
    	System.out.println("success connected");
    	}
    	catch(Exception e)
    	{e.printStackTrace();}
    	
    	return connection;
    }
   

Just for your reference I wrote:
This code is for without connection pool

public static Connection getConnection() throws SQLException
    {
      	try
    	{
    		try{  
    			
       		  	Class.forName("oracle.jdbc.driver.OracleDriver");
       		 			}catch(ClassNotFoundException cnfe){
       	  				String cnfec=cnfe.getMessage();
       	  				
       	  		  }
       		 			
       		 try{ 
       			     connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.171:1521:ITISL" ,"scott", "tiger");
   		   	    		  }catch(SQLException sqle){
   		   	 					String sqlec=sqle.getMessage();
   		   	 					
   		   	 				}
          	}
    	catch(Exception e)
    	{
    		
    	}
       	return connection;
    }

[ujjwal soni]

Hi,

I did something like this and it worked for me fine.

Servlet Code

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import java.io.IOException;
import java.io.PrintWriter;

import javax.naming.Context;
import javax.naming.InitialContext;
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 TestOne extends HttpServlet {

	/**
	 * @param args
	 */
	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            /* TODO output your page here
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet NewServlet</title>");  
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet NewServlet at " + request.getContextPath () + "</h1>");
            out.println("</body>");
            out.println("</html>");
            */
        } finally { 
            out.close();
        }
    } 

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
   
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
        
        try
        {
        	//Context initContext = new InitialContext();
        	//Context envContext  = (Context)initContext.lookup("java:/comp/env");
        	//DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
        	//Context initCtx = new InitialContext();Context envCtx = (Context) initCtx.lookup("java:comp/env");
        	//DataSource ds = (DataSource)  envCtx.lookup("jdbc/min"); 	
        	Context ic = new InitialContext();
        	DataSource ds = (DataSource) ic.lookup(
        	"java:comp/env/jdbc/min");
        	Connection conn = ds.getConnection();
        	if(!conn.isClosed())
        	{
        		System.out.println("connection is open");
        	}
        	} catch (Exception exception) {
        	System.out.println(exception.toString());
        	}

    } 

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */

    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>


}

My Web.XML content

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" 
	xmlns="http://java.sun.com/xml/ns/j2ee" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 
	http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  
   
 <resource-ref>
 <description>Oracle Datasource example</description>
 <res-ref-name>jdbc/min</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>
  
      <servlet>
        <servlet-name>TestOne</servlet-name>
        <servlet-class>TestOne</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>TestOne</servlet-name>
        <url-pattern>/TestOne</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
        </welcome-file-list>
</web-app>

----------And my context.xml

<?xml version='1.0' encoding='utf-8'?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<!-- The contents of this file will be loaded for each web application -->
<Context>
    <!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
	
    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->

    <!-- Uncomment this to enable Comet connection tacking (provides events
         on session expiration as well as webapp lifecycle) -->
    <!--
    <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
    -->

   
 <Resource name="jdbc/min" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@192.168.1.1:1521:mydb"
              username="ujjwal_admin" password="admin" maxActive="20" maxIdle="10"
              maxWait="-1"/> 

</Context>

I Removed all new entries from server.xml that i made before
And......the ghost went away....

The code is working orsom now.........