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


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)

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)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.Util.getInstance(Util.java:386)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)

at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1304)

at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1296)

at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4511)

at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4476)

at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)

at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)

at sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source)

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

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

at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:52)

at $Proxy16.prepareStatement(Unknown Source)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:72)

at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:82)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:54)

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:70)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)

at sun.reflect.GeneratedMethodAccessor125.invoke(Unknown Source)

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

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

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

... 56 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure


The last packet successfully received from the server was 101,739 milliseconds ago.  The last packet sent successfully to the server was 101,739 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3871)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)

at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)

at com.mysql.jdbc.DatabaseMetaData.getUserName(DatabaseMetaData.java:6791)

at org.apache.commons.dbcp.DelegatingConnection.toString(DelegatingConnection.java:123)

at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.toString(PoolingDataSource.java:355)

at java.lang.String.valueOf(String.java:2827)

at java.lang.StringBuilder.append(StringBuilder.java:115)

at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:85)

at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:66)

at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:271)

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:69)

... 67 more

Caused by: java.net.SocketException: Connection reset by peer: socket write error

at java.net.SocketOutputStream.socketWrite0(Native Method)

at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)

at java.net.SocketOutputStream.write(SocketOutputStream.java:136)

at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)

at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3852)

... 81 more


posted by 나는너의힘
: