spring整合tomcat jndi数据源报Could not close JDBC Connection

Cnbaby

　　详细错误信息如下：
org.springframework.jdbc.datasource.DataSourceUtils -564890472 [DefaultQuartzScheduler_Worker-2] ERROR org.springframework.jdbc.datasource.DataSourceUtils  - Could not close JDBC Connection
java.sql.SQLException: Already closed.
at org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.java:114)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
at org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:286)
at org.springframework.jdbc.datasource.DataSourceUtils.releaseConnection(DataSourceUtils.java:247)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:292)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:348)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:352)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:356)
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:387)
at cn.sh.ideal.sendsms.dao.TimingSendSmsDao.getAllSmsReceive(TimingSendSmsDao.java:127)
at cn.sh.ideal.sendsms.service.TimingSendSmsService.timingSendSmsDay(TimingSendSmsService.java:117)
at cn.sh.ideal.sendsms.job.TimingSendSmsJob.timingSendSms(TimingSendSmsJob.java:49)
at sun.reflect.GeneratedMethodAccessor84.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:248)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:165)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:90)
at org.quartz.core.JobRunShell.run(JobRunShell.java:216)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
[secs] 2012-11-15 04:15:30,746 - cn.sh.ideal.sendsms.job.TimingSendSmsJob -564890473 [DefaultQuartzScheduler_Worker-2] ERROR cn.sh.ideal.sendsms.job.TimingSendSmsJob  - （两小时执行一次）定时短信发送异常：
org.springframework.dao.DataAcce***esourceFailureException: StatementCallback; SQL [SELECT T.MOBILE mobile FROM T_REPORT_SMS_SET T]; Io 异常: Connection timed out; nested exception is java.sql.SQLException: Io 异常: Connection timed out
java.sql.SQLException: Io 异常: Connection timed out
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:817)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:841)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1274)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:333)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:282)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:348)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:352)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:356)
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:387)
at cn.sh.ideal.sendsms.dao.TimingSendSmsDao.getAllSmsReceive(TimingSendSmsDao.java:127)
at cn.sh.ideal.sendsms.service.TimingSendSmsService.timingSendSmsDay(TimingSendSmsService.java:117)
at cn.sh.ideal.sendsms.job.TimingSendSmsJob.timingSendSms(TimingSendSmsJob.java:49)
at sun.reflect.GeneratedMethodAccessor84.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:248)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:165)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:90)
at org.quartz.core.JobRunShell.run(JobRunShell.java:216)



原因：
数据源出现长时间没有使用中断后。出现Could not close JDBC Connection这种问题。

解决方案：
将testOnBorrow设置为true而validationQuery没有设置，或为空，或你给的不是一个select语句且没有数据返回。testOnBorrow也是白设。validationQuery这个的SQL语句一般查询dual表.常用的有SELECT 1 FROM DUA

修改后的tomcat JNDI配置如下：


    WEB-INF/web.xml


结论：
正式系统中使用的数据源最好是用上这个testOnBorrow+validationQuery。这样可以保证使用的每个数据源都不会出现长时间没有使用中断后。出现Could not close JDBC Connection这种问题。