import com.sun.rowset.CachedRowSetImpl;
import java.sql.SQLException;
import javax.sql.rowset.*;
import java.sql.*;
import java.sql.DriverManager;
import java.io.*;


public class DisconnectedExample {

private static final BufferedReader in =
    new BufferedReader(new InputStreamReader(System.in));

	public static void main(String[] args) 
	{

	   try
	   {
		Class.forName("com.ibm.db2.jcc.DB2Driver");
		CachedRowSet crs = new CachedRowSetImpl();
		crs.setUsername("db2admin"); 
		crs.setPassword("db2admin"); 
		crs.setUrl("jdbc:db2://localhost:50000/cachedex"); 
		crs.setCommand("SELECT id,firstname,lastname from cachetbl");
		crs.execute();

		System.out.println("---------------------------");
		// display size of cached row set
		System.out.println("Size: " + crs.size() + " records");
		// display records in cachedrowset
		while (crs.next())
		{
			System.out.println(crs.getRow() + " - " + 
			crs.getString("firstname") + " " + 
			crs.getString("lastname"));
		
		}
		System.out.println("---------------------------");

		System.out.println("Showcase scrollability");
		// move backwards through rowset
		// scroll to last row
		crs.last();
		// iterate to next row
		while (crs.previous())
		{
			// report current row contents
			System.out.println(crs.getRow() + " - " + crs.getString("lastname"));
		}
		
		
		System.out.println("---------------------------");

		try
		{
	           System.out.println("Turn off Db2 service now and press any key to continue."); 
		   String input = in.readLine();
    		}
    		catch (IOException e)
		{
		   e.printStackTrace();
		}

		System.out.println("Demonstration of how to update cached row set");		
		crs.updateString("lastname","Kaur");
		// commit changes to cached portion of rowset
		crs.updateRow();
		System.out.println(crs.getRow() + " - " + crs.getString("lastname"));
		
		try
		{
	           System.out.println("Turn on Db2 service now and press any key to continue."); 
		   String input = in.readLine();
    		}
    		catch (IOException e)
		{
		   e.printStackTrace();
		}

		
		// force persistence of changes to database
		crs.acceptChanges();

		try
		{
	           System.out.println("At this point, you can query the " +
			"cachetbl to see that the changes have been persisted.\n" +
			"Press any key when you are ready to move on."); 
		   String input = in.readLine();
    		}
    		catch (IOException e)
		{
		   e.printStackTrace();
		}
		
		System.out.println("---------------------------");
		System.out.println("Demonstration of how to insert a record " +
				"into the disconnected object.");
		
		// move the cursor to a blank row 
		crs.moveToInsertRow();
		// populate the new row
		crs.updateInt(1,01234);
		crs.updateString(2,"Judith");
		crs.updateString(3,"Smith");
		// insert the new row
		crs.insertRow();
		// move cursor back to previous position
		crs.moveToCurrentRow();
		// synchronize changes to database
		crs.acceptChanges();
		

		try
		{
	           System.out.println("At this point, you can query the " +
			"cachetbl to see that the insert has taken place.\n" +
			"Press any key when you are ready to continue."); 
		   String input = in.readLine();
    		}
    		catch (IOException e)
		{
		   e.printStackTrace();
		}

		System.out.println("---------------------------");
		System.out.println("Demonstration of how to delete a record " +
				"in the disconnected object.");

		// delete row (where the cursor is currently positioned)
		crs.deleteRow();
		// synchronize changes to database
		crs.acceptChanges();
		try
		{
	           System.out.println("At this point, you can query the " +
			"cachetbl to see that the deletion has taken place.\n" +
			"Press any key when you are ready to continue."); 
		   String input = in.readLine();
    		}
    		catch (IOException e)
		{
		   e.printStackTrace();
		}

	  }
	catch(SQLException sqle)
	{
		sqle.printStackTrace();
	}
   	catch (ClassNotFoundException cnfe)
	{
		cnfe.printStackTrace();
	}

}
}