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 > > > > > > >
