JDBC PS using ResultSet cursor

Moving around inside a ResultSet table using a PreparedStatement?

  • Cursor is a database term. It generally refers to the set of rows returned by a query.
  • When a cursor is positioned at a row, we mean that we are accessing a particular row in the ResultSet.
  • When a ResultSet is opened, 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() Positioning the Cursor in the ResultSet to the next row
    boolean previous() Positioning the Cursor in the ResultSet to the previous row
    boolean first() Positioning the Cursor in the ResultSet to the first row
    boolean last() Positioning the Cursor in the ResultSet to the last row
    void afterLast() Positioning the Cursor in the ResultSet to the after Last row
    boolean absolute(int) Positioning the Cursor in the ResultSet to a absolute row number where the number start with 1 for the first row.
    boolean relative(int) Positioning the Cursor in the ResultSet to a relative row number.
    boolean isFirst() Returns true if the position of the Cursor in the ResultSet is at the first row.
    boolean isBeforeFirst() Returns true if the position of the Cursor in the ResultSet is before the first row.
    boolean isLast() Returns true if the position of the Cursor in the ResultSet is at the last row.
    boolean isAfterLast() Returns true if the position of the Cursor in the ResultSet is after the last row.
    int getRow() Returns the position row number of the Cursor in the ResultSet.
    Example where ResultSet next() method is included:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class RetrieveResultSet {
     public static void main(String[] args) {
      Connection conn = null;
      PreparedStatement 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  PreparedStatement for a ResultSet that will
       // return all the columns and rows from the dB table Carpark
       stmt = conn.prepareStatement("select * from Carpark ");
       // Gets the ResultSet from the PreparedStatement object
       ResultSet resultSet = stmt.executeQuery();
       // 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) {
       ex.printStackTrace();
      } 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 PrearedStatement 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.