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.