JDBC GS using ResultSet cursor

Moving around inside a ResultSet table?

  • Cursor is a database term. It generally refers to the set of rows returned by a query.
  • When a cursor is position at a row, we mean that we are accessing a particular row in the ResultSet.
  • When a ResultSet is open, JDBC sets the Cursor before the first row in the set.
  • You can move to the next row in the ResultSet table with the ResultSet method:
    public boolean next() // positioning the Cursor in the ResultSet to the next row
  • A Resultset object that is scrollable has methods that allow you to position the cursor on a selected row.
    Here are the most commonly used methods for this purpose:
    Methods in ResultSet interface Description
    boolean next() Position the Cursor in the ResultSet to the next row
    boolean previous() Position the Cursor in the ResultSet to the previous row
    boolean first() Position the Cursor in the ResultSet to the first row
    boolean last() Position the Cursor in the ResultSet to the last row
    void afterLast() Position the Cursor in the ResultSet to the after Last row
    boolean absolute(int) Position the Cursor in the ResultSet to an absolute row number where the number starts with 1 for the first row.
    boolean relative(int) Position the Cursor in the ResultSet to a relative row number.
    boolean isFirst() Return true if the position of the Cursor in the ResultSet is at the first row.
    boolean isBeforeFirst() Return true if the position of the Cursor in the ResultSet is before the first row.
    boolean isLast() Return true if the position of the Cursor in the ResultSet is at the last row.
    boolean isAfterLast() Return true if the position of the Cursor in the ResultSet is after the last row.
    int getRow() Return the position row number of the Cursor in the ResultSet.
    Here is an example where the next() method is used:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class Main {
      public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
          // Register a driver for the MySQL database
          Class.forName("com.mysql.jdbc.Driver");
          // Create a url for accessing the MySQL
          // database CarDB
          String url = "jdbc:mysql://localhost:3306/CarDB";
          // user and password to access the database
          String username = "root";
          String password = "root";
          // User the DriverManager to get a Connection to the database
          conn = DriverManager.getConnection(url, username, password);
          // Create a  Statement for a ResultSet that is
          // sensitive for other users updating of tables in the database and
          // the ResultSet is updatable for a selected table in the database.
          stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                  ResultSet.CONCUR_UPDATABLE);
          // Create a  ResultSet with a query that returns
          // all the columns  from the dB table Carpark
          ResultSet resultSet = stmt.executeQuery("select * from Carpark ");
          // As cursor is at the before first row position
          // we use the next() method to
          // test and read the first row in the ResultSet.
          if (resultSet.next()) {
            // ...
            // ... codes to handle the ResultSet row(s)
            // ...
          }
        } catch (ClassNotFoundException ex) {
          Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          try {
            // Close the Statement, which also close the ResultSet
            stmt.close();
            conn.close();
          } catch (Exception xe) {
            xe.printStackTrace();
          }
        }
      }
    }
  • You should use the close() method in the Statement object when you do not need it anymore.
  • You should also use the close() method in the Connection object when you do not need it anymore.

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.