Java SQL 结果集异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26418840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java SQL ResultSet Exception
提问by k9yosh
I'm Developing a Web Application using JSF & PrimeFaces. I have two classes Inputs & DBConn. And i'm executing a SQL command like this
我正在使用 JSF 和 PrimeFaces 开发 Web 应用程序。我有两个类 Inputs & DBConn。我正在执行这样的 SQL 命令
SQL="SELECT COUNT(*) FROM TXN_HEADER WHERE REQUEST_DATE='01-AUG-2014'"
And then in the DBConn classi call the next()method on the ResultSet objectinside a while loop condition to print the result.
然后在DBConn 类中，我在 while 循环条件内调用ResultSet 对象上的next()方法来打印结果。
This works fine and gives me an output.
这工作正常，并给了我一个输出。
But when i try to call that ResultSet objectto get an output using the same scenario above (using that while loop inside the Inputs class) it gives me the following exception.
但是，当我尝试调用该ResultSet 对象以使用上述相同场景（使用Inputs 类中的 while 循环）获取输出时，它给了我以下异常。
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)
Then i assumed that after the first use of the next()method the cursor is still in the end though i used the next()method again in that Inputs class.
然后我假设在第一次使用next()方法之后，尽管我在那个Inputs 类中再次使用了next()方法，但光标仍然在最后。
So i tried using the .beforeFirst()method before going to the 2nd while loop to reset the cursor back to the top. But it gave me the same error again.
所以我在进入第二个 while 循环之前尝试使用.beforeFirst()方法将光标重置回顶部。但它又给了我同样的错误。
Then i tried commenting the 1st while loop i access in the DBConn classso the cursor is at the top for sure. But then it gives me the following error.
然后我尝试评论我在DBConn 类中访问的第一个 while 循环，因此光标肯定位于顶部。但是它给了我以下错误。
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)
How do i resolve this? This is my first time developing a Database Application. And sorry about these bunch of sys.prints, I used them for debugging.
我如何解决这个问题？这是我第一次开发数据库应用程序。很抱歉这些一堆 sys.prints，我用它们来调试。
These are the two classes related to this case:
这些是与此案例相关的两个类：
Inputs Class
输入类
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 Class
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("<user>");
dataSource.setPassword("<pass>");
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;
}
}
采纳答案by javatar
I think the problem is in the way you get the resultset. Each time you call nCon.getResultSet() it returns a new resultset. That means you call next method of one resultset object and calls getString(1) in another. Here is my solution.
我认为问题在于您获得结果集的方式。每次调用 nCon.getResultSet() 时，它都会返回一个新的结果集。这意味着您调用一个结果集对象的 next 方法并在另一个中调用 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");
}
回答by Shoaib Chikate
Your getResultMethod() is putting cursor at the end of the ResultSet. And this Exception occurs because you are not calling next() and trying to access the ResultSet in your below method
您的 getResultMethod() 将光标放在 ResultSet 的末尾。发生此异常是因为您没有调用 next() 并尝试在下面的方法中访问 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");
}
Solution
解决方案
try{
while(rs.next()){
System.out.println(nCon.getResultSet().getString(1));
}
......
}
