ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jim Borland <jborl...@calpoly.edu>
Subject RE: iBatis - Connections to PostgreSQL Not Closing
Date Mon, 19 Oct 2009 17:09:29 GMT

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


Mime
View raw message