moving ResultSet cursor forward and backward in a

问题:

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.

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

回答1:

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.

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

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

回答2:

You need to call rs.next() before you can call rs.isLast(). The call to rs.isLast() does not offer much in terms of functionality:

if (rs.next()) {
    TxtStockid.setText(rs.getString("StockId"));
    ...

With this approach, you're not advancing the ResultSet cursor so get the same data each time a button is clicked. A WHERE clause would be required to return a different row. If the total amount of records is not large, caching the data would be the preferred approach.

Caching is suitable where the amount of items is relatively small and large scale scaling up is not required. In this case, you could load all data into an ArrayList<MyStockItem> at startup and navigate though the List rather than making database connections. Products such as EHCache are useful for this purpose.

回答3:

You create new result set on every button click. So new result set has it's cursor on first position and that's why you always get the same record while clicking it. To solve it you need to create result set field, then fill it with records in your main class' constrcutor and then operate on this result set.

回答4:

You should use

while(rs.next())  instead of if(!rs.isLast())  and then rs.next()

I dont think resultset provide the api to move backward. Question is why do you need it.