JDBC CS using ResultSet cursor

Moving around inside a ResultSet table using a CallableStatement?

  • 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 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 a absolute row number where the number start 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.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class RetrieveResultSet {
     public static void main(String[] args) {
      Connection conn = null;
      CallableStatement 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);
       /* ---- USING DB Defined PROCEDURE: getCarPark() ----------
        DELIMITER $$
        DROP PROCEDURE IF EXISTS `cardb`.`getCarPark`$$
        CREATE DEFINER=`root`@`localhost`
        PROCEDURE `getCarPark`(IN regInNo varchar(20))
          BEGIN
            select * from CarPark where  regNo LIKE regInNo;
          END$$
        DELIMITER ;
       ---------------------------------------------------------- */
       // Create a  CallableStatement for a ResultSet that will
       // return all the columns and rows from the dB table Carpark
       // using a DB procedure,  getCarPark. 
       stmt = conn.prepareCall("{ call getCarPark('%') } ");
       // Gets the ResultSet from the CallableStatement 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 CallableStatement 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.