03
Oct

Navigating Resultsets to validate selenium actions in database.

Navigating resultsets with JDBC

By default access to data results via JDBC ResultSet object is non scrollable / updatable and is forward only. This limits the end test case’s capabilities to compare it with previous updates / reads made within same result set.

In order to navigate freely within a result set one can specify different resultset types as follow:

Type

Description

ResultSet.TYPE_FORWARD_ONLY One can move forward only in the result set. Access to previous rows within dataset is not allowed.
ResultSet.TYPE_SCROLL_INSENSITIVE One can scroll forward and backward, and the result set do not include changes to the database that occur after the result set was created.
ResultSet.TYPE_SCROLL_SENSITIVE. One can scroll forward and backward, and the result set includes changes to the database that occur after the result set was created.

 

For all our discussion, we will use following table

Employee table structure & data

id

name

1
Jackie Chan
2
Tintin
3
Donald Duck
4
Roger S. Pressman

 

#. How to move the ResultSet cursor backward and forward

The following example demonstrates how to move the ResultSet cursor to forward or backward direction using the absolute(), first() and last() methods of the ResultSet object.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
* @author SR
*/

public class UpdatableResultSetExample {
public static void main(String[] args) {
String jdbcUrl = “jdbc:sqlserver://x.y.z.a:1433;Database=DBName;
String username = “username”;
String password = pwd;
String sql = “select id, name from employee”;
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
PreparedStatement stmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery()) {

// Move cursor to 2nd row
rs.absolute(2);
System.out.println(“ID :” + rs.getInt(“ID”) + ” \tNAME : “ + rs.getString(“NAME”));

// Move cursor to 4th row
rs.absolute(4);
System.out.println(“ID :” + rs.getInt(“ID”) + ” \tNAME : “ + rs.getString(“NAME”));

// Move cursor to first row
rs.first();
System.out.println(“ID :” + rs.getInt(“ID”) + ” \tNAME : “ + rs.getString(“NAME”));

// Move cursor to last row
rs.last();
System.out.println(“ID :” + rs.getInt(“ID”) + ” \tNAME : “ + rs.getString(“NAME”));
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Output:

ID : 2 NAME : Tintin
ID : 4 NAME : Roger S. Pressman
ID : 1 NAME : Jackie Chan
ID : 4 NAME : Roger S. Pressman

#. How to insert a new row in a ResultSet object

The following example demonstrates how to insert a new row in an updatable ResultSet object.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
* @author SR
*/

public class UpdatableResultSetExample {
public static void main(String[] args) {
String jdbcUrl = “jdbc:sqlserver://x.y.z.a:1433;Database=DBName”;
String username = “username”;
String password = “pwd”;
String sql = “select id, name from employee”;
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
PreparedStatement stmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery()) {

// Save the current cursor position, and move cursor to the insert row,
rs.moveToInsertRow();

//Set columns values
rs.updateInt(“ID”, 5);
rs.updateString(“NAME”, “Tom Hardy”);

//Insert new row
rs.insertRow();

// Move cursor back to saved position
rs.moveToCurrentRow();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Output:

ID : 1 NAME : Jackie Chan
ID : 2 NAME : Tintin
ID : 3 NAME : Donald Duck
ID : 4 NAME : Roger S. Pressman
ID : 5 NAME : Tom Hardy

 

#. How to update a row in a ResultSet object

The following example demonstrates how to update an existing row in an updatable ResultSet object.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

/**

* @author SR

*/

public class UpdatableResultSetExample {

public static void main(String[] args) {

String jdbcUrl = “jdbc:sqlserver://x.y.z.a:1433;Database=DBName”;

String username = “username”;

String password = “pwd”;

String sql = “select id, name from employee”;

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);

PreparedStatement stmt = conn.prepareStatement(sql,

ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery()) {

// Update 2nd row

// Move cursor to 2nd row

rs.absolute(2);

rs.updateString(“NAME”,“David”);

rs.updateRow();

System.out.println(“ID : “ + rs.getInt(“ID”) + ” \tNAME : “ + rs.getString(“NAME”));

} catch (SQLException e) {

e.printStackTrace();

}

}

}

Output:

ID : 2 NAME : David

#. How to delete a row in a ResultSet object

The following example demonstrates how to delete a row in an updatable ResultSet object.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

/**

* @author SR

*/

public class UpdatableResultSetExample {

public static void main(String[] args) {

String jdbcUrl = “jdbc:sqlserver://x.y.z.a:1433;Database=DBName”;

String username = “username”;

String password = “pwd”;

String sql = “select id, name from employee”;

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);

PreparedStatement stmt = conn.prepareStatement(sql,

ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery()) {

// Move cursor to last row

// Delete last row

rs.last();

rs.deleteRow();

// Move cursor to 2nd row

// Delete 2nd row

rs.absolute(2);

rs.deleteRow();

// Move cursor to before the first row.

rs.absolute(0);

while (rs.next()) {

System.out.println(

“ID : “ + rs.getInt(“ID”) + ” \tNAME : “ + rs.getString(“NAME”));

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

Output:

ID : 1 NAME : Jackie Chan
ID : 3 NAME : Donald Duck
ID : 4 NAME : Roger S. Pressman

Functions summary

Types of ResultSet

Type

Description

ResultSet.TYPE_FORWARD_ONLY The cursor can only move forward in the result set.
ResultSet.TYPE_SCROLL_INSENSITIVE The cursor can scroll forward and backward, and the result set is not sensitive to changes made by others to the database that occur after the result set was created.
ResultSet.TYPE_SCROLL_SENSITIVE. The cursor can scroll forward and backward, and the result set is sensitive to changes made by others to the database that occur after the result set was created.

Concurrency of ResultSet

Concurrency

Description

ResultSet.CONCUR_READ_ONLY Creates a read-only result set. This is the default
ResultSet.CONCUR_UPDATABLE Creates an updateable result set.

Functions to navigate within ResultSet

Function Description
beforeFirst() Moves the cursor just before the first row.
afterLast() Moves the cursor just after the last row.
first() Moves the cursor to the first row.
last() Moves the cursor to the last row.
absolute(int row) Moves the cursor to the specified row.
relative(int row) Moves the cursor the given number of rows forward or backward, from where it is currently pointing.
previous() Moves the cursor to the previous row. This method returns false if the previous row is off the result set.
next() Moves the cursor to the next row. This method returns false if there are no more rows in the result set.
getRow() Returns the row number that the cursor is pointing to.
moveToInsertRow() Moves the cursor to a special row in the result set that can be used to insert a new row into the database. The current cursor location is remembered.
MoveToCurrentRow() Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing

Functions to View a Result Set

Function Description
getInt(String columnName) Returns the int in the current row in the column named columnName.
getInt(int columnIndex) Returns the int in the current row in the specified column index. The column index starts at 1, meaning the first column of a row is 1, the second column of a row is 2, and so on.

Functions to Update a Result Set

Function Description
updateString(int columnIndex, String s) Changes the String in the specified column to the value of s.
updateString(String columnName, String s) Similar to the previous method, except that the column is specified by its name instead of its index.

Updating a row in the result set changes the columns of the current row in the ResultSet object, but not in the underlying database. To update your changes to the row in the database, you need to invoke one of the following methods.

Function Description
updateRow() Updates the current row by updating the corresponding row in the database.
deleteRow() Deletes the current row from the database
refreshRow() Refreshes the data in the result set to reflect any recent changes in the database.
cancelRowUpdates() Cancels any updates made on the current row.
insertRow() Inserts a row into the database. This method can only be invoked when the cursor is pointing to the insert row.

 

Connection con = null;

String Connectionurl = “jdbc:sqlserver://x.y.z.a:1433;Database=DBName;

con = DriverManager.getConnection(Connectionurl, User, pwd);

Statement stmt = con.createStatement(); //<===

JDBC provides the following connection methods to create statements
createStatement(int RSType, int RSConcurrency);
prepareStatement(String SQL, int RSType, int RSConcurrency);
prepareCall(String sql, int RSType, int RSConcurrency);