Pool connection leak error

[Jignesh Gohel]

Hi,


In my application i am having a two classes which interacts with the database (ie creates the prepared statements & executes the query...etc.)

Now for getting the connection to the database I have made a general class called DTSConnection having a getConnection() & closePreStCon() methods which gets the connection & closes the connection respectively.

Now if do a simple connection then it works fine.
But when i am using connection pooling ,i am getting the following error:

A JDBC pool connection leak was detected.A connection leak occurs when a connection obtained from the pool was not closed explicitly by calling close() & then was disposed by the Garbage collector & returned to the connection pool.

I am providing the code templates which i have used for both the query interactor classes which is related to the connection below....


Also my actual environment where the project is to be depolyed consists of
weblogic server & unix enviroment with jdk1.3.

But my development environment consists of Tomcat 4.1 & jdk 1.4 & Windows.
In my development enviroment i am not obtaining the connections using pooling while in deployemnt environment connection pooling is used.

Both the query interactor classes are as follows:

QueryInteractor class 1

package 
imports...
public class DTSQueryInteractor 
{
 
   private DTSConnection con = new DTSConnection();
   private Connection moDbConn = null;
   private PreparedStatement lo_PrepareStmt = null;
   private ResultSet lo_ResultSet  = null;
 
   private boolean flag = false;
 
   public List getArrayList(String pQryStmt, HashMap valueMap) throws Exception
   {
 ...logic

 try 
 {
 
   moDbConn = con.getConnection();
   lo_PrepareStmt = moDbConn.prepareStatement(pQryStmt);
 
 }
 catch(Exception e)
 {
   e.printStackTrace();
   throw new SystemException(e);
 }
 finally
 {
   try
   {
       con.closePreStCon(lo_PrepareStmt, moDbConn);
   }
   catch(Exception e)
   {
   }
 
 }
 
           return ...
 }

QueryInteractor 2

package 
imports...
 
public class DshBrdQueryInteractor 
{
 
   private DTSConnection con = new DTSConnection();
   private Connection moDbConn = null;
   private PreparedStatement lo_PrepareStmt = null;
   private ResultSet lo_ResultSet  = null;
 
   private boolean flag = false;
   
 
 
   public List getArrayList(String pQryStmt, HashMap valueMap) throws Exception
   {

 try 
 {
 
   moDbConn = con.getConnection();
   lo_PrepareStmt = moDbConn.prepareStatement(pQryStmt);
                            
                       ....
                        execute the statement ....
 }
 catch(Exception e)
 {
   e.printStackTrace();
 }
 finally
 {
   try
   {
  con.closePreStCon(lo_PrepareStmt, moDbConn);
   }
   catch(Exception e)
   {
   }
 
 }
 
return ...;
     
 }
 
 
 
  public boolean createPreparedStatement(String query,HashMap params) 
  {
boolean flag = false;

 
try
{
moDbConn = con.getConnection();
 
if(moDbConn == null)
{
flag = false;

}
else
{
//This is done for using rollback & commit purpose when using Java
moDbConn.setAutoCommit(false);
 
flag = true;
 
lo_PrepareStmt = moDbConn.prepareStatement(query);


}
catch(SQLException sqe)
{ 
 flag = false;
}
catch(Exception e)
{ 
 flag = false;
 
}

 return flag;
 
  }
  
  //For simple select queries
 
  public boolean selectQuery(HttpServletRequest request)
  {
  try
  {
  lo_ResultSet = lo_PrepareStmt.executeQuery();
                             ............. 
   return ....
  
  }
  catch(SQLException sqe)
  {   
     return false;
   }
  catch(Exception e)
  {
     return false;
  }
  finally
  {
   try
   {
 lo_ResultSet.close();
 lo_ResultSet = null;
  con.closePreStCon(lo_PrepareStmt, moDbConn);
   }
   catch(Exception e)
   {

   }
 
 }
 
  }
 
  public int updateExecute()
  {  
  try
  {
int i = lo_PrepareStmt.executeUpdate();
//Committing the operation
moDbConn.commit();
 
return i;
  }
  catch(SQLException sqe)
  {   
 
 if (moDbConn != null) 
 {
rollbackXaction();
 }
 
 return 0;
  }
  catch(Exception e)
  {
if (moDbConn != null) 
{
rollbackXaction();
}
  
     return 0;
  }
  finally
  {
   try
   {
 con.closePreStCon(lo_PrepareStmt, moDbConn);
   }
   catch(Exception e)
   {
   }
 
 }
 
 
  }
 
  public void rollbackXaction()
  {
  try
  {
   moDbConn.rollback();
 
  }
  catch(SQLException sqe)
  {
  }
  catch(Exception e)
  {
  }
 
  }

So please help me to sort out the error And also please tell me what is the reson behind this problem.

What i think is (just a guess )it may be due to the use of getConnection() method in both the classes.But i have closed all the connections afetr getting them...
[ June 13, 2006: Message edited by: Jignesh Gohel ]

[Paul Clapham]

Originally posted by Jignesh Gohel:
But i have closed all the connections afetr getting them...

No, you haven't. Look more closely at your createPreparedStatement method.

[Jignesh Gohel]

Hi Paul thanks for the reply.

Its because after preparing the statement using createPreparedStatement() method i am immediately using either the selectQuery() or updateExecute() method & in both of them i am closing the connections.

And is it valid that we can close the connection after preparing the statement & later on use that prepared statement object after opening the connection once gain???

And please tell me what changes i will have to amke in my code..It would be a great help