Return-Path: 
 <user-java-return-17947-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 37707 invoked from network); 19 Oct 2009 17:10:02 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3)
  by minotaur.apache.org with SMTP; 19 Oct 2009 17:10:02 -0000
Received: (qmail 16233 invoked by uid 500); 19 Oct 2009 17:10:01 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 16206 invoked by uid 500); 19 Oct 2009 17:10:01 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 16198 invoked by uid 99); 19 Oct 2009 17:10:01 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Oct 2009 17:10:01 +0000
X-ASF-Spam-Status: No, hits=-0.0 required=10.0
	tests=SPF_HELO_PASS,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Received: from [216.139.236.158] (HELO kuber.nabble.com) (216.139.236.158)
    by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 19 Oct 2009 17:09:51 +0000
Received: from isper.nabble.com ([192.168.236.156])
	by kuber.nabble.com with esmtp (Exim 4.63)
	(envelope-from <lists@nabble.com>)
	id 1Mzvjd-0007S6-Ve
	for user-java@ibatis.apache.org; Mon, 19 Oct 2009 10:09:29 -0700
Message-ID: <25962028.post@talk.nabble.com>
Date: Mon, 19 Oct 2009 10:09:29 -0700 (PDT)
From: Jim Borland <jborland@calpoly.edu>
To: user-java@ibatis.apache.org
Subject: RE: iBatis - Connections to PostgreSQL Not Closing
In-Reply-To: <07094E40F055C34FA35BB28DB1670E7FB36961@bibbmail.bibb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Nabble-From: jborland@calpoly.edu
References: <25943619.post@talk.nabble.com>
 <07094E40F055C34FA35BB28DB1670E7FB36961@bibbmail.bibb.com>
X-Virus-Checked: Checked by ClamAV on apache.org


No, these <IDLE> connections are caused by my program, not the the other
connections in the connection pool.  Here's an updated look at my situation:

I've rewritten my DBHandler class as follows:

================================

public class SwingCatIBatisDBHandler
{
   private SqlMapClient sqlMap;
   private List list = null;

   public SwingCatIBatisDBHandler(SqlMapClient sqlMap)
   {
      this.sqlMap = sqlMap;
   }

   public ArrayList getArtistInfo()
   {
      ArrayList artists;
      Connection conn = null;
      SqlMapSession session = null;
      try
      {
         conn = sqlMap.getDataSource().getConnection();
         session = sqlMap.openSession(conn);
         artists = (ArrayList) session.queryForList("getArtistInfo", list );
      }
      catch(SQLException e)
      {
         e.printStackTrace();
         throw new RuntimeException ("Error executing sqlMap query. Cause: "
+ e);
      }
      finally
      {
         try
         {
            if (session != null) session.close();
         }
         finally
         {
            try
            {
               if (conn != null) conn.close();
            }
            catch(SQLException e)
            {
               e.printStackTrace();
               throw new RuntimeException ("Error executing sqlMap query.
Cause: " + e);
            }
         }
      }
      return artists;
   }
}

================================

Note that I've tried getting my own connection, using it successfully, then
closing it.  This method runs just fine but I still generate unclosed
connections.

I've set things up so I can run getArtistInfo() once and see the result by:
(a) checking database connections (in psql: "select * from
pg_stat_activity;"), and (b) reading the Tomcat log file.  Each time I run
it the number of <IDLE> database connections goes up by one and the log has
one new "DriverManager.getDriver" entry.

After running it a few times, suddenly I get a reduction in the expected
number of <IDLE> processes, and in the log file there is an equal number of
"Finalizing a Connection that was never closed" entries.  This one-to-one
relationship tells me that these results are caused by this program, not
something else.

I've been wrestling with this problem for a long time and right now there
are three things about which I wonder:

(1) All the code examples I've seen show the sqlMapClient being generated in
the same try statement as the actual query. I'm creating it in a separate
class and passing it to another class. Could this be a problem? I'm not sure
why it would matter, but that is something unique about my situation.

(2) In the above code I use the DataSource obtained from SqlMapClient -- Is
there something wrong with doing this?

(3) Have I somehow mis-configured the connection pool?

Help!!


Rick.Wellman wrote:
> 
> Are they simply the other connections in the connection pool?
> 
> -----Original Message-----
> From: Jim Borland [mailto:jborland@calpoly.edu] 
> Sent: Saturday, October 17, 2009 10:04 PM
> To: user-java@ibatis.apache.org
> Subject: iBatis - Connections to PostgreSQL Not Closing
> 
> 
> I have a Java application in Tomcat 5.5 that works fine, but it creates
> several PostgreSQL processes: <IDLE> in transaction, and they just sit
> there
> forever.  I've updated my library jar files as follows:
> 
> iBatis lib file: ibatis-2.3.4.726.jar
> JDBC driver: postgresql-8.3-605.jdbc3.jar
> 
> The PostgreSQL database server - 8.3.7, using Apache Struts2 -- this is
> an
> action implementation.  I've tried to reduce its code here to a bare
> minimum
> to simplify location of the error.  There are two classes involved, and
> the
> call is to method listOfArtists() in class: ListSwingCatAction.
> 
> (1)
> public class ListSwingCatAction implements SessionAware
> {
>    private SqlMapClient sqlMap;
>    private SwingCatIBatisDBHandler myDBHandler;
>    private Map sessionMap;
> 
>    public ListSwingCatAction()
>    {
>       try
>       {
>          sqlMap =
> SqlMapClientBuilder.buildSqlMapClient(Resources.getResourceAsReader("sql
> Maps.xml"));
>       }
>       catch (Exception e)
>       {
>          e.printStackTrace();
>          throw new RuntimeException ("Error initializing my SwingCat
> class.
> Cause: " + e);
>       }
> 
>       myDBHandler = new SwingCatIBatisDBHandler(sqlMap);
>    }
> 	
>    public String listOfArtists()
>    {
>       ArrayList artists = myDBHandler.getArtistInfo();
>       sessionMap.put("artists", artists);
>       return "success";		
>    }
> }	
> 
> (2)
> public class SwingCatIBatisDBHandler
> {
>    private SqlMapClient sqlMap;
>    private List list = null;
> 
>    public SwingCatIBatisDBHandler(SqlMapClient sqlMap)
>    {
>       this.sqlMap = sqlMap;
>    }
> 
>    public ArrayList getArtistInfo()
>    {
>       ArrayList artists;
>       try
>       {
>          sqlMap.startTransaction();
>          //artists is an array of Artists objects -- the list parameter
> is a
> dummy
>          artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list
> );
>          sqlMap.commitTransaction();
>       }
>       catch(SQLException e)
>       {
>          e.printStackTrace();
>          throw new RuntimeException ("Error executing sqlMap query.
> Cause: "
> + e);
>       }
>       finally
>       {
>          try
>          {
>             sqlMap.endTransaction();
>          }
>          catch(SQLException e)
>          {
>             e.printStackTrace();
>             throw new RuntimeException ("Error executing sqlMap query.
> Cause: " + e);
>          }
>       }
>       return artists;
>    }
> }
> 
> (3) sqlMaps.xml file:
> 
> <?xml version="1.0" encoding="UTF-8" ?>
> <!DOCTYPE sqlMapConfig
> PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> 
>   
> <sqlMapConfig>
>   <properties resource="ibatis.properties" />
>   <settings
>    cacheModelsEnabled="true"
>    enhancementEnabled="true"
>    lazyLoadingEnabled="true"
>    useStatementNamespaces="false" />
>   <transactionManager type="JDBC">
>     <dataSource type="DBCP">
>       <property name="driverClassName" value="${driver}"/>
>       <property name="url" value="${url}"/>
>       <property name="username" value="${username}"/>
>       <property name="password" value="${password}"/>
>       <property name="logAbandoned" value="true"/>
>       <property name="removeAbandoned" value="true"/>
>       <property name="removeAbandonedTimeout" value="1"/>
>       <property name="Driver.logUnclosedConnections" value="true"/>
>     </dataSource>
>   </transactionManager>
>   <sqlMap resource="swingCat-sqlMap.xml" />
> </sqlMapConfig>
> 
> It's probably something very simple, but for the life of me I can't see
> my
> problem.  Any help you can give me would be VERY MUCH apprciated!  Thank
> you.
> 
> -- 
> View this message in context:
> http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp2
> 5943619p25943619.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25962028.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org