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:[email protected]]
> Sent: Saturday, October 17, 2009 10:04 PM
> To: [email protected]
> 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: [email protected]
> For additional commands, e-mail: [email protected]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
--
View this message in context:
http://www.nabble.com/iBatis---Connections-to-PostgreSQL-Not-Closing-tp25943619p25962028.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
