From: Brandon Goodin <[EMAIL PROTECTED]>
Reply-To: Brandon Goodin <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]> 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" <[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
> >
> >
>
>
>
