网站建设 美橙/北京优化seo排名

       今天把notice表中的content字段改为long字段后，含有该字段的使用CTE的查询语句报错了。提示一下错误

 

### Cause: java.sql.SQLException: 流已被关闭
; uncategorized SQLException for SQL []; SQL state [99999]; error code [17027]; 流已被关闭; nested exception is java.sql.SQLException: 流已被关闭] with root cause
java.sql.SQLException: 流已被关闭at oracle.jdbc.driver.LongAccessor.getBytesInternal(LongAccessor.java:156)at oracle.jdbc.driver.LongAccessor.getBytes(LongAccessor.java:126)at oracle.jdbc.driver.LongAccessor.getString(LongAccessor.java:201)at oracle.jdbc.driver.T4CLongAccessor.getString(T4CLongAccessor.java:427)at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:1251)at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)at sun.reflect.GeneratedMethodAccessor30.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:67)at com.sun.proxy.$Proxy75.getString(Unknown Source)at org.apache.ibatis.type.StringTypeHandler.getNullableResult(StringTypeHandler.java:37)at org.apache.ibatis.type.StringTypeHandler.getNullableResult(StringTypeHandler.java:26)at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:55)at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getPropertyMappingValue(DefaultResultSetHandler.java:390)at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyPropertyMappings(DefaultResultSetHandler.java:364)at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:338)at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:291)at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:266)at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:236)at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:150)at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:60)at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)at com.moral.frame.dao.PaginationInterceptor.query(PaginationInterceptor.java:137)at com.moral.frame.dao.PaginationInterceptor.intercept(PaginationInterceptor.java:78)at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:60)at com.sun.proxy.$Proxy140.query(Unknown Source)at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)at com.sun.proxy.$Proxy83.selectList(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:205)at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:117)at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)at com.sun.proxy.$Proxy122.getReceiveNoticeList(Unknown Source)at com.moral.notice.service.impl.NoticeServiceImpl.getReceiveNoticeList(NoticeServiceImpl.java:161)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)at com.sun.proxy.$Proxy126.getReceiveNoticeList(Unknown Source)at com.moral.notice.view.NoticeController.getNoticeListPage(NoticeController.java:69)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)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:241)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)at org.apache.logging.log4j.core.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:66)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:313)at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)at java.lang.Thread.run(Thread.java:745)

         但是我没有使用CTE查询的语句可以正常执行，不会报错，下面是cte的查询语句和普通的查询语句

 

 

	    With NoticeCTE As(Select *from (SelectnoticeKey,title,contentFrom NoticeOrder by noticeKey desc	)), NoticeReadCTE As(Select distinct nc.noticeKey, 1 readState From NoticeCTE nc, notice_Reader nrWhere nc.noticeKey = nr.noticeKeyAnd nr.readerCode = #{readerCode})Selectnc.*, readStateFrom NoticeCTE ncleft join NoticeReadCTE rc on nc.noticeKey = rc.noticeKey order by nc.noticeKey desc 

         以下是普通的查询语句

 

 

		SelectnoticeKey,title,contentFrom NoticeWhere noticeKey = #{noticeKey}

      将long类型改成clob，以后上述的sql语句就可以正常执行了。之所以会出现上述原因是因为：

 

      1、cte会自动创建临时表

      2、而含有long类型字段不能通过create table b as select * from a方式创建表

    3、同时long数据类型已经过时，不建议使用，建议使用clob数据类型

    另外还发现在使用map传递参数给mybaits中的CTE查询语句时必须明确指定参数的数据类型，否则会报以下错误

 

; uncategorized SQLException for SQL []; SQL state [99999]; error code [17004]; 无效的列类型: 1111; nested exception is java.sql.SQLException: 无效的列类型: 1111] with root cause
java.sql.SQLException: 无效的列类型: 1111at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3900)at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4406)at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4388)at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1281)at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:43)at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:81)at com.moral.frame.dao.PaginationInterceptor.setParameters(PaginationInterceptor.java:210)at com.moral.frame.dao.PaginationInterceptor.count(PaginationInterceptor.java:166)at com.moral.frame.dao.PaginationInterceptor.query(PaginationInterceptor.java:135)at com.moral.frame.dao.PaginationInterceptor.intercept(PaginationInterceptor.java:78)at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:60)at com.sun.proxy.$Proxy72.query(Unknown Source)at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)at com.sun.proxy.$Proxy14.selectList(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:205)at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:117)at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)at com.sun.proxy.$Proxy54.getReceiveNoticeList(Unknown Source)at com.moral.notice.service.impl.NoticeServiceImpl.getReceiveNoticeList(NoticeServiceImpl.java:162)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)at com.sun.proxy.$Proxy58.getReceiveNoticeList(Unknown Source)at com.moral.notice.view.NoticeController.getNoticeListPage(NoticeController.java:80)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:606)at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:549)at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)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:241)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)at org.apache.logging.log4j.core.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:66)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:313)at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)at java.lang.Thread.run(Thread.java:745)

      正确的mybaitis的sql语句配置为

 

 

	    With NoticeCTE As(Select *from (SelectnoticeKey,title,contentFrom Notice<where><trim suffixOverrides="and"><if test="arrDivisionKey != null ">and divisionKey in (<foreach collection="arrDivisionKey" item="divisionKey" index="index" separator=" , " >    #{divisionKey, jdbcType=VARCHAR}</foreach>)</if><if test="readerCode != null and readerCode != ''">And createCode != #{readerCode, jdbcType=VARCHAR}</if>				      		      </trim></where>Order by noticeKey desc	)<where><trim suffixOverrides="and"><if test="noticeCount != null and noticeCount > 0">and rowNum <= #{noticeCount, jdbcType=NUMBERIC}</if></trim></where>), NoticeReadCTE As(Select distinct nc.noticeKey, 1 readState From NoticeCTE nc, mor_notice_Reader nrWhere nc.noticeKey = nr.noticeKeyAnd nr.readerCode = #{readerCode, jdbcType=VARCHAR})Selectnc.*, readStateFrom NoticeCTE ncleft join NoticeReadCTE rc on nc.noticeKey = rc.noticeKey order by nc.noticeKey desc 

       出现错误的原因是传入的参数为Map<String, Object>类型，mybatis无法确定参数的实际类型，因此需要手工指定。