How to create Scrollable ResultSet and Updatable ResultSet ?

Normally if you create resultset you can iterate or move only in one direction
you can't move back.

With scrollable resultset you can iterate in both directions.You can go to last row
or firstrow or any particualr row using last(),first() and absolute(3) methods...

We will see an example now on how to create scrollable resultset

What is Updatable ResultSet ?

Generally you call executeUpdate on statement object to execute any updates right ?
But with Updatable ResultSet you can update the rows or insertrows or delte rows
using methods from resultset.

We will see an example below




/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package jdbcsample;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Rishitha
 */
public class ScrollableResultSetExample {
   
     public static Connection getConnection() {

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e) {

            System.out.println("Please add ojdbc6.jar in your classpath if you are using oracle,"
                    + "otherwise set the respective database driver jar file.In netbeans right click on"
                    + "libraries,click on add jar/folder and provide your jar file");

        }
        Connection connection = null;

        try {
            //XE -- Here Give your database name
            //hr -- Give Your database username
            //hr -- Give your database password
            //You have to add ojdbc.jar in your classpath

            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:XE", "hr",
                    "hr");
            System.out.println("Hurrah got connection " + connection);

        } catch (SQLException e) {

            System.out.println("Please check if database name username password are correct ?");

        }
        return connection;

    }
    public static void main(String args[]) throws SQLException{
       
          Connection con = getConnection();
        

//    DatabaseMetaData dbmd = con.getMetaData();
//  
//
//    if (dbmd.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE,
//                                          ResultSet.CONCUR_READ_ONLY)) {
//         System.out.println("Updatable ResultSets are supported ");
//    }
//    else {
//         System.out.println("Updatable ResultSets not supported ");
//    }
          Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          //st = con.createStatement();//un comment this and you will
          //get error because you cant move to first again using non scrollable
          //resultset
          ResultSet scrollRs = null;
         
          scrollRs = st.executeQuery("SELECT fname,lname FROM students ");
          while(scrollRs.next()){
             
              System.out.println(" "+scrollRs.getString(1)+" "+scrollRs.getString(2));
              System.out.println("Current Row is "+scrollRs.getRow());
             
          }
          //iterating the rows in reverse direction using previous method
           while(scrollRs.previous()){
             
              System.out.println(" "+scrollRs.getString(1)+" "+scrollRs.getString(2));
              System.out.println("Current Row is "+scrollRs.getRow());
             
          }
          
           scrollRs.last();//This is to go to last record
          
           System.out.println("Total No of Records in table are "+scrollRs.getRow());
           //scrollRs.getRow gives the no of the record you are on currently


           
           //Now we will see how to update
           //Now to create the statement object that produces scrollable and
           //updatable resultset you have to specify new arguments to statement
           //creation
           //If you observe I have modified second argument to CONCUR_UPDATABLE
           st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
           scrollRs = st.executeQuery("SELECT fname,lname,sid FROM students ");
           //Now we will move to last row and update it
          
           System.out.println("Current values of last row are ");
           scrollRs.last();
           System.out.println(" "+scrollRs.getString(1)+" "+scrollRs.getString(2));
          
           //Now we start modifying
           scrollRs.updateString("fname", "AnilModified");
           scrollRs.updateString("lname","KumarModified");
           scrollRs.updateRow();
          
           System.out.println("Values of last row are ");
         
           System.out.println(" "+scrollRs.getString(1)+" "+scrollRs.getString(2));
          
           //How to delte first Row
           //Move to first row using first method
           //and call deleteRow method
           scrollRs.first();
           scrollRs.deleteRow();
           scrollRs.last();
          
           System.out.println("After deleting Total No of Records in table are "+scrollRs.getRow());
     
            //How to insert a new row
           scrollRs.moveToInsertRow();
           scrollRs.updateString(1, "new Row");
           scrollRs.updateString(2, "new Row");
           scrollRs.updateInt(3,100);
           scrollRs.insertRow();
          
           scrollRs.last();
           System.out.println("Total no of rows are "+scrollRs.getRow());       


    }
   
   
   
}

Imp Note:

If you select * from students... updatable resultsets may not work.
You have to select specific columns like
select fname,lname from students;