Java SQL结果集异常

Question

我在开发使用JSF的Web应用程序& PrimeFaces。我有两个类输入& DBConn。和即时执行SQL命令这样

SQL="SELECT COUNT(*) FROM TXN_HEADER WHERE REQUEST_DATE='01-AUG-2014'" 

然后在DBConn i类调用一个while循环的条件内的下一个（）的ResultSet对象上的方法来打印结果。

这工作正常，并给我一个输出。

但是当我尝试调用ResultSet对象使用上述相同的情况下获得的输出（使用而输入类内循环），它给了我下面的异常。

java.sql.SQLException: Result set after last row 
at oracle.jdbc.driver.GeneratedScrollableResultSet.getString(GeneratedScrollableResultSet.java:879) 
at Inputs.commandButton(Inputs.java:41) 
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
at java.lang.reflect.Method.invoke(Unknown Source) 
at org.apache.el.parser.AstValue.invoke(AstValue.java:245) 
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:277) 
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105) 
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87) 
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102) 
at javax.faces.component.UICommand.broadcast(UICommand.java:315) 
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790) 
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282) 
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81) 
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) 
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198) 
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) 
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) 
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503) 
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136) 
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) 
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610) 
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) 
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:526) 
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078) 
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:655) 
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222) 
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566) 
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) 
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 
at java.lang.Thread.run(Unknown Source) 

然后我假定第一使用下一个（）方法的后光标仍在到底虽然我在输入类再次使用下一个（）方法。

所以我尝试使用.beforeFirst（）方法之前去第二while循环重置光标回到顶部。但它再次给了我同样的错误。

然后我试着评论第一个while while循环，我访问DBConn类，所以游标确实位于顶部。但是，它给了我以下错误。

java.sql.SQLException: ResultSet.next was not called 
at oracle.jdbc.driver.GeneratedScrollableResultSet.getString(GeneratedScrollableResultSet.java:874) 
at Inputs.commandButton(Inputs.java:41) 
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
at java.lang.reflect.Method.invoke(Unknown Source) 
at org.apache.el.parser.AstValue.invoke(AstValue.java:245) 
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:277) 
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105) 
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87) 
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102) 
at javax.faces.component.UICommand.broadcast(UICommand.java:315) 
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790) 
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282) 
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81) 
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) 
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198) 
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) 
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) 
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) 
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503) 
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136) 
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) 
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610) 
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) 
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:526) 
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078) 
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:655) 
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222) 
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566) 
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523) 
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) 
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) 
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 
at java.lang.Thread.run(Unknown Source) 

我该如何解决这个问题？这是我第一次开发数据库应用程序。 对于这些sys.prints，我使用它们进行调试。

这些是与此相关的情况下，两个类：

输入级

import java.sql.SQLException; 

import javax.annotation.ManagedBean; 

@ManagedBean 
public class Inputs { 
private String date; 
private String showRes; 

public String getDate() { 
    return date;  
} 

public void setDate(String date) { 
    this.date = date; 
} 

public String getShowRes() { 
    return showRes; 
} 

public void setShowRes(String showRes) { 
    this.showRes=showRes; 
} 

public void commandButton(){ 

    DBConn nCon=new DBConn(); 

    Calculations nCalc=new Calculations(); 
    nCalc.setPieChartSQL(getDate()); 
    nCon.setSQL(nCalc.getPieChartSQL()); 

    System.out.println("............"); 
    System.out.println(nCon.getResultSet()); 
    System.out.println("............"); 
    System.out.println(nCon.getResultSet()); 
    System.out.println("............"); 

    try { 
     System.out.println(nCon.getResultSet().getString(1)); 
     //nCon.getResultSet().beforeFirst(); 
     while(nCon.getResultSet().next()){ 
      System.out.println("++++++++++++"); 
      System.out.println(nCon.getResultSet().getString(1)); 
      System.out.println("++++++++++++"); 
      setShowRes(nCon.getResultSet().getString(1)); 
     } 
    } catch (SQLException e) { 
     e.printStackTrace(); 
    } 
    System.out.println("777777777777777777777"); 
} 

} 

DBConn类

import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 

import oracle.jdbc.OracleConnection; 
import oracle.jdbc.pool.OracleDataSource; 

public class DBConn { 

private String sql; 
private ResultSet rs; 

public ResultSet getResultSet(){ 

    try { 
     OracleDataSource dataSource=new OracleDataSource(); 
     dataSource.setURL("jdbc:oracle:thin:@172.16.20.45:1521:ABS"); 
     dataSource.setUser("ABS_INP"); 
     dataSource.setPassword("b2a6b224"); 

     OracleConnection con = (OracleConnection)dataSource.getConnection(); 
     con.setAutoCommit(false); 

     Statement stmnt = con.createStatement(); 
     rs = stmnt.executeQuery(sql); 
     System.out.println("-----------"); 
     System.out.println(rs); 
     System.out.println("-----------"); 
     System.out.println(rs); 
     System.out.println("-----------"); 

     while(rs.next()){ 
      System.out.println("*************"); 
      System.out.println(rs.getString(1)); 
      System.out.println("*************"); 
     } 


    } catch (SQLException e) { 
     e.printStackTrace(); 
    } 

    return rs; 
} 

public void setSQL(String sql) { 
    this.sql=sql; 
} 

} 

Answer 1

我认为这个问题是在你的方式结果集。每次调用nCon.getResultSet（）时，都会返回一个新的结果集。这意味着你调用一个resultset对象的下一个方法，并在另一个中调用getString（1）。这是我的解决方案。

public void commandButton(){ 

    DBConn nCon=new DBConn(); 

    Calculations nCalc=new Calculations(); 
    nCalc.setPieChartSQL(getDate()); 
    nCon.setSQL(nCalc.getPieChartSQL()); 

    System.out.println("............"); 
    System.out.println(nCon.getResultSet()); 
    System.out.println("............"); 
    System.out.println(nCon.getResultSet()); 
    System.out.println("............"); 

    try { 
     ResultSet rst=nCon.getResultSet(); 
     rst.beforeFirst(); 
     while(rst.next()){ 
      System.out.println("++++++++++++"); 
      String str=rst.getString(1); 
      System.out.println("++++++++++++"); 
      setShowRes(str); 
     } 
    } catch (SQLException e) { 
     e.printStackTrace(); 
    } 
    System.out.println("777777777777777777777"); 
} 

Answer 2

你getResultMethod（）结尾是把光标的ResultSet。而出现这种异常，因为你是不是下一个调用（）和试图访问的ResultSet在以下方法

public void commandButton(){ 

    DBConn nCon=new DBConn(); 

    Calculations nCalc=new Calculations(); 
    nCalc.setPieChartSQL(getDate()); 
    nCon.setSQL(nCalc.getPieChartSQL()); 

    System.out.println("............"); 
    System.out.println(nCon.getResultSet()); 
    System.out.println("............"); 
    System.out.println(nCon.getResultSet()); 
    System.out.println("............"); 

    try { 
     System.out.println(nCon.getResultSet().getString(1)); //here rs.next() should be called first 
     //nCon.getResultSet().beforeFirst(); 
     while(nCon.getResultSet().next()){ 
      System.out.println("++++++++++++"); 
      System.out.println(nCon.getResultSet().getString(1)); 
      System.out.println("++++++++++++"); 
      setShowRes(nCon.getResultSet().getString(1)); 
     } 
    } catch (SQLException e) { 
     e.printStackTrace(); 
    } 
    System.out.println("777777777777777777777"); 
} 

解决方案

try{ 

    while(rs.next()){ 
     System.out.println(nCon.getResultSet().getString(1)); 
    } 
...... 
}