ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "vi am" <vi...@hotmail.com>
Subject RE: Connection leak
Date Tue, 07 Jun 2005 23:01:34 GMT
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" <vi_am@hotmail.com>
>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: <erlend.bjorge@aftenposten.no>
>>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
>
>



Mime
View raw message