java在JSP中从MySQL数据库获取值

Question

下面的代码用于从MySQL数据库中获取PONo，并在按钮中查看它们

当用户点击（PONo查看）按钮时，会出现另一个页面来查看特定PONo的采购订单信息。（viewpo.jsp）

但正如我点击104的屏幕截图所示，显示了102个采购订单的详细信息。当我点击102时，会显示102个采购订单详细信息。 如何修复此错误

 <%
    String CONN_STRING = "jdbc:mysql://localhost/pmsdb";
    String USERNAME = "dbuser";
    String PASSWORD = "dbpassword";

    Connection conn=null;
    PreparedStatement pst=null;
    ResultSet rs =null;
    ResultSet rs2 =null;
    ResultSet rs3 =null;
    Statement stmt=null;
    Statement stmt2=null;

    Object userr = session.getValue("Username");
    String user = userr.toString();
    Class.forName("com.mysql.jdbc.Driver");

    conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);

    String sql0="SELECT * FROM websupplierinfo WHERE username='"+user+"'";
    pst=conn.prepareStatement(sql0);
    rs3=pst.executeQuery();

    if(rs3.next()){
    String companyname = rs3.getString("Company");

    String sql = "SELECT * FROM purchaseorderinfo WHERE Supplier='"+companyname+"' ORDER BY PONo DESC";
   // String sql2 = "SELECT * FROM itemspoinfo WHERE Company='"+companyname+"' ORDER BY PONo DESC";
    stmt = conn.createStatement();
    rs=stmt.executeQuery(sql);

    //stmt2=conn.createStatement();
   // rs2=stmt2.executeQuery(sql2);

    %> 

<div class="row">
    <div class="col-md-12">
        <div class="citydetails">
            <div class="panel panel-primary">
                <div class="panel-heading">Available Purchase Orders</div>
                    <div class="panel-body">
                        <table class="table">
                            <tr>
                                <th>All Purchase Orders</th>


                            </tr>
                            <tr><td><form>
            <div class="form-group">
                <div class="row">
                    <div class="col-sm-6 center-block">
                        <input type="button" name="view" id="view" tabindex="1" class="form-control btn btn-login" value="All Purchase Orders" onclick="window.open('alladminpo.jsp','_blank','resizable=yes')">
                    </div>
                </div>
            </div>    
        </form></td>
        </tr>    

      <th>Latest Purchase Order Numbers</th>                                  
    <%while(rs.next()){ %>
    <tr>
    <td>
      <form>
            <div class="form-group">
                <div class="row">
                    <div class="col-sm-2 center-block">
                        <input type="button" name="view" id="view" tabindex="1" class="form-control btn btn-login" value="<%=rs.getString("PONo")%>" onclick="window.open('viewpo.jsp','_blank','resizable=yes')">
                   <% 
          String po = rs.getString("PONo");
          session.putValue("PONo", po);
                   %>
                    </div>
                </div>
            </div>    
        </form>
      <% }}%>
    </td>                             
    </tr>                   
                        </table>
                    </div>
            </div>
        </div>
    </div>
</div>

viewpo。jsp（重要代码段）

    <%
    String CONN_STRING = "jdbc:mysql://localhost/pmsdb";
    String USERNAME = "dbuser";
    String PASSWORD = "dbpassword";

    Connection conn=null;
    ResultSet rs =null;
    ResultSet rs2 =null;

    Statement stmt=null;
    Statement stmt2=null;

    Class.forName("com.mysql.jdbc.Driver");

    conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);

    Object po = session.getValue("PONo");
    String pno=po.toString();

    String sql = "SELECT * FROM purchaseorderinfo WHERE PONo='"+pno+"'";
    String sql2 ="SELECT * FROM itemspoinfo WHERE PONo='"+pno+"'";

    stmt = conn.createStatement();
    rs=stmt.executeQuery(sql);

    stmt2=conn.createStatement();
    rs2=stmt2.executeQuery(sql2);
    %>  

<div class="row">
<div class="col-md-12">
<div class="citydetails">
    <div class="panel panel-primary">
      <div class="panel-heading">Purchase Order Details</div>
      <div class="panel-body">
          <table class="table">
              <thead>
                    <tr>
                        <th>Purchase Order <%=session.getValue("PONo") %></th>
                    </tr>   
              </thead>
            <tbody>

                    <tr>

                        <td>
                            <% if(rs.next()){ %>
                            Purchase Order Date : <%= rs.getString("PODate") %><br>
                            Expected Date : <%=rs.getString("ExpectedDate") %> <br>
                            <% } %> 

                            <table class="table">
                                <tbody>
                                    <tr>
                                        <th>Items</th>
                                        <th>Quantity</th>
                                    </tr>
                                     <% if (rs2.next()){ %>
                                    <tr>

                                        <td><%=rs2.getString("ItemName")%></td>
                                        <td><%=rs2.getString("Qty")%></td>

                                    </tr>
                                    <% } %>

Answer 1

在你正在使用的一个while循环中

 String po = rs.getString("PONo");
          session.putValue("PONo", po);

上面的语句和设置属性PONo的最新值将被每个值覆盖。所以每次你都会从数据库中获取最后一个值。 必须设置单击的值

Answer 2

您正在替换session中的PONo值，它只保存最后一个值。 因此，将PONo值作为request参数传递给viewpo.jsp

   <input type="button" name="view" id="view" tabindex="1" class="form-control btn btn-login" value="<%=rs.getString("PONo")%>" onclick="window.open('viewpo.jsp?PONo=<%=rs.getString("PONo")%>','_blank','resizable=yes')">

并删除jsp中的以下代码，因为它将替换PONo的值

 <% 
      String po = rs.getString("PONo");
      session.putValue("PONo", po);
 %>

在viewpo.jsp中，我们可以访问PONo，如下所示：

String pno=request.getParameter('PONo');

无论你在哪里使用session.getValue("PONo");，你都必须request.getParameter("PONo")