What is Scrollable and Updatable ResultSet ?

Until the release of JDBC2.1 the virtual curser could only be moved down the ResultSet object. But now the virtual cursor can be moved backwords or even positioned at a specific row.Also we can modify the values of resultset.
The createStatement() method can take two arguments.
The first argument can be:
TYPE_FOREWARD_ONLY
TYPE_SCROLL_INSENSITIVE
TYPE_SCROLL_SENSITIVE
The TYPE_FOREWARD_ONLY constant restricts the cursor in previous direction which is the default setting.
The TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE permit the virtual cursor in both directions. But the difference is TPE_SCROLL_INSENSITIVE does not reflect changes made to the underlying data source while it is open, but reflects it later.
The second argument can be:
CONCUR_UODATABLE
CONCUR_READ_ONLY
Three ways in which a ResultSet can be changed, by updating value of a row, by deleting a row and by inserting a new row. Once we get the ResultSet the updateXXX() method is used to change the value of a row of ResultSet.This method takes two arguments, first either the name or number of the column of the ResultSet thst is to be updated and the second is the new value that will replace the old.
The value can be set to null by calling updateNull() method. It requires one parameter which is number of the column in the current row of the ResultSet.
The updateRow() method is called after updateXXX() method. It changes the values of the column of current row based on the values of updateXXX() method.
The deleteRow() method is used to delete a row.
To insert a new row moveToInsertRow() method is there. After that we have to set all the fields by updateXXX() method.After that insertRow() should be called to insert the row.
There are other methods of ResultSet object that are used to position the virtual cursor in addition to next().
Those are
first()- moves the curser to first row.
last()- moves the curser to the last row.
previous()-moves the curser to the previous row.
absolute()-moves to a specific row as the parameter passed
relative()-moves to a row relative to another row
getRow()-returns the current row number.

Find whether ResultSet is scrollable:
Some JDBC drivers may not support all these features and therefore they are unable to return a scrollable ResultSet.

To know about it,
boolean forward,sensitive,insensitive;
Forward=DatabaseMetaData.supportsResultSetType(TYPE_FOREWARD_ONLY);
sensitive=DatabaseMetaData.supportsResultSetType(TYPE_SCROLL_SENSITIVE);
Forward=DatabaseMetaData.supportsResultSetType(TYPE_SCROLL_INSENSITIVE);

Another way to know it:
Int type=ResultSet.getType();
1003-ResultSet.TYPE_FOREWARD_ONLY
1004-ResultSet.TYPE_SCROLL_INSENSITIVE
1005-ResultSet.TYEP_SCROLL_SENSITIVE

Find whether ResultSet is updatable:
Int concurrency=ResultSet.getConcurrency();
1007-ResultSet.CONCUR_READ_ONLY
1008-ResultSet.CONCUR_UPDATABLE

EXAMPLE:
Type-1 driver used. Set rt.jar in classpath.
import java.sql.*;
class demo
{
public static void main(String[] args){
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:dsn","system","oracle"); stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs=stmt.executeQuery("select * from emp");
ResultSetMetaData hi=rs.getMetaData();
System.out.println(hi.isReadOnly(2));
System.out.println(rs.getType());//whether scrollable
System.out.println(rs.getConcurrency());//whether updatable
rs.last();
System.out.println(rs.getString(2));
rs.previous();
System.out.println(rs.getString(2));
rs.first();
System.out.println(rs.getString(2));
rs.absolute(2);//moves to second position
System.out.println(rs.getString(2));
rs.relative(1);//moves one relative to previous i.e. 2 to 3
System.out.println(rs.getString(2));
rs.absolute(1);
System.out.println("empid in the first row is:"+rs.getString(1));
System.out.println("The current row is:"+rs.getRow());
rs.updateInt(1,10);//1st column value changed to 10
rs.updateRow();
System.out.println("name in the first row afterchange:"+rs.getString(1));
}catch(Exception e){
System.out.println("the exception is::::->"+e);
}
finally{
try{
rs.close();
stmt.close();
con.close();}
catch(Exception e){
System.out.println("2nd try block exception:::->"+e);
}}}};

No comments:

Post a Comment