iBatis - Connections to PostgreSQL Not Closing

[Jim Borland]

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("sqlMaps.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.

[Rick.Wellman]

Are they simply the other connections in the connection pool?

-----Original Message-----
From: Jim Borland [mailto:jborland@...]
Sent: Saturday, October 17, 2009 10:04 PM
To: user-java@...
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@...
For additional commands, e-mail: user-java-help@...


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

[Jim Borland]

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:[e-mail]]
Sent: Saturday, October 17, 2009 10:04 PM
To: [e-mail]
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: [e-mail]
For additional commands, e-mail: [e-mail]


---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Larry Meadors]

This looks to me like you are *way* overcomplicating this. :-)

The method should be more like this:

public List getArtistInfo(){
  return sqlMap.queryForList("getArtistInfo", list);
}

Unless you have some really crazy wacky stuff going on, there should
never be a need for you to deal with connections at that level.

Also, what's the purpose of passing in 'list' as the second parameter
there? I don't see where it would ever be non-null.

Larry

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

[Rick.Wellman]

Since I have some time over lunch:
1) I agree with Larry's reply below
2) At the risk of embarrassing myself on this forum, see below for my
reply to your comments and questions:

[your-code-sample-was-here]
[your-comments-were-here]
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.
>> Usually, your entire application would share a single instance of
SqlMapClient.  It matters in the sense that it is un-necessary and
would, at a minimum, create an entirely new connection pool (see #3 for
more)

(2) In the above code I use the DataSource obtained from SqlMapClient --
Is there something wrong with doing this?
>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
the "normal" way to use the SqlMapClient)

(3) Have I somehow mis-configured the connection pool?
>> I could be wrong but I still highly suspect that the connections are
a result of the connection pool and it seems to me that you're not
understanding the purpose of a connection pool.  i.e. You're trying to
explicitly open a connection with code.  The connection pool will
usually expand and contract the number of connections to the database
based on the load and its configuration (which is why it is called a
"pool").  You do not have "direct" control over which connection your
iBatis SqlMapClient will use [nor do you probably want that].  I
apologize in advance if I am way off base with this response; not my
intent to offend, but rather educate.

To the masses... in regards to my comment #3, is there an implementation
of a "pool" which is not a pool at all but a single connection that
someone can use to verify an instance like this?  Or maybe configure the
"pool" to only have a size of one?  Just thinking out loud... I've never
had reason to look into something like this but it seems like this
question comes up every so often? (i.e. the question of connections
opened via iBatis)

-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@...]
Sent: Monday, October 19, 2009 12:56 PM
To: user-java@...
Subject: Re: iBatis - Connections to PostgreSQL Not Closing

This looks to me like you are *way* overcomplicating this. :-)

The method should be more like this:

public List getArtistInfo(){
  return sqlMap.queryForList("getArtistInfo", list);
}

Unless you have some really crazy wacky stuff going on, there should
never be a need for you to deal with connections at that level.

Also, what's the purpose of passing in 'list' as the second parameter
there? I don't see where it would ever be non-null.

Larry

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


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

[Jim Borland]

Thank you very much for your reply.  I LOVE short and sweet compared to overcomplicating things.

I tried this --> return (ArrayList) sqlMap.queryForList("getArtistInfo", list); -- (had to add the cast or it was rejected for being "incompatible types").  

This time it was rejected for: "unreported exception java.sql.SQLException; must be caught or declared to be thrown."  Here's what finally made it through the compiler:

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

ArrayList artists = null;
try
{
    artists = (ArrayList)sqlMap.queryForList("getArtistInfo", list );
}
catch(SQLException e)
{
    e.printStackTrace();
}
return artists;

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

Why is it required that I catch an SQLException?

Don't remember why I added the 'list' as the second parameter.  Wrote that part some time ago and I think it was the only way I could make it work.

My new code still generates the same behavior -- Each time I run it the number of <IDLE> database connections goes up by one and I end up with a bunch of "Finalizing a Connection that was never closed" entries in the log.

Thanks in advance for any help you can give me with this!

Larry Meadors wrote:

This looks to me like you are *way* overcomplicating this. :-)

The method should be more like this:

public List getArtistInfo(){
  return sqlMap.queryForList("getArtistInfo", list);
}

Unless you have some really crazy wacky stuff going on, there should
never be a need for you to deal with connections at that level.

Also, what's the purpose of passing in 'list' as the second parameter
there? I don't see where it would ever be non-null.

Larry

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Jim Borland]

I've been fighting this issue for a long time now and am quite frustrated.  I originally started out with just:

--> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list ); -- but was getting all these <IDLE> connections.  So I tried adding start/commit/end transaction statements surrounding the query.  Still getting <IDLE>s so then I tried using :

--> session = sqlMap.openSession(); -- and letting the session start/commit/end the transaction.  Still got <IDLE>s.  That's when I tried creating, using and closing my own connection with the same sad result.

One thing Rick Wellman said was especially interesting.  Every time you create an instance of SqlMapClient you create an entirely new connection pool.  I hadn't thought about that before.  I guess the bottom line is I don't really understand what is happening in a connection pool.  Still, my situation is so simple, yet the same bad outcome occurs no matter what I try.  Help!

Rick.Wellman wrote:

Since I have some time over lunch:
1) I agree with Larry's reply below
2) At the risk of embarrassing myself on this forum, see below for my
reply to your comments and questions:

[your-code-sample-was-here]
[your-comments-were-here]
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.
>> Usually, your entire application would share a single instance of
SqlMapClient.  It matters in the sense that it is un-necessary and
would, at a minimum, create an entirely new connection pool (see #3 for
more)

(2) In the above code I use the DataSource obtained from SqlMapClient --
Is there something wrong with doing this?
>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
the "normal" way to use the SqlMapClient)

(3) Have I somehow mis-configured the connection pool?
>> I could be wrong but I still highly suspect that the connections are
a result of the connection pool and it seems to me that you're not
understanding the purpose of a connection pool.  i.e. You're trying to
explicitly open a connection with code.  The connection pool will
usually expand and contract the number of connections to the database
based on the load and its configuration (which is why it is called a
"pool").  You do not have "direct" control over which connection your
iBatis SqlMapClient will use [nor do you probably want that].  I
apologize in advance if I am way off base with this response; not my
intent to offend, but rather educate.

To the masses... in regards to my comment #3, is there an implementation
of a "pool" which is not a pool at all but a single connection that
someone can use to verify an instance like this?  Or maybe configure the
"pool" to only have a size of one?  Just thinking out loud... I've never
had reason to look into something like this but it seems like this
question comes up every so often? (i.e. the question of connections
opened via iBatis)

-----Original Message-----
From: Larry Meadors [mailto:[e-mail]]
Sent: Monday, October 19, 2009 12:56 PM
To: [e-mail]
Subject: Re: iBatis - Connections to PostgreSQL Not Closing

This looks to me like you are *way* overcomplicating this. :-)

The method should be more like this:

public List getArtistInfo(){
  return sqlMap.queryForList("getArtistInfo", list);
}

Unless you have some really crazy wacky stuff going on, there should
never be a need for you to deal with connections at that level.

Also, what's the purpose of passing in 'list' as the second parameter
there? I don't see where it would ever be non-null.

Larry

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]


---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Warren Bell-2]

Are you using any DAO implementation ? Spring? Makes things much simpler.

Warren

Jim Borland wrote:

> I've been fighting this issue for a long time now and am quite frustrated.  I
> originally started out with just:
>
> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list ); --
> but was getting all these <IDLE> connections.  So I tried adding
> start/commit/end transaction statements surrounding the query.  Still
> getting <IDLE>s so then I tried using :
>
> --> session = sqlMap.openSession(); -- and letting the session
> start/commit/end the transaction.  Still got <IDLE>s.  That's when I tried
> creating, using and closing my own connection with the same sad result.
>
> One thing Rick Wellman said was especially interesting.  Every time you
> create an instance of SqlMapClient you create an entirely new connection
> pool.  I hadn't thought about that before.  I guess the bottom line is I
> don't really understand what is happening in a connection pool.  Still, my
> situation is so simple, yet the same bad outcome occurs no matter what I
> try.  Help!
>
>
> Rick.Wellman wrote:
>  
>> Since I have some time over lunch:
>> 1) I agree with Larry's reply below
>> 2) At the risk of embarrassing myself on this forum, see below for my
>> reply to your comments and questions:
>>
>> [your-code-sample-was-here]
>> [your-comments-were-here]
>> 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.
>>    
>>>> Usually, your entire application would share a single instance of
>>>>        
>> SqlMapClient.  It matters in the sense that it is un-necessary and
>> would, at a minimum, create an entirely new connection pool (see #3 for
>> more)
>>
>> (2) In the above code I use the DataSource obtained from SqlMapClient --
>> Is there something wrong with doing this?
>>    
>>>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
>>>>        
>> the "normal" way to use the SqlMapClient)
>>
>> (3) Have I somehow mis-configured the connection pool?
>>    
>>>> I could be wrong but I still highly suspect that the connections are
>>>>        
>> a result of the connection pool and it seems to me that you're not
>> understanding the purpose of a connection pool.  i.e. You're trying to
>> explicitly open a connection with code.  The connection pool will
>> usually expand and contract the number of connections to the database
>> based on the load and its configuration (which is why it is called a
>> "pool").  You do not have "direct" control over which connection your
>> iBatis SqlMapClient will use [nor do you probably want that].  I
>> apologize in advance if I am way off base with this response; not my
>> intent to offend, but rather educate.
>>
>> To the masses... in regards to my comment #3, is there an implementation
>> of a "pool" which is not a pool at all but a single connection that
>> someone can use to verify an instance like this?  Or maybe configure the
>> "pool" to only have a size of one?  Just thinking out loud... I've never
>> had reason to look into something like this but it seems like this
>> question comes up every so often? (i.e. the question of connections
>> opened via iBatis)
>>
>> -----Original Message-----
>> From: Larry Meadors [mailto:larry.meadors@...]
>> Sent: Monday, October 19, 2009 12:56 PM
>> To: user-java@...
>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>
>> This looks to me like you are *way* overcomplicating this. :-)
>>
>> The method should be more like this:
>>
>> public List getArtistInfo(){
>>   return sqlMap.queryForList("getArtistInfo", list);
>> }
>>
>> Unless you have some really crazy wacky stuff going on, there should
>> never be a need for you to deal with connections at that level.
>>
>> Also, what's the purpose of passing in 'list' as the second parameter
>> there? I don't see where it would ever be non-null.
>>
>> Larry
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@...
>> For additional commands, e-mail: user-java-help@...
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@...
>> For additional commands, e-mail: user-java-help@...
>>
>>
>>
>>    
>
>  

--
Thanks,

Warren Bell


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

[Jim Borland]

No, I'm not smart enough to use a DAO implementation (I've read a little about them).  Also, I keep reading about Spring -- a whole bunch of stuff on it comes up when I Google on these topics.  Someday I'm going to check into Spring.

My situation is very simple and it seems like plain old iBatis ought to be plenty for me in this application.  iBatis is supposed to take care of all the background stuff and just let me write mapped statements.  I'm committed to making iBatis work without a bunch of extra stuff.  Thanks for your interest in my problem.

Warren Bell-2 wrote:

Are you using any DAO implementation ? Spring? Makes things much simpler.

Warren

Jim Borland wrote:
> I've been fighting this issue for a long time now and am quite frustrated.  I
> originally started out with just:
>
> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list ); --
> but was getting all these <IDLE> connections.  So I tried adding
> start/commit/end transaction statements surrounding the query.  Still
> getting <IDLE>s so then I tried using :
>
> --> session = sqlMap.openSession(); -- and letting the session
> start/commit/end the transaction.  Still got <IDLE>s.  That's when I tried
> creating, using and closing my own connection with the same sad result.
>
> One thing Rick Wellman said was especially interesting.  Every time you
> create an instance of SqlMapClient you create an entirely new connection
> pool.  I hadn't thought about that before.  I guess the bottom line is I
> don't really understand what is happening in a connection pool.  Still, my
> situation is so simple, yet the same bad outcome occurs no matter what I
> try.  Help!
>
>
> Rick.Wellman wrote:
>  
>> Since I have some time over lunch:
>> 1) I agree with Larry's reply below
>> 2) At the risk of embarrassing myself on this forum, see below for my
>> reply to your comments and questions:
>>
>> [your-code-sample-was-here]
>> [your-comments-were-here]
>> 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.
>>    
>>>> Usually, your entire application would share a single instance of
>>>>        
>> SqlMapClient.  It matters in the sense that it is un-necessary and
>> would, at a minimum, create an entirely new connection pool (see #3 for
>> more)
>>
>> (2) In the above code I use the DataSource obtained from SqlMapClient --
>> Is there something wrong with doing this?
>>    
>>>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
>>>>        
>> the "normal" way to use the SqlMapClient)
>>
>> (3) Have I somehow mis-configured the connection pool?
>>    
>>>> I could be wrong but I still highly suspect that the connections are
>>>>        
>> a result of the connection pool and it seems to me that you're not
>> understanding the purpose of a connection pool.  i.e. You're trying to
>> explicitly open a connection with code.  The connection pool will
>> usually expand and contract the number of connections to the database
>> based on the load and its configuration (which is why it is called a
>> "pool").  You do not have "direct" control over which connection your
>> iBatis SqlMapClient will use [nor do you probably want that].  I
>> apologize in advance if I am way off base with this response; not my
>> intent to offend, but rather educate.
>>
>> To the masses... in regards to my comment #3, is there an implementation
>> of a "pool" which is not a pool at all but a single connection that
>> someone can use to verify an instance like this?  Or maybe configure the
>> "pool" to only have a size of one?  Just thinking out loud... I've never
>> had reason to look into something like this but it seems like this
>> question comes up every so often? (i.e. the question of connections
>> opened via iBatis)
>>
>> -----Original Message-----
>> From: Larry Meadors [mailto:[e-mail]]
>> Sent: Monday, October 19, 2009 12:56 PM
>> To: [e-mail]
>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>
>> This looks to me like you are *way* overcomplicating this. :-)
>>
>> The method should be more like this:
>>
>> public List getArtistInfo(){
>>   return sqlMap.queryForList("getArtistInfo", list);
>> }
>>
>> Unless you have some really crazy wacky stuff going on, there should
>> never be a need for you to deal with connections at that level.
>>
>> Also, what's the purpose of passing in 'list' as the second parameter
>> there? I don't see where it would ever be non-null.
>>
>> Larry
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [e-mail]
>> For additional commands, e-mail: [e-mail]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [e-mail]
>> For additional commands, e-mail: [e-mail]
>>
>>
>>
>>    
>
>  


--
Thanks,

Warren Bell


---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Larry Meadors]

Can you post the sqlmapconfig.xml?

On Mon, Oct 19, 2009 at 1:44 PM, Jim Borland <jborland@...> wrote:

>
> No, I'm not smart enough to use a DAO implementation (I've read a little
> about them).  Also, I keep reading about Spring -- a whole bunch of stuff on
> it comes up when I Google on these topics.  Someday I'm going to check into
> Spring.
>
> My situation is very simple and it seems like plain old iBatis ought to be
> plenty for me in this application.  iBatis is supposed to take care of all
> the background stuff and just let me write mapped statements.  I'm committed
> to making iBatis work without a bunch of extra stuff.  Thanks for your
> interest in my problem.
>
>
> Warren Bell-2 wrote:
>>
>> Are you using any DAO implementation ? Spring? Makes things much simpler.
>>
>> Warren
>>
>> Jim Borland wrote:
>>> I've been fighting this issue for a long time now and am quite
>>> frustrated.  I
>>> originally started out with just:
>>>
>>> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list ); --
>>> but was getting all these <IDLE> connections.  So I tried adding
>>> start/commit/end transaction statements surrounding the query.  Still
>>> getting <IDLE>s so then I tried using :
>>>
>>> --> session = sqlMap.openSession(); -- and letting the session
>>> start/commit/end the transaction.  Still got <IDLE>s.  That's when I
>>> tried
>>> creating, using and closing my own connection with the same sad result.
>>>
>>> One thing Rick Wellman said was especially interesting.  Every time you
>>> create an instance of SqlMapClient you create an entirely new connection
>>> pool.  I hadn't thought about that before.  I guess the bottom line is I
>>> don't really understand what is happening in a connection pool.  Still,
>>> my
>>> situation is so simple, yet the same bad outcome occurs no matter what I
>>> try.  Help!
>>>
>>>
>>> Rick.Wellman wrote:
>>>
>>>> Since I have some time over lunch:
>>>> 1) I agree with Larry's reply below
>>>> 2) At the risk of embarrassing myself on this forum, see below for my
>>>> reply to your comments and questions:
>>>>
>>>> [your-code-sample-was-here]
>>>> [your-comments-were-here]
>>>> 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.
>>>>
>>>>>> Usually, your entire application would share a single instance of
>>>>>>
>>>> SqlMapClient.  It matters in the sense that it is un-necessary and
>>>> would, at a minimum, create an entirely new connection pool (see #3 for
>>>> more)
>>>>
>>>> (2) In the above code I use the DataSource obtained from SqlMapClient --
>>>> Is there something wrong with doing this?
>>>>
>>>>>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
>>>>>>
>>>> the "normal" way to use the SqlMapClient)
>>>>
>>>> (3) Have I somehow mis-configured the connection pool?
>>>>
>>>>>> I could be wrong but I still highly suspect that the connections are
>>>>>>
>>>> a result of the connection pool and it seems to me that you're not
>>>> understanding the purpose of a connection pool.  i.e. You're trying to
>>>> explicitly open a connection with code.  The connection pool will
>>>> usually expand and contract the number of connections to the database
>>>> based on the load and its configuration (which is why it is called a
>>>> "pool").  You do not have "direct" control over which connection your
>>>> iBatis SqlMapClient will use [nor do you probably want that].  I
>>>> apologize in advance if I am way off base with this response; not my
>>>> intent to offend, but rather educate.
>>>>
>>>> To the masses... in regards to my comment #3, is there an implementation
>>>> of a "pool" which is not a pool at all but a single connection that
>>>> someone can use to verify an instance like this?  Or maybe configure the
>>>> "pool" to only have a size of one?  Just thinking out loud... I've never
>>>> had reason to look into something like this but it seems like this
>>>> question comes up every so often? (i.e. the question of connections
>>>> opened via iBatis)
>>>>
>>>> -----Original Message-----
>>>> From: Larry Meadors [mailto:larry.meadors@...]
>>>> Sent: Monday, October 19, 2009 12:56 PM
>>>> To: user-java@...
>>>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>>>
>>>> This looks to me like you are *way* overcomplicating this. :-)
>>>>
>>>> The method should be more like this:
>>>>
>>>> public List getArtistInfo(){
>>>>   return sqlMap.queryForList("getArtistInfo", list);
>>>> }
>>>>
>>>> Unless you have some really crazy wacky stuff going on, there should
>>>> never be a need for you to deal with connections at that level.
>>>>
>>>> Also, what's the purpose of passing in 'list' as the second parameter
>>>> there? I don't see where it would ever be non-null.
>>>>
>>>> Larry
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>> For additional commands, e-mail: user-java-help@...
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>> For additional commands, e-mail: user-java-help@...
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>> --
>> Thanks,
>>
>> Warren Bell
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@...
>> For additional commands, e-mail: user-java-help@...
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25964382.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@...
> For additional commands, e-mail: user-java-help@...
>
>

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

[Jim Borland]

Thank you for your interest.  Here it is:

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

<?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>

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

Larry Meadors wrote:

Can you post the sqlmapconfig.xml?

On Mon, Oct 19, 2009 at 1:44 PM, Jim Borland <[e-mail]> wrote:
>
> No, I'm not smart enough to use a DAO implementation (I've read a little
> about them).  Also, I keep reading about Spring -- a whole bunch of stuff on
> it comes up when I Google on these topics.  Someday I'm going to check into
> Spring.
>
> My situation is very simple and it seems like plain old iBatis ought to be
> plenty for me in this application.  iBatis is supposed to take care of all
> the background stuff and just let me write mapped statements.  I'm committed
> to making iBatis work without a bunch of extra stuff.  Thanks for your
> interest in my problem.
>
>
> Warren Bell-2 wrote:
>>
>> Are you using any DAO implementation ? Spring? Makes things much simpler.
>>
>> Warren
>>
>> Jim Borland wrote:
>>> I've been fighting this issue for a long time now and am quite
>>> frustrated.  I
>>> originally started out with just:
>>>
>>> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list ); --
>>> but was getting all these <IDLE> connections.  So I tried adding
>>> start/commit/end transaction statements surrounding the query.  Still
>>> getting <IDLE>s so then I tried using :
>>>
>>> --> session = sqlMap.openSession(); -- and letting the session
>>> start/commit/end the transaction.  Still got <IDLE>s.  That's when I
>>> tried
>>> creating, using and closing my own connection with the same sad result.
>>>
>>> One thing Rick Wellman said was especially interesting.  Every time you
>>> create an instance of SqlMapClient you create an entirely new connection
>>> pool.  I hadn't thought about that before.  I guess the bottom line is I
>>> don't really understand what is happening in a connection pool.  Still,
>>> my
>>> situation is so simple, yet the same bad outcome occurs no matter what I
>>> try.  Help!
>>>
>>>
>>> Rick.Wellman wrote:
>>>
>>>> Since I have some time over lunch:
>>>> 1) I agree with Larry's reply below
>>>> 2) At the risk of embarrassing myself on this forum, see below for my
>>>> reply to your comments and questions:
>>>>
>>>> [your-code-sample-was-here]
>>>> [your-comments-were-here]
>>>> 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.
>>>>
>>>>>> Usually, your entire application would share a single instance of
>>>>>>
>>>> SqlMapClient.  It matters in the sense that it is un-necessary and
>>>> would, at a minimum, create an entirely new connection pool (see #3 for
>>>> more)
>>>>
>>>> (2) In the above code I use the DataSource obtained from SqlMapClient --
>>>> Is there something wrong with doing this?
>>>>
>>>>>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
>>>>>>
>>>> the "normal" way to use the SqlMapClient)
>>>>
>>>> (3) Have I somehow mis-configured the connection pool?
>>>>
>>>>>> I could be wrong but I still highly suspect that the connections are
>>>>>>
>>>> a result of the connection pool and it seems to me that you're not
>>>> understanding the purpose of a connection pool.  i.e. You're trying to
>>>> explicitly open a connection with code.  The connection pool will
>>>> usually expand and contract the number of connections to the database
>>>> based on the load and its configuration (which is why it is called a
>>>> "pool").  You do not have "direct" control over which connection your
>>>> iBatis SqlMapClient will use [nor do you probably want that].  I
>>>> apologize in advance if I am way off base with this response; not my
>>>> intent to offend, but rather educate.
>>>>
>>>> To the masses... in regards to my comment #3, is there an implementation
>>>> of a "pool" which is not a pool at all but a single connection that
>>>> someone can use to verify an instance like this?  Or maybe configure the
>>>> "pool" to only have a size of one?  Just thinking out loud... I've never
>>>> had reason to look into something like this but it seems like this
>>>> question comes up every so often? (i.e. the question of connections
>>>> opened via iBatis)
>>>>
>>>> -----Original Message-----
>>>> From: Larry Meadors [mailto:[e-mail]]
>>>> Sent: Monday, October 19, 2009 12:56 PM
>>>> To: [e-mail]
>>>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>>>
>>>> This looks to me like you are *way* overcomplicating this. :-)
>>>>
>>>> The method should be more like this:
>>>>
>>>> public List getArtistInfo(){
>>>>   return sqlMap.queryForList("getArtistInfo", list);
>>>> }
>>>>
>>>> Unless you have some really crazy wacky stuff going on, there should
>>>> never be a need for you to deal with connections at that level.
>>>>
>>>> Also, what's the purpose of passing in 'list' as the second parameter
>>>> there? I don't see where it would ever be non-null.
>>>>
>>>> Larry
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [e-mail]
>>>> For additional commands, e-mail: [e-mail]
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [e-mail]
>>>> For additional commands, e-mail: [e-mail]
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>> --
>> Thanks,
>>
>> Warren Bell
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [e-mail]
>> For additional commands, e-mail: [e-mail]
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25964382.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [e-mail]
> For additional commands, e-mail: [e-mail]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Jeff Butler-2]

We should also see the Java code that creates the SqlMapClient.
Without Spring you need to make sure that only a SINGLE instance of
that object is created (it should be and stored either in a singleton
or something like a web context).

Jeff Butler

On Mon, Oct 19, 2009 at 2:50 PM, Larry Meadors <larry.meadors@...> wrote:

> Can you post the sqlmapconfig.xml?
>
> On Mon, Oct 19, 2009 at 1:44 PM, Jim Borland <jborland@...> wrote:
>>
>> No, I'm not smart enough to use a DAO implementation (I've read a little
>> about them).  Also, I keep reading about Spring -- a whole bunch of stuff on
>> it comes up when I Google on these topics.  Someday I'm going to check into
>> Spring.
>>
>> My situation is very simple and it seems like plain old iBatis ought to be
>> plenty for me in this application.  iBatis is supposed to take care of all
>> the background stuff and just let me write mapped statements.  I'm committed
>> to making iBatis work without a bunch of extra stuff.  Thanks for your
>> interest in my problem.
>>
>>
>> Warren Bell-2 wrote:
>>>
>>> Are you using any DAO implementation ? Spring? Makes things much simpler.
>>>
>>> Warren
>>>
>>> Jim Borland wrote:
>>>> I've been fighting this issue for a long time now and am quite
>>>> frustrated.  I
>>>> originally started out with just:
>>>>
>>>> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list ); --
>>>> but was getting all these <IDLE> connections.  So I tried adding
>>>> start/commit/end transaction statements surrounding the query.  Still
>>>> getting <IDLE>s so then I tried using :
>>>>
>>>> --> session = sqlMap.openSession(); -- and letting the session
>>>> start/commit/end the transaction.  Still got <IDLE>s.  That's when I
>>>> tried
>>>> creating, using and closing my own connection with the same sad result.
>>>>
>>>> One thing Rick Wellman said was especially interesting.  Every time you
>>>> create an instance of SqlMapClient you create an entirely new connection
>>>> pool.  I hadn't thought about that before.  I guess the bottom line is I
>>>> don't really understand what is happening in a connection pool.  Still,
>>>> my
>>>> situation is so simple, yet the same bad outcome occurs no matter what I
>>>> try.  Help!
>>>>
>>>>
>>>> Rick.Wellman wrote:
>>>>
>>>>> Since I have some time over lunch:
>>>>> 1) I agree with Larry's reply below
>>>>> 2) At the risk of embarrassing myself on this forum, see below for my
>>>>> reply to your comments and questions:
>>>>>
>>>>> [your-code-sample-was-here]
>>>>> [your-comments-were-here]
>>>>> 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.
>>>>>
>>>>>>> Usually, your entire application would share a single instance of
>>>>>>>
>>>>> SqlMapClient.  It matters in the sense that it is un-necessary and
>>>>> would, at a minimum, create an entirely new connection pool (see #3 for
>>>>> more)
>>>>>
>>>>> (2) In the above code I use the DataSource obtained from SqlMapClient --
>>>>> Is there something wrong with doing this?
>>>>>
>>>>>>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
>>>>>>>
>>>>> the "normal" way to use the SqlMapClient)
>>>>>
>>>>> (3) Have I somehow mis-configured the connection pool?
>>>>>
>>>>>>> I could be wrong but I still highly suspect that the connections are
>>>>>>>
>>>>> a result of the connection pool and it seems to me that you're not
>>>>> understanding the purpose of a connection pool.  i.e. You're trying to
>>>>> explicitly open a connection with code.  The connection pool will
>>>>> usually expand and contract the number of connections to the database
>>>>> based on the load and its configuration (which is why it is called a
>>>>> "pool").  You do not have "direct" control over which connection your
>>>>> iBatis SqlMapClient will use [nor do you probably want that].  I
>>>>> apologize in advance if I am way off base with this response; not my
>>>>> intent to offend, but rather educate.
>>>>>
>>>>> To the masses... in regards to my comment #3, is there an implementation
>>>>> of a "pool" which is not a pool at all but a single connection that
>>>>> someone can use to verify an instance like this?  Or maybe configure the
>>>>> "pool" to only have a size of one?  Just thinking out loud... I've never
>>>>> had reason to look into something like this but it seems like this
>>>>> question comes up every so often? (i.e. the question of connections
>>>>> opened via iBatis)
>>>>>
>>>>> -----Original Message-----
>>>>> From: Larry Meadors [mailto:larry.meadors@...]
>>>>> Sent: Monday, October 19, 2009 12:56 PM
>>>>> To: user-java@...
>>>>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>>>>
>>>>> This looks to me like you are *way* overcomplicating this. :-)
>>>>>
>>>>> The method should be more like this:
>>>>>
>>>>> public List getArtistInfo(){
>>>>>   return sqlMap.queryForList("getArtistInfo", list);
>>>>> }
>>>>>
>>>>> Unless you have some really crazy wacky stuff going on, there should
>>>>> never be a need for you to deal with connections at that level.
>>>>>
>>>>> Also, what's the purpose of passing in 'list' as the second parameter
>>>>> there? I don't see where it would ever be non-null.
>>>>>
>>>>> Larry
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>>> For additional commands, e-mail: user-java-help@...
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>>> For additional commands, e-mail: user-java-help@...
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Thanks,
>>>
>>> Warren Bell
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>> For additional commands, e-mail: user-java-help@...
>>>
>>>
>>>
>>
>> --
>> View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25964382.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@...
>> For additional commands, e-mail: user-java-help@...
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@...
> For additional commands, e-mail: user-java-help@...
>
>

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

[Jim Borland]

In an off-line email conversation with Rick Wellman I've been inspired to rethink one of my assumptions.  Up until now I've assumed that the "Finalizing a Connection that was never closed" message is due to having an abandoned connection, and happens because I have:

--> <property name="removeAbandoned" value="true"/> in my <sqlMapConfig> file.  Maybe that's not the case.  Could this just be the normal action of a connection pool purging itself of excess connections?

Rick.Wellman wrote:

Since I have some time over lunch:
1) I agree with Larry's reply below
2) At the risk of embarrassing myself on this forum, see below for my
reply to your comments and questions:

[your-code-sample-was-here]
[your-comments-were-here]
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.
>> Usually, your entire application would share a single instance of
SqlMapClient.  It matters in the sense that it is un-necessary and
would, at a minimum, create an entirely new connection pool (see #3 for
more)

(2) In the above code I use the DataSource obtained from SqlMapClient --
Is there something wrong with doing this?
>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
the "normal" way to use the SqlMapClient)

(3) Have I somehow mis-configured the connection pool?
>> I could be wrong but I still highly suspect that the connections are
a result of the connection pool and it seems to me that you're not
understanding the purpose of a connection pool.  i.e. You're trying to
explicitly open a connection with code.  The connection pool will
usually expand and contract the number of connections to the database
based on the load and its configuration (which is why it is called a
"pool").  You do not have "direct" control over which connection your
iBatis SqlMapClient will use [nor do you probably want that].  I
apologize in advance if I am way off base with this response; not my
intent to offend, but rather educate.

To the masses... in regards to my comment #3, is there an implementation
of a "pool" which is not a pool at all but a single connection that
someone can use to verify an instance like this?  Or maybe configure the
"pool" to only have a size of one?  Just thinking out loud... I've never
had reason to look into something like this but it seems like this
question comes up every so often? (i.e. the question of connections
opened via iBatis)

-----Original Message-----
From: Larry Meadors [mailto:[e-mail]]
Sent: Monday, October 19, 2009 12:56 PM
To: [e-mail]
Subject: Re: iBatis - Connections to PostgreSQL Not Closing

This looks to me like you are *way* overcomplicating this. :-)

The method should be more like this:

public List getArtistInfo(){
  return sqlMap.queryForList("getArtistInfo", list);
}

Unless you have some really crazy wacky stuff going on, there should
never be a need for you to deal with connections at that level.

Also, what's the purpose of passing in 'list' as the second parameter
there? I don't see where it would ever be non-null.

Larry

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]


---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Larry Meadors]

On Mon, Oct 19, 2009 at 1:17 PM, Jim Borland <jborland@...> wrote:
> pool.  I hadn't thought about that before.  I guess the bottom line is I
> don't really understand what is happening in a connection pool.  Still, my
> situation is so simple, yet the same bad outcome occurs no matter what I
> try.  Help!
>

A connection pool is simply a collection of connection objects that
are kept open as a performance optimization technique.

Creating network connection to a database server can be an expensive
operation (in terms of time), so often web servers will create a
"pool" of connections that are left open and then shared as needed.

When you ask for a "new" connection, you get an already opened
connection from the pool, instead of waiting for a new one to be
opened, and the pool marks it as in use so no one else gets it.

When you "close" that connection, the pool just marks it as available
so that it can be reused.

That may be why you are seeing idle connections when you aren't
expecting them. After you have closed the connection, it's still open
- that's how a pool works - but it may show up as idle.

Larry

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

[Jim Borland]

Here it is.  Thanks for your interest in my situation.  Using Apache Struts2 - this is an action implementation.  The call is to "listOfArtists" in class "ListSwingCatAction"

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

public class ListSwingCatAction implements SessionAware
{
   private SqlMapClient sqlMap;
   private SwingCatIBatisDBHandler myDBHandler;
   private Map sessionMap;

   public ListSwingCatAction()
   {
      try
      {
         sqlMap = SqlMapClientBuilder.buildSqlMapClient(Resources.getResourceAsReader("sqlMaps.xml"));
      }
      catch (Exception e)
      {
         e.printStackTrace();
      }
      myDBHandler = new SwingCatIBatisDBHandler(sqlMap);
   }

   public String listOfArtists()
   {
      ArrayList artists = myDBHandler.getArtistInfo();
      sessionMap.put("artists", artists);
      return "success";
   }
}

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

Jeff Butler-2 wrote:

We should also see the Java code that creates the SqlMapClient.
Without Spring you need to make sure that only a SINGLE instance of
that object is created (it should be and stored either in a singleton
or something like a web context).

Jeff Butler

On Mon, Oct 19, 2009 at 2:50 PM, Larry Meadors <[e-mail]> wrote:
> Can you post the sqlmapconfig.xml?
>
> On Mon, Oct 19, 2009 at 1:44 PM, Jim Borland <[e-mail]> wrote:
>>
>> No, I'm not smart enough to use a DAO implementation (I've read a little
>> about them).  Also, I keep reading about Spring -- a whole bunch of stuff on
>> it comes up when I Google on these topics.  Someday I'm going to check into
>> Spring.
>>
>> My situation is very simple and it seems like plain old iBatis ought to be
>> plenty for me in this application.  iBatis is supposed to take care of all
>> the background stuff and just let me write mapped statements.  I'm committed
>> to making iBatis work without a bunch of extra stuff.  Thanks for your
>> interest in my problem.
>>
>>
>> Warren Bell-2 wrote:
>>>
>>> Are you using any DAO implementation ? Spring? Makes things much simpler.
>>>
>>> Warren
>>>
>>> Jim Borland wrote:
>>>> I've been fighting this issue for a long time now and am quite
>>>> frustrated.  I
>>>> originally started out with just:
>>>>
>>>> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list ); --
>>>> but was getting all these <IDLE> connections.  So I tried adding
>>>> start/commit/end transaction statements surrounding the query.  Still
>>>> getting <IDLE>s so then I tried using :
>>>>
>>>> --> session = sqlMap.openSession(); -- and letting the session
>>>> start/commit/end the transaction.  Still got <IDLE>s.  That's when I
>>>> tried
>>>> creating, using and closing my own connection with the same sad result.
>>>>
>>>> One thing Rick Wellman said was especially interesting.  Every time you
>>>> create an instance of SqlMapClient you create an entirely new connection
>>>> pool.  I hadn't thought about that before.  I guess the bottom line is I
>>>> don't really understand what is happening in a connection pool.  Still,
>>>> my
>>>> situation is so simple, yet the same bad outcome occurs no matter what I
>>>> try.  Help!
>>>>
>>>>
>>>> Rick.Wellman wrote:
>>>>
>>>>> Since I have some time over lunch:
>>>>> 1) I agree with Larry's reply below
>>>>> 2) At the risk of embarrassing myself on this forum, see below for my
>>>>> reply to your comments and questions:
>>>>>
>>>>> [your-code-sample-was-here]
>>>>> [your-comments-were-here]
>>>>> 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.
>>>>>
>>>>>>> Usually, your entire application would share a single instance of
>>>>>>>
>>>>> SqlMapClient.  It matters in the sense that it is un-necessary and
>>>>> would, at a minimum, create an entirely new connection pool (see #3 for
>>>>> more)
>>>>>
>>>>> (2) In the above code I use the DataSource obtained from SqlMapClient --
>>>>> Is there something wrong with doing this?
>>>>>
>>>>>>> Well, probably... and it is un-necessary.  Use Larry's version. (i.e.
>>>>>>>
>>>>> the "normal" way to use the SqlMapClient)
>>>>>
>>>>> (3) Have I somehow mis-configured the connection pool?
>>>>>
>>>>>>> I could be wrong but I still highly suspect that the connections are
>>>>>>>
>>>>> a result of the connection pool and it seems to me that you're not
>>>>> understanding the purpose of a connection pool.  i.e. You're trying to
>>>>> explicitly open a connection with code.  The connection pool will
>>>>> usually expand and contract the number of connections to the database
>>>>> based on the load and its configuration (which is why it is called a
>>>>> "pool").  You do not have "direct" control over which connection your
>>>>> iBatis SqlMapClient will use [nor do you probably want that].  I
>>>>> apologize in advance if I am way off base with this response; not my
>>>>> intent to offend, but rather educate.
>>>>>
>>>>> To the masses... in regards to my comment #3, is there an implementation
>>>>> of a "pool" which is not a pool at all but a single connection that
>>>>> someone can use to verify an instance like this?  Or maybe configure the
>>>>> "pool" to only have a size of one?  Just thinking out loud... I've never
>>>>> had reason to look into something like this but it seems like this
>>>>> question comes up every so often? (i.e. the question of connections
>>>>> opened via iBatis)
>>>>>
>>>>> -----Original Message-----
>>>>> From: Larry Meadors [mailto:[e-mail]]
>>>>> Sent: Monday, October 19, 2009 12:56 PM
>>>>> To: [e-mail]
>>>>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>>>>
>>>>> This looks to me like you are *way* overcomplicating this. :-)
>>>>>
>>>>> The method should be more like this:
>>>>>
>>>>> public List getArtistInfo(){
>>>>>   return sqlMap.queryForList("getArtistInfo", list);
>>>>> }
>>>>>
>>>>> Unless you have some really crazy wacky stuff going on, there should
>>>>> never be a need for you to deal with connections at that level.
>>>>>
>>>>> Also, what's the purpose of passing in 'list' as the second parameter
>>>>> there? I don't see where it would ever be non-null.
>>>>>
>>>>> Larry
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [e-mail]
>>>>> For additional commands, e-mail: [e-mail]
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [e-mail]
>>>>> For additional commands, e-mail: [e-mail]
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Thanks,
>>>
>>> Warren Bell
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [e-mail]
>>> For additional commands, e-mail: [e-mail]
>>>
>>>
>>>
>>
>> --
>> View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25964382.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [e-mail]
>> For additional commands, e-mail: [e-mail]
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [e-mail]
> For additional commands, e-mail: [e-mail]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Jim Borland]

Thank you for a very clear and concise explanation.  I appreciate your taking the time to reply.

Larry Meadors wrote:

On Mon, Oct 19, 2009 at 1:17 PM, Jim Borland <[e-mail]> wrote:
> pool.  I hadn't thought about that before.  I guess the bottom line is I
> don't really understand what is happening in a connection pool.  Still, my
> situation is so simple, yet the same bad outcome occurs no matter what I
> try.  Help!
>

A connection pool is simply a collection of connection objects that
are kept open as a performance optimization technique.

Creating network connection to a database server can be an expensive
operation (in terms of time), so often web servers will create a
"pool" of connections that are left open and then shared as needed.

When you ask for a "new" connection, you get an already opened
connection from the pool, instead of waiting for a new one to be
opened, and the pool marks it as in use so no one else gets it.

When you "close" that connection, the pool just marks it as available
so that it can be reused.

That may be why you are seeing idle connections when you aren't
expecting them. After you have closed the connection, it's still open
- that's how a pool works - but it may show up as idle.

Larry

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Jeff Butler-2]

IIRC, Struts2 actions are not singletons.  So this code is creating a
new instance of the SqlMapClient (and it's associated connection pool)
each time you hit the web page.  They will eventually get cleaned up
by the GC, but that might take a while.

Better would be to implement ApplicationAware also, then write code like this:

SqlMapClient sqlMap = (SqlMapClient) applicationMap.get("sqlMap");
if (sqlMap == null) {
     try {
        sqlMap =
SqlMapClientBuilder.buildSqlMapClient(Resources.getResourceAsReader("sqlMaps.xml"));
       applicationMap.put("sqlMap", sqlMap);
     } catch (Exception e)  {
        e.printStackTrace();
     }
}

Make sure all other actions use this same code - a good use for a
super class :).  Then you know it's only created once.

Jeff Butler




On Mon, Oct 19, 2009 at 3:28 PM, Jim Borland <jborland@...> wrote:

>
> Here it is.  Thanks for your interest in my situation.  Using Apache Struts2
> - this is an action implementation.  The call is to "listOfArtists" in class
> "ListSwingCatAction"
>
> =============================
>
> public class ListSwingCatAction implements SessionAware
> {
>   private SqlMapClient sqlMap;
>   private SwingCatIBatisDBHandler myDBHandler;
>   private Map sessionMap;
>
>   public ListSwingCatAction()
>   {
>      try
>      {
>         sqlMap =
> SqlMapClientBuilder.buildSqlMapClient(Resources.getResourceAsReader("sqlMaps.xml"));
>      }
>      catch (Exception e)
>      {
>         e.printStackTrace();
>      }
>      myDBHandler = new SwingCatIBatisDBHandler(sqlMap);
>   }
>
>   public String listOfArtists()
>   {
>      ArrayList artists = myDBHandler.getArtistInfo();
>      sessionMap.put("artists", artists);
>      return "success";
>   }
> }
>
> =============================
>
>
> Jeff Butler-2 wrote:
>>
>> We should also see the Java code that creates the SqlMapClient.
>> Without Spring you need to make sure that only a SINGLE instance of
>> that object is created (it should be and stored either in a singleton
>> or something like a web context).
>>
>> Jeff Butler
>>
>> On Mon, Oct 19, 2009 at 2:50 PM, Larry Meadors <larry.meadors@...>
>> wrote:
>>> Can you post the sqlmapconfig.xml?
>>>
>>> On Mon, Oct 19, 2009 at 1:44 PM, Jim Borland <jborland@...>
>>> wrote:
>>>>
>>>> No, I'm not smart enough to use a DAO implementation (I've read a little
>>>> about them).  Also, I keep reading about Spring -- a whole bunch of
>>>> stuff on
>>>> it comes up when I Google on these topics.  Someday I'm going to check
>>>> into
>>>> Spring.
>>>>
>>>> My situation is very simple and it seems like plain old iBatis ought to
>>>> be
>>>> plenty for me in this application.  iBatis is supposed to take care of
>>>> all
>>>> the background stuff and just let me write mapped statements.  I'm
>>>> committed
>>>> to making iBatis work without a bunch of extra stuff.  Thanks for your
>>>> interest in my problem.
>>>>
>>>>
>>>> Warren Bell-2 wrote:
>>>>>
>>>>> Are you using any DAO implementation ? Spring? Makes things much
>>>>> simpler.
>>>>>
>>>>> Warren
>>>>>
>>>>> Jim Borland wrote:
>>>>>> I've been fighting this issue for a long time now and am quite
>>>>>> frustrated.  I
>>>>>> originally started out with just:
>>>>>>
>>>>>> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list );
>>>>>> --
>>>>>> but was getting all these <IDLE> connections.  So I tried adding
>>>>>> start/commit/end transaction statements surrounding the query.  Still
>>>>>> getting <IDLE>s so then I tried using :
>>>>>>
>>>>>> --> session = sqlMap.openSession(); -- and letting the session
>>>>>> start/commit/end the transaction.  Still got <IDLE>s.  That's when I
>>>>>> tried
>>>>>> creating, using and closing my own connection with the same sad
>>>>>> result.
>>>>>>
>>>>>> One thing Rick Wellman said was especially interesting.  Every time
>>>>>> you
>>>>>> create an instance of SqlMapClient you create an entirely new
>>>>>> connection
>>>>>> pool.  I hadn't thought about that before.  I guess the bottom line is
>>>>>> I
>>>>>> don't really understand what is happening in a connection pool.
>>>>>>  Still,
>>>>>> my
>>>>>> situation is so simple, yet the same bad outcome occurs no matter what
>>>>>> I
>>>>>> try.  Help!
>>>>>>
>>>>>>
>>>>>> Rick.Wellman wrote:
>>>>>>
>>>>>>> Since I have some time over lunch:
>>>>>>> 1) I agree with Larry's reply below
>>>>>>> 2) At the risk of embarrassing myself on this forum, see below for my
>>>>>>> reply to your comments and questions:
>>>>>>>
>>>>>>> [your-code-sample-was-here]
>>>>>>> [your-comments-were-here]
>>>>>>> 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.
>>>>>>>
>>>>>>>>> Usually, your entire application would share a single instance of
>>>>>>>>>
>>>>>>> SqlMapClient.  It matters in the sense that it is un-necessary and
>>>>>>> would, at a minimum, create an entirely new connection pool (see #3
>>>>>>> for
>>>>>>> more)
>>>>>>>
>>>>>>> (2) In the above code I use the DataSource obtained from SqlMapClient
>>>>>>> --
>>>>>>> Is there something wrong with doing this?
>>>>>>>
>>>>>>>>> Well, probably... and it is un-necessary.  Use Larry's version.
>>>>>>>>> (i.e.
>>>>>>>>>
>>>>>>> the "normal" way to use the SqlMapClient)
>>>>>>>
>>>>>>> (3) Have I somehow mis-configured the connection pool?
>>>>>>>
>>>>>>>>> I could be wrong but I still highly suspect that the connections
>>>>>>>>> are
>>>>>>>>>
>>>>>>> a result of the connection pool and it seems to me that you're not
>>>>>>> understanding the purpose of a connection pool.  i.e. You're trying
>>>>>>> to
>>>>>>> explicitly open a connection with code.  The connection pool will
>>>>>>> usually expand and contract the number of connections to the database
>>>>>>> based on the load and its configuration (which is why it is called a
>>>>>>> "pool").  You do not have "direct" control over which connection your
>>>>>>> iBatis SqlMapClient will use [nor do you probably want that].  I
>>>>>>> apologize in advance if I am way off base with this response; not my
>>>>>>> intent to offend, but rather educate.
>>>>>>>
>>>>>>> To the masses... in regards to my comment #3, is there an
>>>>>>> implementation
>>>>>>> of a "pool" which is not a pool at all but a single connection that
>>>>>>> someone can use to verify an instance like this?  Or maybe configure
>>>>>>> the
>>>>>>> "pool" to only have a size of one?  Just thinking out loud... I've
>>>>>>> never
>>>>>>> had reason to look into something like this but it seems like this
>>>>>>> question comes up every so often? (i.e. the question of connections
>>>>>>> opened via iBatis)
>>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: Larry Meadors [mailto:larry.meadors@...]
>>>>>>> Sent: Monday, October 19, 2009 12:56 PM
>>>>>>> To: user-java@...
>>>>>>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>>>>>>
>>>>>>> This looks to me like you are *way* overcomplicating this. :-)
>>>>>>>
>>>>>>> The method should be more like this:
>>>>>>>
>>>>>>> public List getArtistInfo(){
>>>>>>>   return sqlMap.queryForList("getArtistInfo", list);
>>>>>>> }
>>>>>>>
>>>>>>> Unless you have some really crazy wacky stuff going on, there should
>>>>>>> never be a need for you to deal with connections at that level.
>>>>>>>
>>>>>>> Also, what's the purpose of passing in 'list' as the second parameter
>>>>>>> there? I don't see where it would ever be non-null.
>>>>>>>
>>>>>>> Larry
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>>>>> For additional commands, e-mail: user-java-help@...
>>>>>>>
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>>>>> For additional commands, e-mail: user-java-help@...
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Thanks,
>>>>>
>>>>> Warren Bell
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>>> For additional commands, e-mail: user-java-help@...
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>> View this message in context:
>>>> http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25964382.html
>>>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>>> For additional commands, e-mail: user-java-help@...
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-java-unsubscribe@...
>>> For additional commands, e-mail: user-java-help@...
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@...
>> For additional commands, e-mail: user-java-help@...
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25965093.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@...
> For additional commands, e-mail: user-java-help@...
>
>

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

[Larry Meadors]

OK, you need to refactor that. :-)

The sqlMap should only be created once. I'm not a struts2 user, so I
can't say what the appropriate way to do that is. :-/

If you just want to go ghetto style, you could use a static variable
to hold it and initialize it when the class is loaded...but I'd really
only do that to get a proof of concept working. You should look at the
"struts2" way of doing it, or write a context listener to do it when
your application starts up.

A bit more unsolicited criticism, if you don't mind:

Unless that artist list varies by user (it doesn't look like it does)
putting it in session scope is probably not needed - you could put it
in request scope, cache it in ibatis, and share a single instance of
that list for all users - that would make your application faster and
use less memory.

Using ArrayList as ArrayList is almost never a good idea - unless you
need specific implementation features, just treat it as a List
instead.

Using e.printStackTrace() is really bad - you'd be better off just
saying something like "throw new RuntimeException(e);" because that
way the application won't just silently fail.

Larry

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

[Jim Borland]

Wow, I didn't realize that I am creating a new instance of the SqlMapClient every time I call this method.  That obviously is not a good thing.  Thank you for discovering my oversight and for your suggested fixes.  I will scratch my head for a bit and figure out how to fix this situation.  Thanks again!

Jeff Butler-2 wrote:

IIRC, Struts2 actions are not singletons.  So this code is creating a
new instance of the SqlMapClient (and it's associated connection pool)
each time you hit the web page.  They will eventually get cleaned up
by the GC, but that might take a while.

Better would be to implement ApplicationAware also, then write code like this:

SqlMapClient sqlMap = (SqlMapClient) applicationMap.get("sqlMap");
if (sqlMap == null) {
     try {
        sqlMap =
SqlMapClientBuilder.buildSqlMapClient(Resources.getResourceAsReader("sqlMaps.xml"));
       applicationMap.put("sqlMap", sqlMap);
     } catch (Exception e)  {
        e.printStackTrace();
     }
}

Make sure all other actions use this same code - a good use for a
super class :).  Then you know it's only created once.

Jeff Butler




On Mon, Oct 19, 2009 at 3:28 PM, Jim Borland <[e-mail]> wrote:
>
> Here it is.  Thanks for your interest in my situation.  Using Apache Struts2
> - this is an action implementation.  The call is to "listOfArtists" in class
> "ListSwingCatAction"
>
> =============================
>
> public class ListSwingCatAction implements SessionAware
> {
>   private SqlMapClient sqlMap;
>   private SwingCatIBatisDBHandler myDBHandler;
>   private Map sessionMap;
>
>   public ListSwingCatAction()
>   {
>      try
>      {
>         sqlMap =
> SqlMapClientBuilder.buildSqlMapClient(Resources.getResourceAsReader("sqlMaps.xml"));
>      }
>      catch (Exception e)
>      {
>         e.printStackTrace();
>      }
>      myDBHandler = new SwingCatIBatisDBHandler(sqlMap);
>   }
>
>   public String listOfArtists()
>   {
>      ArrayList artists = myDBHandler.getArtistInfo();
>      sessionMap.put("artists", artists);
>      return "success";
>   }
> }
>
> =============================
>
>
> Jeff Butler-2 wrote:
>>
>> We should also see the Java code that creates the SqlMapClient.
>> Without Spring you need to make sure that only a SINGLE instance of
>> that object is created (it should be and stored either in a singleton
>> or something like a web context).
>>
>> Jeff Butler
>>
>> On Mon, Oct 19, 2009 at 2:50 PM, Larry Meadors <[e-mail]>
>> wrote:
>>> Can you post the sqlmapconfig.xml?
>>>
>>> On Mon, Oct 19, 2009 at 1:44 PM, Jim Borland <[e-mail]>
>>> wrote:
>>>>
>>>> No, I'm not smart enough to use a DAO implementation (I've read a little
>>>> about them).  Also, I keep reading about Spring -- a whole bunch of
>>>> stuff on
>>>> it comes up when I Google on these topics.  Someday I'm going to check
>>>> into
>>>> Spring.
>>>>
>>>> My situation is very simple and it seems like plain old iBatis ought to
>>>> be
>>>> plenty for me in this application.  iBatis is supposed to take care of
>>>> all
>>>> the background stuff and just let me write mapped statements.  I'm
>>>> committed
>>>> to making iBatis work without a bunch of extra stuff.  Thanks for your
>>>> interest in my problem.
>>>>
>>>>
>>>> Warren Bell-2 wrote:
>>>>>
>>>>> Are you using any DAO implementation ? Spring? Makes things much
>>>>> simpler.
>>>>>
>>>>> Warren
>>>>>
>>>>> Jim Borland wrote:
>>>>>> I've been fighting this issue for a long time now and am quite
>>>>>> frustrated.  I
>>>>>> originally started out with just:
>>>>>>
>>>>>> --> artists = (ArrayList) sqlMap.queryForList("getArtistInfo", list );
>>>>>> --
>>>>>> but was getting all these <IDLE> connections.  So I tried adding
>>>>>> start/commit/end transaction statements surrounding the query.  Still
>>>>>> getting <IDLE>s so then I tried using :
>>>>>>
>>>>>> --> session = sqlMap.openSession(); -- and letting the session
>>>>>> start/commit/end the transaction.  Still got <IDLE>s.  That's when I
>>>>>> tried
>>>>>> creating, using and closing my own connection with the same sad
>>>>>> result.
>>>>>>
>>>>>> One thing Rick Wellman said was especially interesting.  Every time
>>>>>> you
>>>>>> create an instance of SqlMapClient you create an entirely new
>>>>>> connection
>>>>>> pool.  I hadn't thought about that before.  I guess the bottom line is
>>>>>> I
>>>>>> don't really understand what is happening in a connection pool.
>>>>>>  Still,
>>>>>> my
>>>>>> situation is so simple, yet the same bad outcome occurs no matter what
>>>>>> I
>>>>>> try.  Help!
>>>>>>
>>>>>>
>>>>>> Rick.Wellman wrote:
>>>>>>
>>>>>>> Since I have some time over lunch:
>>>>>>> 1) I agree with Larry's reply below
>>>>>>> 2) At the risk of embarrassing myself on this forum, see below for my
>>>>>>> reply to your comments and questions:
>>>>>>>
>>>>>>> [your-code-sample-was-here]
>>>>>>> [your-comments-were-here]
>>>>>>> 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.
>>>>>>>
>>>>>>>>> Usually, your entire application would share a single instance of
>>>>>>>>>
>>>>>>> SqlMapClient.  It matters in the sense that it is un-necessary and
>>>>>>> would, at a minimum, create an entirely new connection pool (see #3
>>>>>>> for
>>>>>>> more)
>>>>>>>
>>>>>>> (2) In the above code I use the DataSource obtained from SqlMapClient
>>>>>>> --
>>>>>>> Is there something wrong with doing this?
>>>>>>>
>>>>>>>>> Well, probably... and it is un-necessary.  Use Larry's version.
>>>>>>>>> (i.e.
>>>>>>>>>
>>>>>>> the "normal" way to use the SqlMapClient)
>>>>>>>
>>>>>>> (3) Have I somehow mis-configured the connection pool?
>>>>>>>
>>>>>>>>> I could be wrong but I still highly suspect that the connections
>>>>>>>>> are
>>>>>>>>>
>>>>>>> a result of the connection pool and it seems to me that you're not
>>>>>>> understanding the purpose of a connection pool.  i.e. You're trying
>>>>>>> to
>>>>>>> explicitly open a connection with code.  The connection pool will
>>>>>>> usually expand and contract the number of connections to the database
>>>>>>> based on the load and its configuration (which is why it is called a
>>>>>>> "pool").  You do not have "direct" control over which connection your
>>>>>>> iBatis SqlMapClient will use [nor do you probably want that].  I
>>>>>>> apologize in advance if I am way off base with this response; not my
>>>>>>> intent to offend, but rather educate.
>>>>>>>
>>>>>>> To the masses... in regards to my comment #3, is there an
>>>>>>> implementation
>>>>>>> of a "pool" which is not a pool at all but a single connection that
>>>>>>> someone can use to verify an instance like this?  Or maybe configure
>>>>>>> the
>>>>>>> "pool" to only have a size of one?  Just thinking out loud... I've
>>>>>>> never
>>>>>>> had reason to look into something like this but it seems like this
>>>>>>> question comes up every so often? (i.e. the question of connections
>>>>>>> opened via iBatis)
>>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: Larry Meadors [mailto:[e-mail]]
>>>>>>> Sent: Monday, October 19, 2009 12:56 PM
>>>>>>> To: [e-mail]
>>>>>>> Subject: Re: iBatis - Connections to PostgreSQL Not Closing
>>>>>>>
>>>>>>> This looks to me like you are *way* overcomplicating this. :-)
>>>>>>>
>>>>>>> The method should be more like this:
>>>>>>>
>>>>>>> public List getArtistInfo(){
>>>>>>>   return sqlMap.queryForList("getArtistInfo", list);
>>>>>>> }
>>>>>>>
>>>>>>> Unless you have some really crazy wacky stuff going on, there should
>>>>>>> never be a need for you to deal with connections at that level.
>>>>>>>
>>>>>>> Also, what's the purpose of passing in 'list' as the second parameter
>>>>>>> there? I don't see where it would ever be non-null.
>>>>>>>
>>>>>>> Larry
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: [e-mail]
>>>>>>> For additional commands, e-mail: [e-mail]
>>>>>>>
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: [e-mail]
>>>>>>> For additional commands, e-mail: [e-mail]
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Thanks,
>>>>>
>>>>> Warren Bell
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [e-mail]
>>>>> For additional commands, e-mail: [e-mail]
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>> View this message in context:
>>>> http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25964382.html
>>>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [e-mail]
>>>> For additional commands, e-mail: [e-mail]
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [e-mail]
>>> For additional commands, e-mail: [e-mail]
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [e-mail]
>> For additional commands, e-mail: [e-mail]
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25965093.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [e-mail]
> For additional commands, e-mail: [e-mail]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

[Jim Borland]

Yesterday I was the beneficiary of a flurry of postings and off-line emails regarding a database connection issue using iBatis.  I am overflowing with gratitude because your efforts helped me understand the situation and solve the problem.  I had spent several weeks wrestling with this and gone down many dead-ends in futile attempts to figure out what was wrong.  Here are the three lessons I learned yesterday:

(1) Every time you generate an instance of SqlMapClient, you create an entirely new connection pool.  So, your entire application should share a single instance of SqlMapClient.

(2) My application generates a new instance of SqlMapClient every time it runs (thank you so much for pointing this out to me -- I had no idea).

(3) Connection pools are cool and not such a mystery after all, now that you folks have explained how they work.

All the bizarre behavior I experience in regard to my database connections can be explained by the fact that multiple connection pools are being generated, and they just hang around while waiting for the Garbage Collector to clean things up.

Many of you took time yesterday to explain how connection pools work, and that helped clear up much of the mystery for me.  I've gone back through all the postings and emails to compile a summary of what I learned, and I'd like to share that with you.  Thanks again to all of you who contributed to this effort.

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

How a database connection pool works -

A connection pool is simply a collection of connection objects that are kept "open" as a performance optimization technique (they show up as <IDLE> processes in PostgreSQL's psql --> "select * from pg_stat_activity;").  Creating network connections to a database server can be an expensive operation (in terms of time), so web applications create a "pool" of connections that are left "open," and then used as needed.

When a program asks for a new connection, it gets an already opened connection from the pool instead of waiting for a new one to be opened, and the pool marks it as in use so no one else gets it.  When you "close" that connection, the pool just marks it as available so that it can be reused.  This is why we see <IDLE> connections when not expecting them.  After you have closed the connection, it's still open -– that's how a pool works –- but it shows up as <IDLE>.

The connection pool will usually expand and contract the number of connections to the database based on the load and how it's configured (which is why it is called a "pool").  The pool will "grow" under heavy use, and then when the load decreases it will "shrink" back to its maxIdle.

Connection pools usually have three configurable parameters:

(a)  minIdle is the smallest number of <IDLE> connections you will ever see being held open by the pool.  If this number was two, you would always have at least two <IDLE> connections being held open in the pool.  If it were zero, then all the <IDLE> connections in the pool may be closed.  If the number were two, on startup, as soon as the pool is created, you will immediately see two additional <IDLE> connections.  MinIdle should be at least one so that there is always a connection ready to go; otherwise, the first user will probably notice a delay in their HTTP response.  

(b)  maxActive - you will never have more "active" connections than this because you don't want a "runaway" program (a database client) to chew up all your database connections.

(c)  maxIdle is a way to communicate when the pool can "give up" connections after it has grown and then shrunk back down.  As the pool shrinks, and more active connections go <IDLE>, if this number is exceeded the excess connections will automatically close.  So, maxIdle is the largest number of <IDLE> connections ever held open by the pool.

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

I hope I got this right.  As I said, these are your words, not mine, and I thank you all for the contributions.

Once again, thank you VERY MUCH for helping me during my search for answers.  You folks are the greatest!



Wow, I didn't realize that I am creating a new instance of the SqlMapClient every time I call this method.  That obviously is not a good thing.  Thank you for discovering my oversight and for your suggested fixes.  I will scratch my head for a bit and figure out how to fix this situation.  Thanks again!


IIRC, Struts2 actions are not singletons.  So this code is creating a
new instance of the SqlMapClient (and it's associated connection pool)
each time you hit the web page.  They will eventually get cleaned up
by the GC, but that might take a while.