Scrollable Resultset in JDBC
Advertisements
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.
Difference between Scrollable ResultSet and Non-Scrollable ResultSet
| Non-Scrollable ResultSet | Scrollable ResultSet | |
|---|---|---|
| 1 | Cursor move only in forward direction | Cursor can move both forward and backward direction |
| 1 | Slow performance, If we want to move nth record then we need to n+1 iteration | Fast performance, directly move on any record. |
| 1 | Non-Scrollable ResultSet cursor can not move randomly | Scrollable 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();
}
}
Google Advertisment