在 JDBC 应用程序中向前和向后移动 ResultSet 游标

我正在开发一个库存系统软件，该软件使用 JDBC ODBC 连接连接到 Ms Sql 服务器。我想将结果集光标移动到下一行并向后移动。连接正常，程序可以从数据库中检索字段，因此没有问题。

我这里的代码位于标有“下一步”的按钮上。当您单击此按钮时，它应该移动到数据库中的下一行并从该行检索数据。检索到的数据应显示在“文本字段”中。问题是当我点击下一步时没有任何反应？

    private void NextBtnActionPerformed(java.awt.event.ActionEvent evt) {                                        
    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbc");
        Connection con;
        con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");           

        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        String query = "select * from Stocktbl";

        ResultSet rs; 
        rs = stmt.executeQuery(query); 


        if(!rs.isLast()) {
            rs.next();

            TxtStockid.setText(rs.getString("StockId"));
            TxtItem.setText(rs.getString("ItemName"));
            TxtQuantity.setText(rs.getString("Quantity"));
            TxtUnitprice.setText(rs.getString("UnitPrice"));
            TxtNetprice.setText(rs.getString("NetPrice"));
            TxtUnitweight.setText(rs.getString("UnitWeight"));
            TxtNetweight.setText(rs.getString("Netweight"));
            TxtDescription.setText(rs.getString("Description"));
        }

        rs.close();
        stmt.close();
        con.close();




    } 
    catch (SQLException ex) 
    {
        Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
    }
    catch (ClassNotFoundException ex) 
    {
        Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
    }       
}

这是程序的其余编码，“下一步”按钮被添加到该面板中。

public class StockScr extends javax.swing.JPanel {
ResultSet rs;
Connection con = null;

public StockScr() {
    initComponents();
    ShowTable();

}

void ShowTable(){
    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbc");
        Connection con;
        con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");           

        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        String query = "select * from Stocktbl";           
        ResultSet rs; 
        rs = stmt.executeQuery(query);

        rs.first();
            TxtStockid.setText(rs.getString("StockId"));
            TxtItem.setText(rs.getString("ItemName"));
            TxtQuantity.setText(rs.getString("Quantity"));
            TxtUnitprice.setText(rs.getString("UnitPrice"));
            TxtNetprice.setText(rs.getString("NetPrice"));
            TxtUnitweight.setText(rs.getString("UnitWeight"));
            TxtNetweight.setText(rs.getString("Netweight"));
            TxtDescription.setText(rs.getString("Description"));

        rs.close();
        stmt.close();
        con.close();

    } 
    catch (SQLException ex) 
    {
        Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
    }
    catch (ClassNotFoundException ex) 
    {
        Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);
    }        
}


public void fetchResultSet()
{

try {
    if(con==null || con.isClosed())
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbc");
      con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");           
    }
    Statement stmt =     con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    String query = "select * from Stocktbl";
    rs = stmt.executeQuery(query); 
  }catch(Exception ex){
     System.out.println(ex);
     Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex); 
  }

     try
     {
        if(con != null)
        {
          con.close();
        }
     }catch(Exception ex){

     }

}


private void NextBtnActionPerformed(java.awt.event.ActionEvent evt) {

try
{

 if (rs == null)
 {
    fetchResultSet();
 }

 if (rs!=null)
 {
    if (rs.next())
    {
        TxtStockid.setText(rs.getString("StockId"));
        TxtItem.setText(rs.getString("ItemName"));
        TxtQuantity.setText(rs.getString("Quantity"));
        TxtUnitprice.setText(rs.getString("UnitPrice"));
        TxtNetprice.setText(rs.getString("NetPrice"));
        TxtUnitweight.setText(rs.getString("UnitWeight"));
        TxtNetweight.setText(rs.getString("Netweight"));
        TxtDescription.setText(rs.getString("Description"));         
    }
   else
   {
      rs = null;
   }
 }
}catch(Exception ex){
  System.out.println(ex); 
  }        
}

您应该在单击之前创建与数据库的连接并仅获取一次新记录next按钮。然后继续使用rs.next in NextBtnActionPerformed method.

例如，您应该有一个方法调用 fetchResultSet ，如下所示，并且应该在之前调用next单击按钮。

ResultSet rs;
Connection con = null;
public void fetchResultSet()
{

   try {
        if(con==null || con.isClosed())
        {
          Class.forName("sun.jdbc.odbc.JdbcOdbc");
          con = DriverManager.getConnection("jdbc:odbc:StockInventory","sa","123");           
        }
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        String query = "select * from Stocktbl";
        rs = stmt.executeQuery(query); 
      }catch(Exception ex)
      {
         System.out.println(ex);
         Logger.getLogger(StockScr.class.getName()).log(Level.SEVERE, null, ex);

         try
         {
            if(con != null)
            {
              con.close();
            }
         }catch(Exception x){}
      }
}

然后你的NextBtnActionPerformed应该是这样的：

private void NextBtnActionPerformed(java.awt.event.ActionEvent evt)
{
  try
  {
     if (rs == null)
     {
        fetchResultSet();
     }
     if (rs!=null)
     {
        if (rs.next())
        {
            TxtStockid.setText(rs.getString("StockId"));
            TxtItem.setText(rs.getString("ItemName"));
            TxtQuantity.setText(rs.getString("Quantity"));
            TxtUnitprice.setText(rs.getString("UnitPrice"));
            TxtNetprice.setText(rs.getString("NetPrice"));
            TxtUnitweight.setText(rs.getString("UnitWeight"));
            TxtNetweight.setText(rs.getString("Netweight"));
            TxtDescription.setText(rs.getString("Description"));         
        }
       else
       {
          rs = null;
       }
     }
  }catch(Exception ex){System.out.println(ex);}
}