Scrollable Resultset in JDBC


Prev Tutorial Next Tutorial

Scrollable Resultset in JDBC

In Jdbc ResultSet Interface are classified into two type;.

  • Non-Scrollable ResultSet in JDBC
  • Scrollable ResultSet

By default a ResultSet Interface is Non-Scrollable, In non-scrollable ResultSet we can move only in forward direction (that means from first record to last record), but not in Backward Direction, If you want to move in backward direction use Scrollable Interface.

scrollable resultset

Difference between Scrollable ResultSet and Non-Scrollable ResultSet

Non-Scrollable ResultSetScrollable ResultSet
1Cursor move only in forward directionCursor can move both forward and backward direction
1Slow performance, If we want to move nth record then we need to n+1 iterationFast performance, directly move on any record.
1Non-Scrollable ResultSet cursor can not move randomlyScrollable ResultSet cursor can move randomly

Create Scrollable ResultSet

To create a Scrollable ResultSet, create Statement object with two parameters.

Syntax

Statement stmt=con.CreateStatement(param1, param2);
// parm1 type and param2 mode

These type and mode are predefined in ResultSet Interface of Jdbc like below which is static final.

Type:

  • public static final int TYPE_FORWARD_ONLY=1003
  • public static final int TYPE_SCROLL_INSENSITIVE=1004
  • public static final int TYPE_SCROLL_SENSITIVE=1005

Mode:

  • public static final int CONCUR_READ_ONLY=1007
  • public static final int CONCUR_UPDATABLE=1008

Note: To create Statement object we can pass either int value as parameter or their variable name.

Example

Statement stmt=con.CreateStatement(1004, 1007);
              or
Statement stmt=con.CreateStatement(ResutlSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

Methods of Scrollable ResultSet

Below all methods are used for move the cursor in Scrollable ResultSet.

  • afterLast Used to move the cursor after last row.
  • BeforeFirst: Used to move the cursor before first row.
  • previous: Used to move the cursor backward.
  • first: Used to move the cursor first at row.
  • last: Used to move the cursor at last row.

Example of Scrollable ResultSet

import java.sql.*;
class ScrollableTest 
{
public static void main(String[] args) throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@John-pc:1521:xe","system","system");
Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs=stmt.executeQuery("select * from student");

//reading from button to top
rs.afterLast();
while(rs.previous())
{
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getInt(3));
}

//move the cursor to 3rd record
rs.absolute(3);
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getInt(3));

//move the cursor to 2nd record using relative()
rs.relative(-1);
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getInt(3));
int i=rs.getRow(); // get cursor position
System.out.println("cursor position="+i);

//cleanup
rs.close();
stmt.close();
con.close();
 }
}

Prev Tutorial Next Tutorial

Advertisements




Facebook Likes

Advertisements