java.sql.SQLException: Already closed.

JAVA/iBatis 2013. 4. 30. 08:42

java.sql.SQLException: Already closed.

error: java.sql.SQLException: Already closed. oracle
상황 : 이상없이 잘 사용하던 커넥션이 한번씩 끊긴다. 이후 재사용시에는 무리없는 활동을 보여준다.
이유 : 데이터베이스 입장에서 오랫동안 휴면되는 커넥션은 불필요하다고 판단하고, 끊어버린다. 때문에 커넥션풀 설정시 연결 확인용 쿼리를 지정해주어야 한다.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${db.driverName}"></property>
        <property name="url" value="${db.url}"></property>
        <property name="username" value="${db.username}"></property>
        <property name="password" value="${db.password}"></property>
        <property name="initialSize" value="${db.initialSize}"></property>
        <property name="maxActive" value="${db.maxActive}"></property>
        <property name="validationQuery" value="select 1 from dual" /> <—Oracle

<property name="validationQuery" value="select 1" /> <-- MySql -->

</bean>

원본 데이터 자료 : http://seemoon.tistory.com/191

DEBUG: org.springframework.jdbc.datasource.DataSourceUtils - Could not close JDBC Connection

java.sql.SQLException: Already closed.

at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)

at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)

at org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:333)

at org.springframework.jdbc.datasource.DataSourceUtils.releaseConnection(DataSourceUtils.java:294)

at org.mybatis.spring.transaction.SpringManagedTransaction.close(SpringManagedTransaction.java:122)

at org.apache.ibatis.executor.BaseExecutor.close(BaseExecutor.java:78)

at org.apache.ibatis.executor.CachingExecutor.close(CachingExecutor.java:65)

at org.apache.ibatis.session.defaults.DefaultSqlSession.close(DefaultSqlSession.java:206)

at org.mybatis.spring.SqlSessionUtils.closeSqlSession(SqlSessionUtils.java:172)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)

at $Proxy15.selectList(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:194)

at kr.faz.ordersheet.dao.impl.OrdersheetDaoImpl.list(OrdersheetDaoImpl.java:26)

at kr.faz.ordersheet.service.impl.OrdersheetServiceImpl.list(OrdersheetServiceImpl.java:27)

at kr.faz.ordersheet.controller.OrdersheetController.checkData(OrdersheetController.java:110)

at kr.faz.ordersheet.controller.OrdersheetController.adminOrdersheetList(OrdersheetController.java:74)

at kr.faz.ordersheet.controller.OrdersheetController$$FastClassByCGLIB$$86da2af8.invoke(<generated>)

at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)

at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)

at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)

at kr.faz.aop.SessionCheck.adminSessionCheck(SessionCheck.java:51)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)

at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)

at kr.faz.ordersheet.controller.OrdersheetController$$EnhancerByCGLIB$$8d2c0c1.adminOrdersheetList(<generated>)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)

at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:852)

at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:584)

at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1508)

at java.lang.Thread.run(Thread.java:619)

org.springframework.dao.DataAccessResourceFailureException:

### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

### The error may exist in file [P:\WSC\sts\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\cs\WEB-INF\classes\mybatis\xml\ordersheet.xml]

### The error may involve SqlOrdersheetMapper.ordersheetList

### The error occurred while executing a query

### SQL: SELECT ************** LIMIT ? , ?

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

; SQL []; No operations allowed after connection closed.; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)