在JDBC应用程序中向前和向后移动ResultSet游标 [英] moving ResultSet cursor forward and backward in a JDBC application

问题描述

我正在开发一个Stock Inventory System软件，它使用JDBC ODBC连接连接到Ms Sql服务器。我想将结果集光标移动到下一行和后一行。连接工作，程序可以从数据库中检索字段，因此没有问题。

I'm working on a Stock Inventory System software which connects to a Ms Sql server using a JDBC ODBC connection. I want to move the Result Set cursor to the next row and backwards. The connection works and the program can retrieve the fields from the database, so there's no issue with that.

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

The code that I have here is a on a button labeled "Next". When you click this button it should move to the next row in the database and retrieve the data from that row. The data retrieved should be displayed in the "textfields". The problem is when I click next nothing happens?

    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);
    }       
}

以下是该程序的其余编码， 下一步按钮被添加到此面板。

Here is the rest of the coding for the program, The "Next" button is added to this Panel.

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 按钮之前获取新记录一次。然后在 NextBtnActionPerformed 方法中使用 rs.next 继续前进。

You should creat Connection to database and fetch fresh record only once before you are clicking next button. And then keep moving forward using rs.next in NextBtnActionPerformed method.

例如，您应该按如下方式调用fetchResultSet方法，并且应该在单击 next 按钮之前调用。

For example you should have a method call fetchResultSet as follows and that should be called beforenext button is clicked.

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 应该是这样的：

And then your NextBtnActionPerformed should be like this:

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);}
}

这篇关于在JDBC应用程序中向前和向后移动ResultSet游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！