Scrollable ResultSet

In Jdbc Programming we have two ResultSets They are
1)Non Scrollable/Sequential ResultSet
2)Scrollable ResultSet


Def for Non Scrollable ResultSet: a non scrollable ResultSet is one in which the data is retrieved only in forward direction but not in backward direction

Def for Scrollable ResultSet: a non scrollable ResultSet is one in which the data is retrieved  in forward direction as well as backword direction 

before JDBC2.0 we have only non scrollable ResultSet. But from JDBC2.0 on wards we have a concept called Scrollable ResultSet

the basic advantage of scrollable resultset improves the performance of JDBC application by retriving the records within less time

In order to convert a non-scrollable resultset into scrollable resultset we need to use method in java.sql.Connection
                                              java.sql.Connection
                                                          |
                       public Statement createStatement(int type,int mode)
 In the above method Type refer a public static final datamember present in java.sql.ResultSet

About TYPE Variables
                                          java.sql.ResultSet
                                                      |
          1.  public static final int TYPE_FORWARD_ONLY(default)
          2.  public static final int TYPE_SCROLL_INSENSITIVE
          3.  public static final int TYPE_SCROLL_SENSITIVE

By default ResultSet Interface Object allowes us to retrieve the data only in forward direction. hence data member is default the data member in every JDBC application
DataMembers 2& 3 allowces us to convert non scrollableresultSet into scrollable resultset

But when we use data member 2 then we never pickup the changes of database to the resultset object

when we use data member 3 then what ever the changes we do in the database they will be reflected to resultset object. if  we don't get changes then call refreshRow() in ResultSet Object

About MODE Variables
In int mode represents the following public static final datamembers in java.sql.ResultSet Interface
                                                java.sql.ResultSet
                                                              |
                          1.  public static final int CONCUR_READ_ONLY
                         2.  public static final int CONCUR_UPDATABLE


Data member 1 represents what ever the changes we do on ResultSet object,those changes never reflect to database

Data member 2 represents what ever the changes we do on ResultSet Object those chnges will be reflected to database

ResultSet Interface Methods
1.public  boolean isBeforeFirst()
2.public boolean isAfterLast()
3.public void beforeFirst()
4.public void afterLast()
5.public void first(0
6.public void last()
7.public boolean next()
8.public boolean previous()
9.public void relative(int)
10.public void absolute(int)

Assume that emp table is having 14 records now the following program by using above methods.


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ScrollableResult {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "system123");
Statement st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs=st.executeQuery("select * from emp");
System.out.println("==============Now you in Forward Direction============");
while(rs.next())
{
System.out.println(rs.getString(1)+"       "+rs.getString(2));
}
System.out.println("==============Now you in BackWord Direction============");
while(rs.previous())
{
System.out.println(rs.getString(1)+"       "+rs.getString(2));
}
System.out.println("==========Now Go To 7 th Record===================");
rs.absolute(7);
System.out.println(rs.getString(1)+"         "+rs.getString(2));
}
}

Inserting Record though ResultSet:

In order to insert a record into a database through ResulSet object perform the following steps

  1. call a method moveToInsertRow() w.r.t ResultSet object

           rs.moveToInsertRow()

  2. call updateXXX() for setting the Record values

                  Where XXX represents fundamental datatype

       rs.updateInt(1,100)

       rs.updateString(2,"sathya");

      rs.updateString(3,"S,R Nagar");

3.what ever the values we put in ResultSet object, to insert those values in the database we use a method called insertRow()

      rs.insertRow();

Example Program for Inserting Row

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class InsertRow {

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

// TODO Auto-generated method stub

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hyd", "system", "system123");

Statement st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs=st.executeQuery("select * from std1");

while(rs.next())

{

}

rs.moveToInsertRow();

rs.updateInt(1, 10);

rs.updateString(2, "rs1");

rs.updateString(3, "coim");

rs.insertRow();

System.out.println("finished......");

con.close();

}

}

Updating a Record

Steps to update a record through ResultSet

1. Identify which record we want update

    rs.absolute(5)

2.call upateXXX(-,-) method for updating column of record which is pointed by ResultSet object

     rs.updateString(2,"abcde");//second column of 5 th record is updated

3.to update record into the database we have to call updateRow()

     rs.updateRow()

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class InsertRow {

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

// TODO Auto-generated method stub

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hyd", "system", "system123");

Statement st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs=st.executeQuery("select * from std1");

while(rs.next())

{

}

rs.absolute(1);

rs.updateString(2, "abcde");

rs.updateRow();

System.out.println("finished......");

con.close();

}

}

Delete the Record through ResultSet

1. first call absoulte() method to which row you want to delete

      rs.absoulte(1);// to delete first record

2. call deleteRow() to delete from Database

    rs.deleteRow()

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class InsertRow {

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

// TODO Auto-generated method stub

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con=DriverManager.getConnection("jdbc:odbc:hyd", "system", "system123");

Statement st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs=st.executeQuery("select * from std1");

while(rs.next())

{

}

rs.absolute(1);

rs.deleteRow();

System.out.println("finished......");

con.close();

}

}