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 Wed, 08 Jun 2005 05:03:06 GMT
Brandon: I will re-read the documentation.  The JSP code I posted was 
written purely to evaluate iBATIS.  But, that is not the point.  I couldn't 
find in the documentation if/when the connections are closed.  Can anyone 
clarify regarding connections please.

erlend.bjorge:  I got the tip to generate DEBUG logging from your response.  
Thank you.  Would you please post sample code or email it to me at  
vi_am@hotmail.com ?  I would rather use DBCP than SIMPLE... so that 
connections are used from a pool.

Once I find the answers, I will be happy to write up some documentation 
regarding connection pools and also on logging.  Logging is not that simple 
using Tomcat.... it seems to have issues regarding the location of 
log4j.properties.

I really like the simplicity of iBATIS... and am eager to recommend this 
tool to my team after I get over the initial bumps.

Thank you.



>From: Brandon Goodin <brandon.goodin@gmail.com>
>Reply-To: Brandon Goodin <brandon.goodin@gmail.com>
>To: ibatis-user-java@incubator.apache.org
>Subject: Re: Connection leak
>Date: Tue, 7 Jun 2005 19:40:40 -0600
>
>I hope coding in the JSP is purely for quick and dirty evaluation and
>not your common pattern. That's a pretty nasty approach to development
>if it is. Also, you are loading up the sqlmap each time you access the
>page... another NoNo. Have you read the tutorial yet? I think you may
>need to read it.
>
>http://prdownloads.sourceforge.net/ibatisdb/iBATIS-SqlMaps-2-Tutorial.pdf?download
>
>Brandon
>
>On 6/7/05, vi am <vi_am@hotmail.com> wrote:
> > 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