Я написал следующий код
public JSONObject getListByUserSQLQuery(JSONObject preparedQuery,final int jsonType,final Map<Object, Object>mMap,final JSONObject pagination)
throws Exception {
SqlParameterSource parameterSource=getMapFromParam(preparedQuery);
final CachedRowSet cachedRowSet=new CachedRowSetImpl();
System.out.println(pagination);
Object obj=namedParameterJdbcTemplate.query(preparedQuery.getString("query"), parameterSource, new ResultSetExtractor<Object>() {
@Override
public Object extractData(ResultSet rs) throws SQLException,
DataAccessException {
ResultSetMetaData rsmd = rs.getMetaData();
if(pagination.has("_LIMIT_")){
cachedRowSet.setPageSize(pagination.getInt("_LIMIT_"));
cachedRowSet.setFetchSize(pagination.getInt("_LIMIT_"));
}
if( pagination.has("_START_") && cachedRowSet.getPageSize()!=0){
cachedRowSet.populate(rs, pagination.getInt("_START_")+1);
}else{
cachedRowSet.populate(rs);
}
Object obj=null;
if (jsonType == CommonConstants.JSON_TYPE_MOBILE) {
obj = formatOtherData(cachedRowSet, rsmd, mMap);
} else if (jsonType == CommonConstants.JSON_TYPE_PLATEFORM) {
obj = formatPlatformData(cachedRowSet, rsmd);
} else if (jsonType == CommonConstants.JSON_DOWNLOAD_MOBILE) {
obj = formatOtherDownloadData(cachedRowSet, rsmd);
} else{
throw new Exception("Unknown Format");
}
return obj;
}
});
Вывод для этого кода выглядит следующим образом
{"_START_":0,"_LIMIT_":10}
[http-bio-8081-exec-9] [ERROR] [SQLSERVER2307] ==> PreparedStatementCallback; uncategorized SQLException for SQL [SELECT "lj"."category_id" "category_id", "lj"."category_code" "category_code", "lj"."category_name" "category_name", "lj"."bz$_version_id" "bz$_version_id_5688" FROM "trn_375_category2" "lj"]; SQL state [null]; error code [0]; The requested operation is not supported on forward only result sets.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets.
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT "lj"."category_id" "category_id", "lj"."category_code" "category_code", "lj"."category_name" "category_name", "lj"."bz$_version_id" "bz$_version_id_5688" FROM "trn_375_category2" "lj"]; SQL state [null]; error code [0]; The requested operation is not supported on forward only result sets.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets.
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:154)
at com.buzzor.api.dao.impl.CommonQueryCallDaoImpl.getListByUserSQLQuery(CommonQueryCallDaoImpl.java:118)
at com.buzzor.api.service.impl.CommonQueryCallServiceImpl.executeQuery(CommonQueryCallServiceImpl.java:182)
at com.buzzor.api.service.impl.CommonUrlCallServiceImpl.getActualData(CommonUrlCallServiceImpl.java:490)
at com.buzzor.api.service.impl.CommonUrlCallServiceImpl.getDataNOTrans(CommonUrlCallServiceImpl.java:609)
at com.buzzor.api.service.impl.CommonUrlCallServiceImpl.getData(CommonUrlCallServiceImpl.java:306)
at com.buzzor.api.service.impl.APIHandlerServiceImpl.callAPI(APIHandlerServiceImpl.java:105)
at com.buzzor.portal.biz.impl.CommonGridServiceImpl.callAPI(CommonGridServiceImpl.java:851)
at com.buzzor.portal.biz.impl.CommonGridServiceImpl.organizeData(CommonGridServiceImpl.java:797)
at com.buzzor.portal.biz.impl.CommonGridServiceImpl.getReportQuery(CommonGridServiceImpl.java:714)
at com.buzzor.portal.biz.impl.CommonGridServiceImpl.getReportData(CommonGridServiceImpl.java:161)
at com.buzzor.portal.action.CommonGridAction.getData(CommonGridAction.java:309)
at com.buzzor.portal.action.CommonGridAction.getGridData(CommonGridAction.java:474)
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.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:108)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at com.buzzor.portal.security.RequestFilterSessionValidator.doFilterInternal(RequestFilterSessionValidator.java:46)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.filters.RequestDumperFilter.doFilter(RequestDumperFilter.java:203)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
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:744)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.throwNotScrollable(SQLServerResultSet.java:402)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.verifyResultSetIsScrollable(SQLServerResultSet.java:425)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.absolute(SQLServerResultSet.java:1564)
at org.apache.commons.dbcp.DelegatingResultSet.absolute(DelegatingResultSet.java:335)
at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:7255)
at com.buzzor.api.dao.impl.CommonQueryCallDaoImpl$2.extractData(CommonQueryCallDaoImpl.java:128)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:708)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
... 92 more
этот же код отлично работает в mysql, но когда я подключаюсь к базе данных sqlserver, он выдает ошибку при заполнении набора результатов
заранее спасибо
ResultSet.TYPE_SCROLL_INSENSITIVE, но у меня нет доступного объекта подключения, я используюnamedParameterJdbcTemplate- person Darshan Patel schedule 24.07.2014