Today While developing a piece of code to fetch user details from the database on execution of a stored procedure i have came across the following exception -
Exception
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:796)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6857)
at biz.roitech.colibri.dao.impl.MUserDaoImpl.navLinkWithSubLink(MUserDaoImpl.java:65)
at biz.roitech.colibri.ume.service.impl.LoginServiceImpl.login(LoginServiceImpl.java:61)
at biz.roitech.colibri.ume.action.LoginAction.login(LoginAction.java:263)
at biz.roitech.colibri.ume.action.LoginAction.login(LoginAction.java:92)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:252)
at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265)
at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:73)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:91)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:252)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:193)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)
at biz.roitech.colibri.interceptor.CustomI18NInterceptor.intercept(CustomI18NInterceptor.java:36)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at rt.template.startup.EntityManagerInterceptor.intercept(EntityManagerInterceptor.java:29)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at rt.template.interceptors.AuthenticationInterceptor.handleExcludedActions(AuthenticationInterceptor.java:38)
at rt.template.interceptors.AuthenticationInterceptor.intercept(AuthenticationInterceptor.java:143)
at com.google.inject.struts2.Struts2Factory$ProvidedInterceptor.intercept(Struts2Factory.java:215)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:546)
at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at rt.template.startup.TemplateContextFilter.doFilter(TemplateContextFilter.java:46)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at org.apache.catalina.filters.ExpiresFilter.doFilter(ExpiresFilter.java:1201)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.filters.ExpiresFilter.doFilter(ExpiresFilter.java:1201)
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:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Code to fetch User Details from the database using procedure call
UserDetails userDetails;
UserDto userDto = new UserDto();
Set<RoleDto> roleSet = new HashSet<>();
List<NavLinkDto> navLinkDtos = new ArrayList<>();
try {
EntityManager em = getEntityManager();
// em.getTransaction().begin();
SessionImpl sessionImpl = ((SessionImpl) em.getDelegate());
Connection cc = sessionImpl.connection();
CallableStatement callableStatement = null;
callableStatement = cc.prepareCall("{call LoginProcedure (?,?,?,?)}");
callableStatement.setString(1, userId);
callableStatement.setString(2, password);
callableStatement.setString(3, ip);
callableStatement.setString(4, locale);
boolean isResultSet = callableStatement.execute();
ResultSet rs1 = callableStatement.getResultSet();
while (rs1.next()) {
userDto = new UserDto(rs1.getInt(1), rs1.getString(2), rs1.getString(3), rs1.getString(4), rs1.getString(5), rs1.getString(6), rs1.getString(7));
String[] roleArray = rs1.getString(8).split(",");
for (String role : roleArray) {
roleSet.add(new RoleDto(role));
}
}
rs1.close();
if (callableStatement.getMoreResults()) {
ResultSet rs2 = callableStatement.getResultSet();
while (rs2.next()) {
NavLinkDto navlink = new NavLinkDto();
navlink.setLinkText(rs2.getString(1));
String[] sublinkText = rs2.getString(2).split(",");
String[] sublinkPath = rs2.getString(3).split(",");
List<NavSubLinkDto> subLinkList = new ArrayList<>();
for (int i = 0; i < sublinkText.length; i++) {
NavSubLinkDto sublink = new NavSubLinkDto(sublinkPath[i], sublinkText[i]);
subLinkList.add(sublink);
}
navlink.setSubLinkList(subLinkList);
navLinkDtos.add(navlink);
}
rs2.close();
}
if (callableStatement.getMoreResults()) {
ResultSet rs3 = callableStatement.getResultSet();
while (rs3.next()) {
userDto.setErrorMessage(rs3.getString(1));
}
rs3.close();
}
userDetails = new UserDetails(userDto, navLinkDtos, roleSet);
} catch (RuntimeException | SQLException re) {
re.printStackTrace();
throw new DaoException(re);
}
return userDetails;
}
While debugging the code the exception is thrown at the 2nd while loop where ResultSet rs2 is checked for next() and NavLinkDto navlink object is populated by navlink.setLinkText(rs2.getString(1)). The While loop executes first time as expected and populates NavLinkDto object after that it again checks for next() and generates the exception
Exception occurred in target VM: Operation not allowed after ResultSet closed<
I don't understand why the ResultSet is getting closed inside the while loop. Is there any problem with the coding structure ?
