JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » Java 新手區  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 [發問] JAVA查詢資料庫,並將ResultSet傳回JSP網頁,發生Exception, 請問解決方法!!
ttnezpwinf





發文: 20
積分: 0
於 2019-02-26 02:49 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
各位好,請問一個問題:

我用JSP網頁寫一個輸入查詢條件並點擊查詢按鈕後,會將參數傳到Servlet,接著會查詢資料庫,並且將ResultSet傳回到JSP網頁顯示查詢到的該欄位資料,但是我遇到了以下的錯誤訊息:

java.sql.SQLException: Invalid state, the ResultSet object is closed.
  at net.sourceforge.jtds.jdbc.JtdsResultSet.checkOpen(JtdsResultSet.java:302)
  at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:586)
  at org.apache.jsp.index_jsp._jspService(index_jsp.java:142)
  at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
  at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:476)
  at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:386)
  at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:330)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
  at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
  at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:728)
  at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:470)
  at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:395)
  at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:316)
  at com.test.servlet.QueryServlet.doPost(QueryServlet.java:30)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
  at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
  at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
  at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
  at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800)
  at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
  at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
  at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
  at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
  at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
  at java.lang.Thread.run(Thread.java:748)


請教各位前輩該如何解決此問題,以下是我開發時的程式碼:

----------------------------------------------------------------------------------------------------

index.jsp (JSP網頁-輸入查詢條件,並且將資料顯示在該畫面)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.ResultSet" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>index.jsp</title>
  </head>
  <body>
    <h1>DataBase Connection</h1>
    <form action="QueryServlet" method="post">
      <p>
        搜尋條件 <input type="text" name="memberNo" />
        <input type="submit"value="查詢" /> 
      </p>
    </form>
    <br/><br/><br/>
    <table border="2">
      <tr>
        <td>MemberNo</td>
      </tr>
      <%!
        ResultSet rtnRS = null;
      %>
      <%
      rtnRS = (ResultSet)request.getAttribute("rs");
      if(rtnRS != null){
      try{
        while(rtnRS.next())
        {
      %>
        <tr><td><%=rtnRS.getString("MemberNo") %></td></tr>
      <%
        }
      %>
    </table>
    <%
        rtnRS.close();
      } catch(Exception ex){
        ex.printStackTrace();
      }
      }
    %>
  </body>
</html>


----------------------------------------------------------------------------------------------------
QueryServlet (這是一支Servlet 接收參數並執行後端JAVA程式查詢資料庫,將ResultSet傳回index.jsp網頁)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package com.test.servlet;
 
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import com.test.action.OperateQuery;
 
public class QueryServlet extends HttpServlet {
  private static final long serialVersionUID = 1;
       
    @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
      doPost(request, response);
    }
 
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String memberNo = request.getParameter("memberNo");
    OperateQuery qq = new OperateQuery();
    ResultSet rs = null;
    if(memberNo!=null && !memberNo.equals("")){
      rs = qq.doQuery(memberNo);  
    }
    request.setAttribute("rs", rs);
    request.getRequestDispatcher("/index.jsp").forward(request, response);
  }
 
}
 

----------------------------------------------------------------------------------------------------
OperateQuery (資料庫操作,並將Result回傳給QueryServlet )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.test.action;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class OperateQuery {
 
  private static final String DRIVER="net.sourceforge.jtds.jdbc.Driver";
  private static final String URL="jdbc:jtds:sqlserver://127.0.0.1:1433/MYTEST";
  private static final String USERNAME="sa";
  private static final String PASSWORD="1qaz@WSX";
  
  Connection conn =null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  
  public ResultSet doQuery(String memberNo){
    int rsRow=0;
    String strSQL = "select MemberNo from CardInfo where MemberNo=? ";
    try {
      Class.forName(DRIVER).newInstance();
      conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
      ps = conn.prepareStatement(strSQL);
      ps.setString(1, memberNo);
      rs = ps.executeQuery();
      while(rs.next()){
        rsRow +=1;
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        ps.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    return rs;
  }
}
 


reply to postreply to post
作者 Re:[發問] JAVA查詢資料庫,並將ResultSet傳回JSP網頁,發生Exception, 請問解決方法!! [Re:ttnezpwinf]
Landgray





發文: 78
積分: 0
於 2019-02-26 10:31 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
ttnezpwinf wrote:
各位好,請問一個問題:

我用JSP網頁寫一個輸入查詢條件並點擊查詢按鈕後,會將參數傳到Servlet,接著會查詢資料庫,並且將ResultSet傳回到JSP網頁顯示查詢到的該欄位資料,但是我遇到了以下的錯誤訊息:

java.sql.SQLException: Invalid state, the ResultSet object is closed.



錯誤訊息第一句通常就會很明確地告訴你原因
the ResultSet object is closed.

一般做法就是ResultSet 關起來前把資料處理好放進List
再用List傳遞到jsp頁面


reply to postreply to post
作者 Re:[發問] JAVA查詢資料庫,並將ResultSet傳回JSP網頁,發生Exception, 請問解決方法!! [Re:Landgray]
ttnezpwinf





發文: 20
積分: 0
於 2019-03-01 01:08 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
謝謝Landgray指點,我試著照著你說的,
在ResultSet 關起來前把資料處理好放進List,
再用List傳遞到jsp頁面。
可以正常執行。
謝謝。


reply to postreply to post
» JWorld@TW »  Java 新手區

reply to topicthreaded modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8