CS276 Advanced Oracle Using Java

1. CS276 Advanced Oracle Using Java Chapter 4 Advanced Result Sets

2. Scrollable Result Sets In JDBC 1.0,you could only move forward one row at a time in a result set, when you read rows stored in a database. With JDBC 2.0, a new class of result sets known as scrollable result. Scrollable result sets allow you to move backwards as well as forwards through rows in a result set, and also allow to jump to any row directly, using either the actual row number stored in the result set, or a row number.

3. Scrollable Result Sets Scrollable result sets may be: • updatable • Sensitive The following example creates a Statement object, which specifies that any resultSet objects created from it are to be scrollable and read-only

4. Scrollable Result Sets Statement myStatement = myConnection.createStatement( ResultSet.TYPE_Scroll_INSENSITIVE, ResultSet.CONCUR_READ_ONLY

5. Scrollable Result Sets The Result Set TYPE may be specified using one of the following int constants: • ResultSet.TYPE_FORWARD_ONLY • Specifies that ResultSet objects are not scrollable. This is default

6. Scrollable Result Sets • ResultSet.TYPE_Scroll_INSENSITIVE • Specifies that ResultSet objects are scrollable but not sensitive to changes in the database • ResultSet.TYPE_Scroll_SENSITIVE • Specifies that ResultSet objects are scrollable and sensitive to changes in the database

7. Scrollable Result Sets The Result Set CONCURRENCY determines whether or not your resultset objects can modify the rows and may be specified using one of the following int constants: ResultSet.CONCURE_READ_ONLY (ResultSet can not make changes to the database), ResultSet.CONCURE_UPDATABLE (ResultSet can make changes to the database)

8. Scrollable Result Sets The following example creates a ResultSet object that uses the result set type and concurrency previously set for myStatement: ResultSet customerResulrSet = myStatement.executeQuery( "SELECT id, first_name, last_name, dob, phone " + "FROM customers " + "ORDER BY id", );

9. Scrollable Result Sets The following examples create a PreparedStatement object from which an insensitive scrollable ResultSet object will be created PreparedStatement myPrepStatement = myConnection.prepareStatement( "SELECT id, first_name, last_name, dob, phone " + "FROM customers " + "WHERE id <= ?" + "ORDER BY id", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); // bind the int value 5 to the PreparedStatement object myPrepStatement.setInt(1, 5); // create a ResultSet object and execute the query ResultSet customerResultSet = myPrepStatement.executeQuery();

10. Navigating a Scrollable Result Set With a scrollable result set, you can use many more methods to navigate the rows in a more flexible manner( than only next()): • next() • Navigates to the next row stored in a result set. If there is no row to move forward to in the result set, this method returns false; otherwise, it returns true

11. Navigating a Scrollable Result Set • previous() • Navigates to the previous row stored in a result set. If there is no row to move back to in the result set, this method returns false; otherwise, it returns true • first() • Navigates to the first row stored in a result set. If there is no rows in the result set, this method returns false; otherwise, it returns true • last() • Navigates to the last row stored in a result set. If there is no rows in the result set, this method returns false; otherwise, it returns true

12. Navigating a Scrollable Result Set • beforeFirst() • Navigates to a position before the first row. If there is no rows in the result set, calling this method has no effect • afterLast() • Navigates to a position after the last row. If there is no rows in the result set, calling this method has no effect

13. Navigating a Scrollable Result Set • absolute(int rowNumber) • Navigates to a row specified by rowNumber. • If the row number is positive, the cursor moves to the given row number with respect to the beginning of the result set. The first row is row 1, the second is row 2, and so on.

14. Navigating a Scrollable Result Set • absolute(int rowNumber) • If the given row number is negative, the cursor moves to an absolute row position with respect to the end of the result set. For example • calling the method absolute(-1) positions the cursor on the last row; • calling the method absolute(-2) moves the cursor to the next-to-last row, and so on.

15. Navigating a Scrollable Result Set • absolute(int rowNumber) • An attempt to position the cursor beyond the first/last row in the result set leaves the cursor before the first row or after the last row. • Note: • Calling absolute(1) is the same as calling first(). • Calling absolute(-1) is the same as calling last(). • If there is no rows in the result set, this method returns false • otherwise, it returns true

16. Navigating a Scrollable Result Set • relative(int relativeRowNumber) • Navigates to a row relative to the current row. • Moves the cursor a relative number of rows, either positive or negative. • Attempting to move beyond the first/last row in the result set positions the cursor before/after the first/last row.

17. Navigating a Scrollable Result Set • relative(int relativeRowNumber) • Calling relative(0) is valid, but does not change the cursor position. Note: • Calling the method relative(1) is identical to calling the method next() • Calling the method relative(-1) is identical to calling the method previous().

18. Navigating a Scrollable Result Set • relative(int relativeRowNumber) • If there is no rows in the result set, this method returns false • otherwise, it returns true • The following exampleuses a while loop to navigate and display the five rows stored in customerResultSet in reverse order.

19. Navigating a Scrollable Result Set // display the rows in the ResultSet in reverse order System.out.println("Customers in reverse order"); customerResultSet.afterLast(); while (customerResultSet.previous()) { System.out.println("id = " + customerResultSet.getInt("id")); System.out.println("first_name = " + customerResultSet.getString("first_name"));

20. Navigating a Scrollable Result Set // display the rows in the ResultSet in reverse order System.out.println("last_name = " + customerResultSet.getString("last_name")); System.out.println("dob = " + customerResultSet.getString("dob")); System.out.println("phone = " + customerResultSet.getString("phone")); } // end of while loop

21. Navigating a Scrollable Result Set • The next example navigates to row #3: • customerResultSet.absolute(3); • If you pass a negative number, it will navigate to a row counting the back from the last row. For example, the following navigates to row #4: • customerResultSet.absolute(-2);

22. Navigating a Scrollable Result Set • Note: absolute( -1) is equivalent to last() • The Next example navigates to row #2 by navigating back two rows relative to current row(#4): • customerResultSet.relative(-2); • Note: You can not use relative positioning from before the first row or after the last row. You will cause a SQL exception

23. Determining the Position in a Scrollable Result Set When you use the scrollable result set methods to navigate rows, you might lose track of where you are. You can check current position using the following methods: • getRow() • Retrieves the current row number. The first row is number 1, the second number 2, and so on. • Returns: • the current row number; 0 if there is no current row • isFirst() • Returns true if the current row is the first row, false otherwise

24. Determining the Position in a Scrollable Result Set isLast() • Retrieves whether the cursor is on the last row of this ResultSet object. • Note: Calling the method isLast may be expensive because the JDBC driver might need to fetch ahead one row in order to determine whether the current row is the last row in the result set. • Returns: • true if the cursor is on the last row; false otherwise

25. Determining the Position in a Scrollable Result Set isBeforeFirst() • Retrieves whether the cursor is before the first row in this ResultSet object. • Returns: • true if the cursor is before the first row; false if the cursor is at any other position or the result set contains no rows • isAfterLast() • Retrieves whether the cursor is after the last row in this ResultSet object. • Returns: • true if the cursor is after the last row; false if the cursor is at any other position or the result set contains no rows

26. Determining the Position in a Scrollable Result Set • In the following example, • thebeforeFirst() navigates before first row • the isBeforeFirst() is used to check the condition • the getRow() is called to display the returned value( 0 because there is no valid current row) customerResultSet.beforeFirst(); if (customerResultSet.isBeforeFirst()) { System.out.println("Before first row"); System.out.println("Current row = " + customerResultSet.getRow()); }

27. Determining the Position in a Scrollable Result Set In the following example, • thefirst() navigates to the first row • the getRow() is called to display the returned value( 1 for first row System.out.println("Going to first row"); customerResultSet.first(); System.out.println("Current row = " + customerResultSet.getRow());

28. Determining the Position in a Scrollable Result Set Example Program: AdvResultSetExample1.java • The example shows how to use an insensitive scrollable result set And Performs the following tasks:

29. AdvResultSetExample1.java • register the Oracle JDBC drivers • create a Connection object, and connect to the database as store_user using the Oracle JDBC Thin driver

30. AdvResultSetExample1.java • create a PreparedStatement object from which an insensitive scrollable ResultSet object will be created • bind the int value 5 to the PreparedStatement object • create a ResultSet object

31. AdvResultSetExample1.java • display the rows in the ResultSet in reverse order using methods • customerResultSet.afterLast(); • customerResultSet.previous(); • navigate to row #3 • using method • customerResultSet.absolute(3);

32. AdvResultSetExample1.java • navigate back two rows to row #1 using method: • customerResultSet.relative(-2); System.out.println("Going back two rows");

33. AdvResultSetExample1.java • navigate before first row customerResultSet.beforeFirst(); if (customerResultSet.isBeforeFirst()) { System.out.println("Before first row"); System.out.println("Current row = " + customerResultSet.getRow());

34. AdvResultSetExample1.java • navigate to the first row using method: System.out.println("Going to first row"); customerResultSet.first();

35. Updatable Result Set Example Program: AdvResultSetExample2.java

36. Updatable Result Set Updatable Result Sets allow you to make changes to rows in the database. The following int constants are used to indicate the result set concurrency: ResultSet.CONCURE_READ_ONLY

37. Updatable Result Set (ResultSet can not make changes to the database), ResultSet.CONCURE_UPDATABLE (ResultSet can make changes to the database)

38. Updatable Result Set Example: create a Statement object from which an updatable // ResultSet object will be created Statement myStatement = myConnection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );

39. Updatable Result Set Example: // create a ResultSet object ResultSet customerResultSet = myStatement.executeQuery( "SELECT id, first_name, last_name, dob, phone " + "FROM customers" );

40. Updatable Result Set The limitations for the query used with an updatable result set. • You can only use a single table • You must select the table’s primary key column and all the other NOT NULL columns • You cannot use an ORDER BY

41. Updatable Result Set • You must only select column values • You cannot use SELECT *. For example: SELECT customers.* it’s ok

42. Updatable Result Set Updating a Row • You may use updateString() to update a CHAR or Varchar2

43. Updatable Result Set • updateDate() to update a DATE column • updateInt(), updateDouble • The update methods accept two parameters • The column to be updated • The new value

44. Updatable Result Set Updating a Row Example: we want to update the customer in row #2 and set the f name to “Greg” • Navigate to row #2 customersResultSet.absolute(2);

45. Updatable Result Set Updating a Row • Update firstname and dob customersResultSet.updateString(“first_name”, “Greg”); Java.sql.Date dob = new java.sqlDate(69,1,1); customersResultSet.updateDate(“dob”, dob”);

46. Updatable Result Set Updating a Row You can undo you changes: use cancelRowUpdates() • Send changes to the database: customersResultSet.updateRow(); 4. Commit: myConnection.commit(); Now you cannot use cancelRowUpdates(), You can use only rollback()

47. Updatable Result Set Deleting a Row Example: we want to update the customer in row #2 and set the f name to “Greg” customersResultSet.absolute(5); customersResultSet.deleteRow;

48. Updatable Result Set Conflicts When Updating and Deleting a Row. Solution: use FOR UPDATE clause to lock the rows: create a ResultSet object ResultSet customerResultSet = myStatement.executeQuery( "SELECT id, first_name, last_name, dob, phone " + "FROM customers“ + “FOR UPDATE” );

49. Updatable Result Set Inserting a Row • Create a blank row in the result set System.out.println("Inserting new row"); customerResultSet.moveToInsertRow(); • Update data customerResultSet.updateInt("id", 6); customerResultSet.updateString("first_name", "Jason");

50. Updatable Result Set Inserting a Row customerResultSet.updateString("last_name", "Price"); customerResultSet.updateDate("dob", dob); • Insert row customerResultSet.insertRow(); customerResultSet.moveToCurrentRow();