ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Connection leak
Date Wed, 08 Jun 2005 01:40:40 GMT
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