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.createStateme
  nt(
• 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.executeQ
  uery(
• "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 lt ?"
• "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 example uses 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,
• the beforeFirst() 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,
• the first() 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.createSta
  tement(
• 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 tables 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. its 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()

51
What Database Changes does a Result Set See ?

• What Database Changes does a Result Set Sees
  when changes are made to the column values for
  rows that were initially read from the database.
• What does see mean? By this, we mean what value
  a get method returns if the column value
  originally read from the database by that result
  set is changed.
• Changes to column values can be made by two
  sources

52
What Database Changes does a Result Set See ?

• Changes to column values can be made by two
  sources
• Updatable result set changes
• we refer to these changes as internal
• because they are changes that are made using the
  update methods by that result set
• Other database transactions that may be
  performing inserts, updates, or deletes that
  change the rows originally read by the result set
• we refer to these changes as external
• because they are changes that are made outside
  of the result set

53
What Database Changes does a Result Set See

• Depending on the type of the result set (forward
  only, etc..), that result set may see different
  rows than those originally read if there have
  been internal and /or external changes.
• The following table shows the default visibility
  for three types of result sets. But you can
  override them by refreshing the current row in
  the result set using the refreshRow() Method.

54
What Database Changes does a Result Set See

• The following table

55
What Database Changes does a Result Set See

• From this table you can see
• a forward only result set can only see internal
  updates
• a scrollable insensitive result set can only see
  internal updates and deletes
• a scrollable sensitive result set can only see
  internal updates and deletes and external updates
• Note
• only one external change is seen, an external
  update
• and only by a scrollable sensitive result set.
• Also, internal inserts arent seen by any result
  set type

56
The refreshRow() Method

• The following Example Program
• AdvResultSetExample3.java
• shows the use of the refreshRow()
• method to refresh an updated row in an
  updatable scrollable insensitive result set

57
AdvResultSetExample3.java

• create a Statement object from which a
  scrollable insensitive ResultSet object will be
  created
• Statement myStatement myConnection.createSta
  tement(
• ResultSet.TYPE_SCROLL_INSENSITIVE,
• ResultSet.CONCUR_UPDATABLE
• )

58
AdvResultSetExample3.java

• create a ResultSet object
• ResultSet customerResultSet
  myStatement.executeQuery(
• "SELECT id, first_name, last_name, dob,
  phone "
• "FROM customers"
• )

59
AdvResultSetExample3.java

• display customer 2's id and last name
• customerResultSet.absolute(2)
• System.out.println("id "
• customerResultSet.getInt("id"))
• System.out.println("last_name "
• customerResultSet.getString("last_name"))

60
AdvResultSetExample3.java

• update customer 2's last name to "Jones"
  using a separate Statement object - this is an
  external update
• System.out.println("Updating customer 2's
  last name to 'Jones'")
• Statement updateStatement
  myConnection.createStatement()

61
AdvResultSetExample3.java

• updateStatement.execute(
• "UPDATE customers "
• "SET last_name 'Jones' "
• "WHERE id 2"
• )
• myConnection.commit()

62
AdvResultSetExample3.java

• refresh the current row, in this case row
  2
• System.out.println("Refeshing row 2")
• customerResultSet.refreshRow()

63
AdvResultSetExample3.java

• re-display row 2's id and last name
• System.out.println("id "
• customerResultSet.getInt("id"))
• System.out.println("last_name "
• customerResultSet.getString("last_name"))

64
Scrollable Sensitive Result Sets

• The following Example Program
  AdvResultSetExample4.java
• shows the visibility of changes to an updatable
  scrollable sensitive result set

65
AdvResultSetExample4.java

• disable auto-commit mode
• myConnection.setAutoCommit(false)

66
AdvResultSetExample4.java

• create a Statement object from which a
  scrollable sensitive ResultSet object will be
  created
• Statement myStatement myConnection.createSta
  tement(
• ResultSet.TYPE_SCROLL_SENSITIVE,
• ResultSet.CONCUR_UPDATABLE
• )

67
AdvResultSetExample4.java

• set the fetch size to 1
• myStatement.setFetchSize(1)
• create a ResultSet object
• ResultSet customerResultSet
  myStatement.executeQuery(
• "SELECT id, first_name, last_name, dob,
  phone "
• "FROM customers"
• )

68
AdvResultSetExample4.java

• System.out.println("Retrieved rows from
  customers table")
• display row 2's id and last name
• customerResultSet.absolute(2)

69
AdvResultSetExample4.java

• System.out.println("id "
• customerResultSet.getInt("id"))
• System.out.println("last_name "
• customerResultSet.getString("last_name"))

70
AdvResultSetExample4.java

• update customer 2's last name to "Jones" using
  a separate Statement object - this is an
  external update
• System.out.println("Updating customer 2's
  last name to 'Jones'")
• Statement updateStatement
  myConnection.createStatement()
• updateStatement.execute(

71
AdvResultSetExample4.java

• "UPDATE customers "
• "SET last_name 'Jones' "
• "WHERE id 2"
• )
• myConnection.commit()

72
Meta Data

• The following Example Program
  MetaDataExample.java
• illustrates the use of some methods described in
  the previous section to obtain and display result
  set and database meta data

73
Meta Data

• You can use meta data to get information about
  the database and tables that your program
  accesses.
• There are two types of meta data

74
Meta Data

• Result set meta data
• This provides information about the table and
  columns accessed by a ResultSet

75
Meta Data

• Database meta data
• This provides information about the database to
  which a Connection object is linked to
• get and display result set meta data
• ResultSetMetaData myRSMetaData
  customerResultSet.getMetaData()
• get and display database meta data
• DatabaseMetaData myDBMetaData
  myConnection.getMetaData()

76
Meta Data

• Once you have interface ResultSetMetaData
  object, you can use various methods that read the
  meta data for the ResultSet object
• public int getColumnCount() throws SQLException
• Returns the number of columns in this ResultSet
  object.

77
Meta Data

• public String getColumnName(int column)
• throws SQLException
• Get the designated column's name.
• Parameters
• column - the first column is 1, the second is 2,
  ...
• Returns
• column name

78
Meta Data

• public int getColumnType(int column) throws
  SQLException
• Retrieves the designated column's SQL type.
• Parameters
• column - the first column is 1, the second is 2,
  ...
• Returns
• SQL type from java.sql.Types

79
Meta Data

• public int getColumnDisplaySize(int column)
• throws SQLException
• Indicates the designated column's normal maximum
  width in characters.
• Parameters
• column - the first column is 1, the second is 2,
  ...
• Returns
• the normal maximum number of characters allowed
  as the width of the designated column

80
Meta Data

• public int isNullable(int column) throws
  SQLException
• Indicates the nullability of values in the
  designated column.
• Parameters
• column - the first column is 1, the second is 2,
  ...
• Returns
• the nullability status of the given column one
  of columnNoNulls, columnNullable or
  columnNullableUnknown

81
Meta Data

• public int getPrecision(int column) throws
  SQLException
• Get the designated column's number of decimal
  digits.
• Parameters
• column - the first column is 1, the second is 2,
  ...
• Returns
• precision

82
Meta Data

• public int getScale(int column) throws
  SQLException
• Gets the designated column's number of digits to
  right of the decimal point.
• Parameters
• column - the first column is 1, the second is 2,
  ...
• Returns
• scale

83
Meta Data

• public interface DataBaseMetaData
• This interface is implemented by driver vendors
  to let users know the capabilities of a Database
  Management System (DBMS) in combination with the
  driver based on JDBCTM technology ("JDBC driver")
  that is used with it.
• Once you have interface DataBaseMetaData object
• you can use different useful methods for reading
  database meta data

84
Meta Data

• public String getDatabaseProductName() throws
  SQLException
• Retrieves the name of this database product.
• Returns
• database product name

85
Meta Data

• public String getDatabaseProductVersion() throws
  SQLException
• Retrieves the version number of this database
  product.
• Returns
• database version number

86
Meta Data

• public String getURL() throws SQLException
• Retrieves the URL for this DBMS.
• Returns
• the URL for this DBMS or null if it cannot be
  generated

87
Meta Data

• public String getUserName() throws SQLException
• Retrieves the user name as known to this
  database.
• Returns
• the database user name