ResultSet is an interface in java.sql package which has methods to retrieve and manipulate the results of an executed query. Broadly ResultSet provides the functionalities for navigation, retrieval and update .
ResultSet exhibit different characteristics. They can be classified into type, concurrency and cursor hold ability.
1) ResultSet Types:
The type of ResultSet object can be defined on two aspects the navigation of records and how sensitive the ResultSet is to concurrent changes to the underlying data store.
Base on these we have 3 different ResultSet types:
- TYPE_FORWARD_ONLY : The ResultSet can only be scrolled or navigated in forward direction. This is the default type and is not sensitive to the underlying data store changes.
- TYPE_SCROLL_INSESITIVE: The ResultSet can be scrolled either in forward or backward direction. We can move to absolute location or relative record position using this type of scrolling. However Result set is insensitive or insulated from the underlying data store changes
- TYPE_SCROLL_SENSITIVE: This type is exactly has the same functionality as the TYPE_SCROLL_INSESITIVE in terms of scrolling however the result set is sensitive to the changes in underlying data store.
2) RsultSet Concurrency:
They determine the level of update functionality provided. There on two categories based on this criteria:
- CONCUR_READ_ONLY : The ResultSet type object cannot be used for updates to data store.
- CONCUR_UPDATABLE: The ResultSet typeobject can be used for updating the data store.
3) Result Set Holdablity :
By default if we can Connection.commit() after the transaction, the ResultSet object will get closed automatically. This default behavior may not be desired in some business scenarios. JDBC does provide a way to control this through java application.
The following two constants of ResultSet interface can be passed to Connection methods createStatement,prepareStatement and prepareCall to control this behavior of Connection.commit() call:
- HOLD_CURSORS_OVER_COMMIT : ResultSet's are not closed on an commit. Instead they are held. These type is very useful if the ResultSet are read only.
- CLOSE_CURSORS_AT_COMMIT: ResultSet closed immediately on a commit call. This can produce better performance over the later one.
Applying the Above in Coding:
ResultSet can be created by below methods:
1) Using ExecuteQuery() on a Statement type object calling a SELECT query.
2) The second approach is to get a Statement type object from the Connection object using createStatement() method. After a statement is created we can issue getResultSet() call to get the corresponding ResultSet type object.
Example1:
Statement st=connection.CreateStatement();
Boolean bVal=st.execute("SELECT * FROM EMP");
ResultSet st=st.getResultSet();
The Connection type object provides 3 overloaded createStatement() methods:
1) Statement createStatement() : This returns a default Statement type object which produces the default ResultSet on calling an executeQuery. The returned ResultSet is ForwardOnly,ReadOnly and cannot hold the result type on transaction commit(). The example [Example1] provided above demonstrates this.
2) Statement createStatement(int resultSetType, int resultSetConcurrency)This
provides flexibility over the plain createStatement() in a way that we can define the ResultType we want after execution of the query.
Example 2: If we want a scrollable ,sensitive,updatable ResultSet we can call as below:
st=connection.CreateStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
Please note that we can do the same for a PrepareStatement or CallableStatement using prepareStatement() or prepareCall() respectively.
3) Statement createStatement((int resultSetType, int resultSetConcurrency, int resultSetHoldability)
This provides flexibility for even mentioning whether ResultSet is Holdable or not after a commit.
