Here is the JSP page I was using to test the connections:
************************************************
<%@ page import="com.ibatis.sqlmap.client.*" %>
<%@ page import="com.ibatis.common.resources.*" %>
<%@ page import="java.io.Reader" %>
<%@ page import="java.util.*" %>
<%@ page import="vj.ibatis.Account" %>
<%
String resource = "batis_maps/sql-map-config.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
List list = sqlMap.queryForList("getAccountByUsername", "j");
Iterator iter = list.iterator();
while(iter.hasNext()) {
Account account = (Account) iter.next();
if(account == null)
out.println("<p>account is null");
else {
out.println("<p>first Name = " + account.getFirstName()
);
out.println("<br>last Name = " + account.getLastName()
);
}
}
iter = null;
list = null;
%>
************************************************
From: "vi am" <[EMAIL PROTECTED]>
Reply-To: ibatis-user-java@incubator.apache.org
To: ibatis-user-java@incubator.apache.org
Subject: RE: Connection leak
Date: Tue, 07 Jun 2005 18:50:06 -0400
By mistake, there are two threads for this topic... I will try to use only
this thread now.
I turned on the DEBUG feature... and the seemingly inconsistent behavior
is causing confusion. If I use "SIMPLE", on one try the connections were
getting created and closed. I could verify this using a SQL query against
the v$ tables in Oracle database. On another try, "DBCP" worked
perfectly.... promptly returning connections to the pool. However, I
couldn't reproduce the same on my next try!
If I had, <dataSource type="SIMPLE">, here is the DEBUG output (which is
the source of confusion):
DEBUG [http8090-Processor4] - Created connection 6189088.
DEBUG [http8090-Processor4] - {conn-100000} Connection
DEBUG [http8090-Processor4] - {pstm-100001} PreparedStatement: select
first_name, last_name from vj_batis
DEBUG [http8090-Processor4] - {pstm-100001} Parameters: []
DEBUG [http8090-Processor4] - {pstm-100001} Types: []
DEBUG [http8090-Processor4] - {rset-100002} ResultSet
DEBUG [http8090-Processor4] - {rset-100002} Header: [FIRST_NAME, LAST_NAME]
DEBUG [http8090-Processor4] - {rset-100002} Result: [jessica, simpson]
DEBUG [http8090-Processor4] - {rset-100002} Result: [paris, hilton]
DEBUG [http8090-Processor4] - Returned connection 6189088 to pool.
**** I was NOT expecting "Returned Connection to pool".
*************************************************
If I had, <dataSource type="DBCP">, here is the DEBUG output (again,
source of confusion):
WebappClassLoader: Resource
'/WEB-INF/classes/batis_maps/sql-map-config.xml'
was modified; Date is now: Tue Jun 07 15:28:08 PDT 2005 Was: Tue Jun 07
15:26:05 PDT 2005
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all
connections.
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all
connections.
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all
connections.
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all
connections.
DEBUG [http8090-Processor3] - {conn-100000} Connection
DEBUG [http8090-Processor3] - {pstm-100001} PreparedStatement: select
first_name, last_name from vj_batis
DEBUG [http8090-Processor3] - {pstm-100001} Parameters: []
DEBUG [http8090-Processor3] - {pstm-100001} Types: []
DEBUG [http8090-Processor3] - {rset-100002} ResultSet
DEBUG [http8090-Processor3] - {rset-100002} Header: [FIRST_NAME, LAST_NAME]
DEBUG [http8090-Processor3] - {rset-100002} Result: [jessica, simpson]
DEBUG [http8090-Processor3] - {rset-100002} Result: [paris, hilton]
What happened to returning connection to the pool? There were no extra
connections created in the database. This means... even if it was DBCP,
a connection was created and closed. But, the DEBUG did not show this !?
Here are the contents of "sql-map-config.xml" file:
***********************************************
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd";>
<sqlMapConfig>
<properties resource="properties/database.properties"/>
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
maxSessions="50"
maxTransactions="30"
maxRequests="2"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
<property name="Pool.MaximumActiveConnections" value="5"/>
<property name="Pool.MaximumIdleConnections" value="2"/>
<property name="Pool.MaximumCheckoutTime" value="60000"/>
<property name="Pool.TimeToWait" value="7000"/>
<property name="Pool.PingQuery" value="select 1 as ISPINGWORKING
from dual"/>
<property name="Pool.PingEnabled" value="true"/>
<property name="Pool.PingConnectionsOlderThan" value="1000"/>
<property name="Pool.PingConnectionsNotUsedFor" value="1000"/>
</dataSource>
</transactionManager>
<sqlMap resource="batis_maps/Account.xml"/>
<sqlMap resource="batis_maps/Product.xml"/>
</sqlMapConfig>
***********************************************
Any ideas on what I might be doing wrong? Thanks for your help.
From: <[EMAIL PROTECTED]>
Reply-To: ibatis-user-java@incubator.apache.org
To: <ibatis-user-java@incubator.apache.org>
Subject: SV: Connection leak
Date: Tue, 7 Jun 2005 09:20:37 +0200
Hi there!
>Is there a way to limit the maximum number of connections (at any given
>time) Apache's DBCP pool?
We haven't used that one, but have you tried the simple datasource ?
Is it the same behaviour ?
Our config look like this:
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
<property name="Pool.MaximumActiveConnections" value="30"/>
<property name="Pool.MaximumIdleConnections" value="0"/>
<property name="Pool.MaximumCheckoutTime" value="60000"/>
<property name="Pool.TimeToWait" value="7000"/>
<property name="Pool.PingQuery" value="select 1 as
ISPINGWORKING from dual"/>
<property name="Pool.PingEnabled" value="true"/>
<property name="Pool.PingConnectionsOlderThan" value="1000"/>
<property name="Pool.PingConnectionsNotUsedFor"
value="1000"/>
We use Oracle too, 9.2 ... With the classes12.jar driver.
With the ping stuff .... You will also get something like this when you
are in debug mode for log4j:
DEBUG 09:02:43,041 [main] com.ibatis.common.jdbc.SimpleDataSource -
Testing connection 25942001...
DEBUG 09:02:43,051 [main] com.ibatis.common.jdbc.SimpleDataSource -
Connection 25942001 is GOOD!
DEBUG 09:02:43,061 [main] com.ibatis.common.jdbc.SimpleDataSource - Closed
connection 25942001.
When you have explisit transaction handling:
sqlMap.startTransaction();
sqlMap.commitTransaction();
sqlMap.endTransaction();
So when you get closed connection, it's all cleaned up.
---
When you have implisit transaction handling... The connection get closed
automatically.
Don't you see that in debug mode with log4j ??
--
I also recommend you to e.g use a dynamic proxy ... Then you can call
sqlMap.endTransaction() in a finally
clause in the invoke() method and you make sure the connection is returned
independently if you use
explisit and implisitt transaction handling ... And you don't have to call
endTransaction() yourself.
AND you also can introduce automatically rollback when an exception
happens in your DAO class e.g. very
nice feature... So you don't have to program a rollback yourself each
time, when you have a write transaction.
Let me know if you want some code examples of dynamic proxy.
Take care,
Erlend Bjørge
