Thanks for the pointers Clinton: I'm not needing lazy loading I don't think, so am running with a pretty minimal configuration - I've left the dataSource element empty. As near as I can tell, the Spring dataSource settings for the dbcp pool (eg. maxActive, maxIdle, etc) are picked up from spring. I've got this up and running now - hopefully this will fix the Connection.close() issue. Thanks a bunch.
Regards, Brian Parkinson --- x8 snip iBatis: <sqlMapConfig> <transactionManager type="EXTERNAL"> <dataSource type="DBCP"> </dataSource> </transactionManager> <sqlMap resource="com/ecobee/foundation/dao/ibatis/SqlMapAlert.xml" /> .. etc. </sqlMapConfig> Spring: <bean id="mapConfig" class="org.springframework.core.io.ClassPathResource"> <constructor-arg> <value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value> </constructor-arg> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql:///ecobee" /> <property name="username" value="XXX" /> <property name="password" value="YYY" /> <property name="initialSize" value="10" /> <property name="maxActive" value="100" /> <property name="maxIdle" value="16" /> <property name="maxWait" value="2000" /> </bean> <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" ref="mapConfig" /> </bean> ________________________________ From: Clinton Begin [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 5:05 PM To: user-java@ibatis.apache.org Subject: Re: Question: "Connection.close has already been closed" Yes, and fill in the DBCP DataSource element with the appropriate values if you plan to use lazy loading... Clinton. On Mon, Aug 18, 2008 at 2:54 PM, Brian Parkinson <[EMAIL PROTECTED]> wrote: Clinton writes: "Make sure the iBATIS TransactionManager is set to EXTERNAL (in SqlMapConfig.xml or equivalent). " Aha. I had no TransactionManager element defined in my SqlMapConfig. Whoops? I was using Spring to configure the datasource: <bean id="mapConfig" class="org.springframework.core.io.ClassPathResource"> <constructor-arg> <value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value> </constructor-arg> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql:///ecobee" /> <property name="username" value="XXX" /> <property name="password" value="YYY" /> <property name="initialSize" value="10" /> <property name="maxActive" value="64" /> <property name="maxIdle" value="16" /> <property name="maxWait" value="1000" /> </bean> <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" ref="mapConfig" /> </bean> but I had no TransactionManager defined in my SqlMapConfig.xml file. Am I correct in assuming that I just need to add: <transactionManager type="EXTERNAL"> <dataSource type="DBCP"> </dataSource> </transactionManager> to my SqlMapConfig file? If so, should I move the properly declarations from the spring bean (id="dataSource") into the SqlMapConfig file (under the transactionManager) element? Any advice on best practices appreciated - the configuraiton of the datasource, spring, transaction manager, etc. isn't completely clear to me. Cheers, Brian Parkinson... ________________________________ From: Clinton Begin [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 4:19 PM To: user-java@ibatis.apache.org Subject: Re: Question: "Connection.close has already been closed" Based on the stack trace, it doesn't look like iBATIS ever even gets the connection... so this looks like a Spring/DBCP related issue. That said, if iBATIS prematurely closed the connection and DBCP didn't check the connection for validity, that would cause this problem. But the root cause would be why iBATIS is closing the connection. Make sure the iBATIS TransactionManager is set to EXTERNAL (in SqlMapConfig.xml or equivalent). Clinton On Mon, Aug 18, 2008 at 2:13 PM, Brian Parkinson <[EMAIL PROTECTED]> wrote: Hi all: I'm using iBatis 2.3.3.720 and Spring and dbcp 1.2.2. All seems to be well, but sometimes I see an exception related to "Connection.close() has already been called". When this happens, the server is pretty much borked - any new database access results in the exception. I never call close() in my code - unsure what's going on here. A quick inspection of my logs didn't reveal anything... Does anyone have any clues? Any help is appreciated. <bean id="mapConfig" class="org.springframework.core.io.ClassPathResource"> <constructor-arg> <value>com/ecobee/foundation/dao/ibatis/SqlMapConfig.xml</value> </constructor-arg> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql:///ecobee" /> <property name="username" value="ecobee" /> <property name="password" value="ecobee" /> <property name="initialSize" value="10" /> <property name="maxActive" value="64" /> <property name="maxIdle" value="16" /> <property name="maxWait" value="1000" /> </bean> <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" ref="mapConfig" /> </bean> <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" > <property name="dataSource" ref="dataSource"/> </bean> <tx:advice id="txAdvice" transaction-manager="txManager"> <tx:attributes> <tx:method name="get*" read-only="true" /> <tx:method name="*" /> </tx:attributes> </tx:advice> <aop:config> <aop:pointcut id="daoServiceOperation" expression="execution(* com.ecobee.foundation.dao.ibatis.*.*(..))" /> <aop:advisor advice-ref="txAdvice" pointcut-ref="daoServiceOperation" /> </aop:config> Thanks. Brian Parkinson ----------- x8 snip ERROR 15:45:16:428 Error selecting user from dataabase. {foundation.schedule.DbConnectionPing.executeTask} Message: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state. Trace org.springframework.transaction.CannotCreateTransactionException: org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin (DataSourceTransactionManager.java:238) org.springframework.transaction.support.AbstractPlatformTransactionManag er.getTransaction(AbstractPlatformTransactionManager.java:377) org.springframework.transaction.interceptor.TransactionAspectSupport.cre ateTransactionIfNecessary(TransactionAspectSupport.java:261) org.springframework.transaction.interceptor.TransactionInterceptor.invok e(TransactionInterceptor.java:101) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref lectiveMethodInvocation.java:171) org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(E xposeInvocationInterceptor.java:89) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref lectiveMethodInvocation.java:171) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAo pProxy.java:204) $Proxy5.getUser(Unknown Source) >>> com.ecobee.foundation.schedule.DbConnectionPing.executeTask(DbConnection Ping.java:27) com.ecobee.foundation.schedule.ScheduledService.executeSchedule(Schedule dService.java:38) sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) java.lang.reflect.Method.invoke(Method.java:597) org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:275) org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean $MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.ja va:272) org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBea n.java:86) org.quartz.core.JobRunShell.run(JobRunShell.java:202) org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java :529) Message: Connection.close() has already been called. Invalid operation in this state. Nested exception trace com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888) com.mysql.jdbc.Connection.getMutex(Connection.java:3728) com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5365) org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingCon nection.java:331) org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.set AutoCommit(PoolingDataSource.java:317) org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin (DataSourceTransactionManager.java:221) org.springframework.transaction.support.AbstractPlatformTransactionManag er.getTransaction(AbstractPlatformTransactionManager.java:377) org.springframework.transaction.interceptor.TransactionAspectSupport.cre ateTransactionIfNecessary(TransactionAspectSupport.java:261) org.springframework.transaction.interceptor.TransactionInterceptor.invok e(TransactionInterceptor.java:101) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref lectiveMethodInvocation.java:171) org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(E xposeInvocationInterceptor.java:89) org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(Ref lectiveMethodInvocation.java:171) org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAo pProxy.java:204) $Proxy5.getUser(Unknown Source) >>> com.ecobee.foundation.schedule.DbConnectionPing.executeTask(DbConnection Ping.java:27) com.ecobee.foundation.schedule.ScheduledService.executeSchedule(Schedule dService.java:38) sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source) sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) java.lang.reflect.Method.invoke(Method.java:597) org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:275) org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean $MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.ja va:272) org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBea n.java:86) org.quartz.core.JobRunShell.run(JobRunShell.java:202) org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java :529)
