Recent Pos

JDBC Advanced ResultSet – Scrollable & Updatable ResultSet

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

Advanced ResultSet:

⇒ In the earlier post we have discussed about the basic ResultSet that allows us to read data from only in forward direction i.e. Using next() method.

⇒ The JDBC 2.0 introduces a support for advanced ResultSet that allows to read the data in any direction and even allow to update the data.
 
 
jdbc scrollable updatable resultset
 

Scrollable ResultSet:

⇒ This type of ResultSet allows to scroll within the rows in any direction and in any steps.

⇒ To get the ResultSet of the required type we need to create the JDBC Statement specifying the type. The following overloaded methods of Connection can be used to do this:
 

 
⇒ The rs_type can be any of the following values:
 
ResultSet.TYPE_FORWARD_ONLY
 
ResultSet.TYPE_SCROLL_SENSITIVE
 
ResultSet.TYPE_SCROLL_INSENSITIVE
 
⇒ The first one is the default in case of not specifying the ResultSet type explicitly.

⇒ The later two are used for scrolling within the rows.
 
⇒ The rs_concur can be any of the following two values:
 
ResultSet.CONCUR_READ_ONLY
 
ResultSet.CONCUR_UPDATABLE
 
⇒ The Scrollable ResultSet is allowed to use the following methods to move the row pointer:
 
1. first() – Move the pointer to first row.
 
2. last() – Move the pointer to last row.
 
3. next() – Move the pointer to next row from current position.
 
4. previous() – Move the pointer to previous row from the current position.
 
5. absolute(int inxex) – Move the pointer to the row at given index (index starts with 1).
 
 
Example:

rs.absolute(3) – Move to 3rd row

rs.absolute(-3) – Move to last 3rd row (counting from last)

rs.absolute(0) – SQLException

6. relative(int position) – Will move the pointer to (current_row_index+position) row
 
Example:

⇒ If current position is in 2nd row and position we provide 2 then position will be 4.

⇒ All the above 6 methods have boolean return type. They return true if in case able to move to the valid row ro read the data otherwise returns false.
 
⇒ In addition we can use the following two methods also:

1. void beforeFirst() – This will move the pointer to the before first row. However it is not a valid position to read the values but in some algorithms we may want to go before first and start reading moving ahead.

2. void afterLast() – This will move the pointer to the after last row.
 
 
Example:

 
Download this example: ScrollableResultSetExample.zip
 
 

What is the difference between TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE?

⇒ Both these ResultSet types allows to scroll within the rows in the ResultSet.

⇒ The only difference is the TYPE_SCROLL_SENSITIVE will refresh the Cursor every time it is accessed from server. Where the TYPE_SCROLL_INSENSITIVE will not. In simple after getting the ResultSet if some other client modifies the data in the database those changes will not be effected into this ResultSet if it is TYPE_SCROLL_INSENSITIVE, will be effected in case of TYPE_SCROLL_SENSITIVE.
 
Note: The SENSITIVE ResutlSet is more costlier to system compared to INSENSITIVE.
 
⇒ Thus only in case if we find that the data selected into ResultSet is possible to modification by other clients and is important to get effected to read the changes in our application, then only use TYPE_SCROLL_SENSITIVE otherwise TYPE_SCROLL_INSENSITIVE will be best.
 
Note: All teh databases and JDBC drivers may not support all the type of ResultSet.
 
 

Updatable ResultSet:

⇒ To get the UpdatableResultSet we need to use ResultSet.CONCUR_UPDATABLE while creating the Statement for a ResultSet concurrency.

⇒ The Updatable ResultSet allows us to insert, update and delete the records using ResultSet.
 
 
Using Updatable ResultSet to update the data:

⇒ The following three steps are required to implement Updatable ResultSet:

Step 1: Move the row pointer to the row that we want to update.

Step 2: Update the column values.

-We use the following updateXxx() methods for updating the column values:

-Similarly for all the datatypes
 
 
⇒ We have all these methods overloaded taking the first arg as String i.e. col_name
Step 3: Update the row-

-We use updateRow() method of ResultSet to update the row.
 
 
Example:

 
Note: 1. The updateRow() method will update the row data to the database.

2. After changing the values of the columns without calling the updateRow() method if we move the row pointer from the current row, the modifications will not be effected into the database.

3. The ResultSet may show the updated data into it or may not, it varies from database to database and drivers. We can use the following methods or DatabaseMetaData to find is this Driver/Database shows the updated data in the ResultSet:

4. The following method of ResultSet is used to find is the current updated by this ResultSet:

5. All the drivers may not support to find the updated row, we can find this support using the following method of DatabaseMetaData:

 
 
Using Updatable ResultSet to delete the row:

⇒ The following two steps are involved in doing this:

Step 1: Move the row pointer to the row that we want to delete.

Step 2: Delete the row. We use deleteRow() method of ResultSet to delete the current row.
 
 
Example: A program to print all the employees from the database and delete the employee records with the given empno:

 
Note: Like with the update, the deletes are also visible/not visible depending on the Driver/Database.

-We can use the following method of DatabaseMetaData to find is the driver works with deletes to show or not:

-This returns true in case if the deletes are visible (i.e. deleted rows are removed from ResutlSet), otherwise false.

-In case if deletes are visible (i.e. calling deleteRow()) the row will be removed from the ResultSet as well as from Database.

-In this case the following operations are performed:

1. Move the row pointer to the previous record

2. Shift all the rows below the deleted row advancing by 1. (i.e. current_row_index-1).

Note: In MySQL driver we have observed that the row pointer is not shifted to the previous record even the row at current index is deleted, instead it is advancing the below row to top (1 position).

In case of deletes are not visible:

-The deleted row will not be removed from the ResultSet.

-In this case we can use the following method of ResultSet to find whether this row was deleted:

 
 
Using Updatable ResultSet to insert a new row:

The following three steps are used to do this:

Step 1: Move to new empty row

Step 2: Update the values for the columns

Step 3: Insert the row

⇒ Moving the pointer to a new empty row:

-The following method of ResultSet is used to move the row pointer to the empty row:

⇒ Updating the values for the columns:

-The Step2 here and Step2 of updating the row are same.

Inserting the row:

-The following method of ResultSet is used for inserting the row:

 
 
Example:

 
 
⇒ The following method of ResultSet is used to move the row pointer to the row form where we jumped to the insert row:

⇒ Like after the insertRow() the inserted row would be available in the ResultSet or may not.

⇒ In case if the row is available in the ResultSet then at what index it will exist is decided by the driver.

⇒ Thus to continue with our logic we may want to get back to the position moving to the insert row.

⇒ We can use rowInserted() method to find is this row was inserted by this ResultSet.

Note: 1. In case of working with Oracle driver using * in the SELECT query will not return the updatable ResultSet.

2. In case of working with MySQL if the table doesn’t have a primary key for the column which selected, the updatable ResultSet is not returned.
 
 
Download this example: UpdatableResultSetExample.zip
 
 
<-Previous: Accessing Database Meta Data Using JDBC DatabaseMetaData | | Next->Reading Auto Generated Key Using JDBC

No Comments

Post a Comment