JDBC ResultSet navigation methods example in java

In this post we talk and learn about some of important Jdbc ResultSet navigation methods.

Some of important ResultSet navigation methods are given below which we are going to use in the program example but this interface has lot more methods which you can explore yourself.

boolean absolute(int row) throws SQLException
This method moves the cursor to the given row number in this ResultSet object.
If the row number is positive, the cursor moves to the given row number with respect to the beginning of the result set. The first row is row 1, the second is row 2, and so on.
If the given row number is negative, the cursor moves to an absolute row position with respect to the end of the result set. For example, calling the method absolute(-1)positions the cursor on the last row; calling the method absolute(-2) moves the cursor to the next-to-last row, and so on.
If the row number specified is zero, the cursor is moved to before the first row.
An attempt to position the cursor beyond the first/last row in the result set leaves the cursor before the first row or after the last row.
This method throws SQLException – if a database access error occurs
Note: Calling absolute(1) is the same as calling first(). Calling absolute(-1) is the same as calling last().

int getRow() throws SQLException
This method retrieves the current row number. The first row is number 1, the second number 2, and so on.
Note:Support for the getRow method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY
This method throws SQLException if a database access error occurs or this method is called on a closed result set.
It may throw SQLFeatureNotSupportedException if the JDBC driver does not support this method.

void afterLast() throws SQLException
Thismethod moves the cursor to the end of this ResultSet object, just after the last row. This method has no effect if the result set contains no rows.

It Throws:SQLException – if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLYSQLFeatureNotSupportedException – if the JDBC driver does not support this method

boolean isAfterLast() throws SQLException
This method checks whether the cursor is after the last row in this ResultSet object.
Note:Support for the isAfterLast method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY

It Throws:SQLException – if a database access error occurs or this method is called on a closed result set
SQLFeatureNotSupportedException – if the JDBC driver does not support this method

void beforeFirst() throws SQLException
Moves the cursor to the front of this ResultSet object, just before the first row. This method has no effect if the result set contains no rows.

Throws:
SQLException – if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY
SQLFeatureNotSupportedException – if the JDBC driver does not support this method

boolean isBeforeFirst() throws SQLException
This method checks whether the cursor is before the first row in this ResultSet object.
Note:Support for the isBeforeFirst method is optional for ResultSetwith a result set type of TYPE_FORWARD_ONLY

SQLException – if a database access error occurs or this method is called on a closed result set
SQLFeatureNotSupportedException – if the JDBC driver does not support this method

boolean first() throws SQLException
This method moves the cursor to the first row in this ResultSet object.It returns true if the cursor is on a valid row or false if there are no rows in the result set.
SQLException – if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY
SQLFeatureNotSupportedException – if the JDBC driver does not support this method

boolean isFirst() throws SQLException
Retrieves whether the cursor is on the first row of this ResultSet object.Note:Support for the isFirst method is optional for ResultSet with a result set type of TYPE_FORWARD_ONLY.It returnstrue if the cursor is on the first row or false otherwise

Throws:
SQLException – if a database access error occurs or this method is called on a closed result set
SQLFeatureNotSupportedException – if the JDBC driver does not support this method

boolean last() throws SQLException
This method moves the cursor to the last row in this ResultSet object.

It returnstrue if the cursor is on a valid row else false if there are no rows in the result set
Throws:
SQLException – if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY
SQLFeatureNotSupportedException – if the JDBC driver does not support this method

boolean relative( int rows ) throws SQLException

This method moves the cursor a relative number of rows, either positive or negative. Attempting to move beyond the first/last row in the result set positions the cursor before/after the the first/last row. Calling relative(0) is valid, but does not change the cursor position.
Note: Calling the method relative(1) is identical to calling the method next() and calling the method relative(-1) is identical to calling the method previous().you can specify the number of rows to move from the current row a positive number moves the cursor forward a negative number moves the cursor backward
It returns true if the cursor is on a row else false otherwise
It Throws:
SQLException – if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY
SQLFeatureNotSupportedException – if the JDBC driver does not support this method

Project structure in eclipse:

SQL Query to create employee_table and insert few rows in MySQL (jdbcdb schema)
(Note:Make sure you have created employee_table and inserted few records in jdbcdb database schema)
DB.sql

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Martin','martin.cs2017@yahoo.com',71000,'2017-05-17 00:00:00',400);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','paul.cs2017@gmail.com',91000,'2017-05-18 00:00:00',800);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','sam.cs2014@gmail.com',76000,'2017-05-16 00:00:00',300);
INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','paul.cs2009@yahoo.com',60000,'2017-05-17 00:00:00',400);

CREATE TABLE `employee_table` (

`employee_id` int(11) NOT NULL auto_increment,

`employee_name` varchar(60) NOT NULL,

`email` varchar(45) NOT NULL,

`salary` double default NULL,

`date_of_joining` datetime default NULL,

`bonus` decimal(10,0) default NULL,

PRIMARY KEY (`employee_id`)

) ;

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Martin','[email protected]',71000,'2017-05-17 00:00:00',400);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',91000,'2017-05-18 00:00:00',800);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','[email protected]',76000,'2017-05-16 00:00:00',300);

INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',60000,'2017-05-17 00:00:00',400);

After running above db script in your MySQL jdbcdb schema employee_table will have following content.

DBUtil.java class which is responsible to connect with MySQL database.

package com.infotech.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

public static Connection getConnection(){

return connection;

}

Below ClientTest.java class uses some Jdbc ResultSet navigation methods

package com.infotech.client;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.infotech.util.DBUtil;
/**
 * @author KK JavaTutorials
 *JDBC program to to show some ResultSet Navigation Methods
 */
public class ClientTest {

	public static void main(String[] args) throws SQLException {
		resultSetNavigationMethods();
	}

	public static void resultSetNavigationMethods() {

		String SQL = "SELECT *FROM employee_table";
		try (Connection connection = DBUtil.getConnection();
				PreparedStatement ps = connection.prepareStatement(SQL,ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_UPDATABLE)) {
			ResultSet rs = ps.executeQuery();
			
			//1.Moves the cursor to the given row number in this ResultSet object.
			rs.absolute(2);
			
			//2.Retrieves the current row number.The first row is number 1,the second number 2, and so on.
			int rowNum = rs.getRow();
			System.out.println("Row num:"+rowNum);
			
			//3.Moves the cursor to the end of this ResultSet object
			rs.afterLast();
			
			//4.Retrieves whether the cursor is after the last row in this ResultSet object.
			boolean afterLast = rs.isAfterLast();
			System.out.println("AfterLast:"+afterLast);
			
			//5.Moves the cursor to the front of this ResultSet object, just before the first row. 
			rs.beforeFirst();
			
			//6.Retrieves whether the cursor is before the first row in this ResultSet object.
			boolean beforeFirst = rs.isBeforeFirst();
			System.out.println("BeforeFirst:"+beforeFirst);
			
			//7.Moves the cursor to the first row in this ResultSet object.
			rs.first();
			
			//8.Retrieves whether the cursor is on the first row of this ResultSet object.
			boolean isFirst = rs.isFirst();
			System.out.println("IsFirst:"+isFirst);
			
			//9.Moves the cursor to the last row in this ResultSet object.
			rs.last();
			
			//10.Moves the cursor a relative number of rows, either positive or negative.
			rs.relative(-2);
			
			int rowNum2 = rs.getRow();
			System.out.println("Row num2:"+rowNum2);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

package com.infotech.client;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.infotech.util.DBUtil;

/**

* @author KK JavaTutorials

*JDBC program to to show some ResultSet Navigation Methods

public class ClientTest {

public static void main(String[] args) throws SQLException {

resultSetNavigationMethods();

}

public static void resultSetNavigationMethods() {

String SQL = "SELECT *FROM employee_table";

try (Connection connection = DBUtil.getConnection();

PreparedStatement ps = connection.prepareStatement(SQL,ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE)) {

ResultSet rs = ps.executeQuery();

//1.Moves the cursor to the given row number in this ResultSet object.

rs.absolute(2);

//2.Retrieves the current row number.The first row is number 1,the second number 2, and so on.

int rowNum = rs.getRow();

System.out.println("Row num:"+rowNum);

//3.Moves the cursor to the end of this ResultSet object

rs.afterLast();

//4.Retrieves whether the cursor is after the last row in this ResultSet object.

boolean afterLast = rs.isAfterLast();

System.out.println("AfterLast:"+afterLast);

//5.Moves the cursor to the front of this ResultSet object, just before the first row.

rs.beforeFirst();

//6.Retrieves whether the cursor is before the first row in this ResultSet object.

boolean beforeFirst = rs.isBeforeFirst();

System.out.println("BeforeFirst:"+beforeFirst);

//7.Moves the cursor to the first row in this ResultSet object.

rs.first();

//8.Retrieves whether the cursor is on the first row of this ResultSet object.

boolean isFirst = rs.isFirst();

System.out.println("IsFirst:"+isFirst);

//9.Moves the cursor to the last row in this ResultSet object.

rs.last();

//10.Moves the cursor a relative number of rows, either positive or negative.

rs.relative(-2);

int rowNum2 = rs.getRow();

System.out.println("Row num2:"+rowNum2);

} catch (Exception e) {

e.printStackTrace();

}

That’s all about JDBC ResultSet navigation methods example in java

You May Also Like:

Calling StoredProcedure Using CallableStatement
Get ResultSet By Calling StoredProcedure Using CallableStatement
Calling database custom Function Using CallableStatement
JDBC batch update with Statement
JDBC batch update with PreparedStatement
JDBC batch update with CallableStatement
How to retrieve data from JDBC resultset in java
How to update a Row in a Database Table Using an updatable ResultSet
How to insert a Row in a Database Table Using an updatable ResultSet

If you have any feedback or suggestion please feel free to drop in below comment box.

Leave a Reply Cancel reply