ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Spring, Ibatis, and DBCP commons pooling
Date Fri, 14 Sep 2007 11:06:35 GMT
If you're using JNDI datasources, then it's likely that you'll need to
configure ping queries in the container that's managing the connection pool.

What's the container?  I'm guessing you're using Tomcat, but it's not clear.

Jeff Butler


On 9/14/07, Tracey Annison <tannison@trisystems.co.uk> wrote:
>
> Heya
>
> Hmmm, yes... But isn't that functionally the same as what we have here
> in our context.xml :
>
>        <Resource name="jdbc/imacs"
>                  auth="Container"
>                      type="javax.sql.DataSource"
>                  driverClassName="XXX"
>                  url="XXX"
>                  username="XXX"
>                  password="XXX" />
>
>        <ResourceParams name="jdbc/imacs">
>                <parameter>
>                        <name>factory</name>
>                        <value>
>
> org.apache.commons.dbcp.BasicDataSourceFactory
>                        </value>
>                </parameter>
>                <parameter>
>                        <name>maxActive</name>
>                        <value>10</value>
>                </parameter>
>                <parameter>
>                        <name>maxIdle</name>
>                        <value>2</value>
>                </parameter>
>                <parameter>
>                        <name>maxWait</name>
>                        <value>100</value>
>                </parameter>
>                <parameter>
>                        <name>testOnBorrow</name>
>                        <value>true</value>
>                </parameter>
>                <parameter>
>                        <name>validationQuery</name>
>                        <value>select 1</value>
>                </parameter>
>        </ResourceParams>
>
> Which is giving the bad results that we see...
>
> Cheers
> Tracey Annison
>
> -----Original Message-----
> From: charlie bird [mailto:zebthecat@yahoo.co.uk]
> Sent: 13 September 2007 16:57
> To: user-java@ibatis.apache.org
> Subject: RE: Spring, Ibatis, and DBCP commons pooling
>
> Don't know if this'll help but we always put the dbcp config in the
> Apache application context xml and NOT in the app's Resouce config.
>
> I think that the properties you're looking for are:
> testOnBorrow
> testOnReturn
> testWhileIdle
> validationQuery
>
> See here for more info:
> http://commons.apache.org/dbcp/configuration.html
>
> Something like this maybe:
>
> <Resource name="jdbc/creditSubscriptions"
> auth="Container" type="javax.sql.DataSource"
>               maxActive="100" maxIdle="30"
> maxWait="10000" username="sa" password="password"
> driverClassName="class"
> removeAbandoned="true" removeAbandonedTimeout="60"
> logAbandoned="true"
> url="jdbc:somurl"
> validationQuery="select 'hello'"
> testOnBorrow="true"
> testOnReturn="true"
> testWhileIdle="true"
> />
>
>
>
>
> --- Tracey Annison <tannison@trisystems.co.uk> wrote:
>
> > But this is a setup for a SimpleDataSource, with Ibatis pooling, and
> > not using the apache commons DBCP pooling at all, isn't it?
> >
> > You see, we had it set up like this, with settings that we thought
> > were a hangover from before the apace commons poling was enabled, and
> > we saw exactly the same problems... it seemed to us that having pool
> > settings here, and in the context.xml for apache, was a mistake... ?
> >
> > Cheers
> > Tracey Annison
> >
> >
> >
> >   _____
> >
> > From: Meindert [mailto:meindert@pastelebusiness.com]
> > Sent: 13 September 2007 15:06
> > To: user-java@ibatis.apache.org
> > Subject: RE: Spring, Ibatis, and DBCP commons pooling
> >
> >
> >
> > I would think you will need to ping the connection before you can use
> > it.
> >
> >
> >
> > <transactionManager type="JDBC">
> >
> >     <dataSource type="SIMPLE">
> >
> >       <property value="${driver}"
> > name="JDBC.Driver"/>
> >
> >       <property value="${url}"
> > name="JDBC.ConnectionURL"/>
> >
> >       <property value="${username}"
> > name="JDBC.Username"/>
> >
> >       <property value="${password}"
> > name="JDBC.Password"/>
> >
> >       <property value="15"
> > name="Pool.MaximumActiveConnections"/>
> >
> >       <property value="15"
> > name="Pool.MaximumIdleConnections"/>
> >
> >       <property value="1000"
> > name="Pool.MaximumWait"/>
> >
> >       <property name="Pool.PingQuery" value="SELECT
> > 1 FROM Company"/>
> >
> >       <property name="Pool.PingEnabled" value="true"
> > />
> >
> >       <property name="Pool.PingConnectionsOlderThan"
> > value="1000000" />
> >
> >     </dataSource>
> >
> >   </transactionManager>
> >
> >
> >
> >   _____
> >
> > From: Tracey Annison
> > [mailto:tannison@trisystems.co.uk]
> > Sent: Thursday, September 13, 2007 3:58 PM
> > To: user-java@ibatis.apache.org
> > Subject: Spring, Ibatis, and DBCP commons pooling
> >
> >
> >
> > We are using Java with Spring to access AS/400 databases via Ibatis
> > and the SQLMapClient, and pooling connections via the Apache Commons
> > DBCP.
> > We have a problem when the application remains running, but the AS/400
>
> > connection is lost, due to a closed job or an IPL or whatever. After
> > this happens, the first call on the database fails, though subsequent
> > calls succeed.
> >
> > The odd thing is that it seems to be failing while trying to return
> > the bad connection, presumably as part of some cleanup operation,
> > which would hopefully be followed by an attempt to reconnect? It looks
>
> > to me as though the Spring DataSourceUtils finds that the connection
> > is dead, and tries to return it to the pool (presumably prior to
> > obtaining a less dead one?) and clashes with the Tomcat DBCP that has
> > already done so.
> > But I cannot seem to find a way of seeing what is going on during this
>
> > process, as I can't find out how to log these actions in any more
> > detail.
> >
> > So I don't know how to correct this issue... can anyone help me?
> > We are setting up an Ibatis DataSource in Spring like this :
> > [CODE]<bean id="iseriesJdbcDataSource"
> >
> class="org.springframework.jndi.JndiObjectFactoryBean">
> > <property name="jndiName">
> > <value>java:comp/env/jdbc/host</value>
> > </property>
> > </bean>
> > <bean id="sqlMapClient"
> >
> class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
> > <property name="configLocation">
> > <value>/WEB-INF/sql-map-config.xml</value>
> > </property>
> > <property name="useTransactionAwareDataSource">
> > <value>true</value>
> > </property>
> > <property name="dataSource">
> > <ref bean="iseriesJdbcDataSource"/>
> > </property>
> > </bean>
> > <bean id="sqlMapClientTemplate"
> >
> class="org.springframework.orm.ibatis.SqlMapClientTemplate">
> > <property name="sqlMapClient">
> > <ref bean="sqlMapClient"/>
> > </property>
> > </bean>
> > <bean id="ourDAO"
> > class="uk.co.XXX.our.dao.OurIbatisDAO">
> > <property name="sqlMapClientTemplate"> <ref
> > bean="sqlMapClientTemplate"/> </property> </bean>[/CODE] Which uses
> > the following settings in the Ibatis sql-map-client.xml :
> > [CODE] <transactionManager type="JDBC"> <dataSource type="JNDI">
> > <property name="DataSource"
> > value="java:comp/env/jdbc/host" />
> > </dataSource>
> > </transactionManager>[/CODE]
>
> > And also these Apache DBCP settings in context.xml :
> > [CODE]<Resource name="jdbc/host"
> > auth="Container"
> > type="javax.sql.DataSource"
> >
> driverClassName="com.ibm.as400.access.AS400JDBCDriver"
> >
> url="jdbc:as400://999.999.999.999/HOSTLIB;prompt=false;naming=system;dat
> > eformat=iso"
> > username="HOSTUSER"
> > password="HOSTPWD" />
> > <ResourceParams name="jdbc/host">
> > <parameter>
> > <name>factory</name>
> >
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> > </parameter>
> > <parameter>
> > <name>maxActive</name>
> > <value>10</value>
> > </parameter>
> > <parameter>
> > <name>maxIdle</name>
> > <value>2</value>
> > </parameter>
> > <parameter>
> > <name>maxWait</name>
> > <value>100</value>
> > </parameter>
> > <parameter>
> > <name>testOnBorrow</name>
> > <value>true</value>
> > </parameter>
> > <parameter>
> > <name>validationQuery</name>
> > <value>select 1</value>
> > </parameter>
> > </ResourceParams>[/CODE]
> > Using these log4J settings to get the debugs :
> > [CODE]log4j.category.com.ibatis = DEBUG, file
> > log4j.category.org.apache = DEBUG, file
> > log4j.category.org.springframework = DEBUG, file[/CODE] We see logging
>
> > like this for a successful result, AS/400 present, all is well :
> > [CODE]11:43:45,271
> > uk.co.XXX.our.soap.OurService.getItem():359
> === message truncated ===
>
>
>
>      ___________________________________________________________
> Want ideas for reducing your carbon footprint? Visit Yahoo! For Good
> http://uk.promotions.yahoo.com/forgood/environment.html
>
>
>
> ----------------------------------------------------------------------
> The information in this email is confidential and may be legally
> privileged.
> It is intended solely for the addressee. Access to this email by
> anyone else is unauthorised. If you are not the intended recipient,
> any disclosure, copying, distribution, or any action taken or omitted
> to be taken in reliance on it, is prohibited and may be unlawful.
> TriSystems Ltd. cannot accept liability for statements made which are
> clearly
> the sender's own.
>
>
>

Mime
View raw message