ResultSet
A ResultSet is an object in JDBC API that is used to store/hold the data of database
table into computer’s main memory for a temporary period.
Types of Result Sets
Based on the capabilities of scrollability and sensitivity to changes, there are three
types of result sets available with the JDBC 2.0 core API. The following constants,
defined in the ResultSet interface, are used to specify these three types of result
sets:
1. TYPE_FORWARD_ONLY
The result set is non-scrollable; its cursor moves forward only, from top to
bottom.
2. TYPE_SCROLL_INSENSITIVE
The result set is scrollable: Its cursor can move forward or backward and can be
moved to a particular row or to a row whose position is relative to its current
position.
The result set generally does not show changes to the underlying database that
are made while it is open.
3. TYPE_SCROLL_SENSITIVE
The result set is scrollable; its cursor can move forward or backward and can be
moved to a particular row or to a row whose position is relative to its current
position.
The result set is sensitive to changes made while it is open. If the underlying
column values are modified, the new values are visible, thus providing a dynamic
view of the underlying data.
Concurrency Types
A result set may have different update capabilities. As with scrollability, making a
ResultSet object updatable increases overhead and should be done only when
necessary. The JDBC 2.0 core API offers two update capabilities, specified by the
following constants in the ResultSet interface:
1. CONCUR_READ_ONLY
Indicates a result set that cannot be updated programmatically
Offers the highest level of concurrency (allows the largest number of
simultaneous users). When a ResultSet object with read-only concurrency needs
to set a lock, it uses a read-only lock. This allow users to read data but not to
change it.
2. CONCUR_UPDATABLE
Indicates a result set that can be updated programmatically
Available to drivers that implement the JDBC 2.0 core API
Reduces the level on concurrency. Updatable results sets may use write-only locks
so that only one user at a time has access to a data item. This eliminates the
possibility that two or more users might change the same data, thus ensuring
database consistency. However, the price for this consistency is a reduced level of
concurrency.
Note: By default a ResultSet is Scrollable but not updatable. When we create a
Statement/PreparedStatement, by default they create a ResultSet with "type"--
TYPE_SCROLL_INSENSITIVE, "concurrency"--CONCUR_READ_ONLY
Advantage of using Scrollable ResultSet are we can move back and forth in the
ResultSet with the following methods:
public boolean next (); It returns true when rs contains next record otherwise
false.
public void beforeFirst (); It is used for making the ResultSet object to point to
just before the first record (it is by default)
public boolean isFirst (); It returns true when rs is pointing to first record
otherwise false.
public void first (); It is used to point the ResultSet object to first record.
public boolean isBeforeFirst (); It returns true when rs pointing to before first
record otherwise false.
public boolean previous (); It returns true when rs contains previous record
otherwise false.
public void afterLast (); It is used for making the ResultSet object to point to just
after the last record.
public boolean isLast (); It returns true when rs is pointing to last record
otherwise false.
public void last (); It is used to point the ResultSet object to last record.
public boolean isAfterLast (); It returns true when rs is pointing after last record
otherwise false.
public void absolute (int); It is used for moving the ResultSet object to a particular
record either in forward direction or in backward direction with respect to first
record and last record respectively. If int value is positive, rs move in forward
direction to that with respect to first record. If int value is negative, rs move in
backward direction to that with respect to last record.
public void relative (int); It is used for moving rs to that record either in forward
direction or in backward direction with respect to current record.
Example:
import java.sql.*;
public class ScrollResultSet {
public static void main(String[] args) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/onlinetutorialspoint", "root",
"123456");
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery("select * from student");
System.out.println("RECORDS IN THE TABLE...");
while (rs.next()) {
System.out.println(rs.getInt(1) + " -> " + rs.getString(2));
rs.first();
System.out.println("FIRST RECORD...");
System.out.println(rs.getInt(1) + " -> " + rs.getString(2));
rs.absolute(3);
System.out.println("THIRD RECORD...");
System.out.println(rs.getInt(1) + " -> " + rs.getString(2));
rs.last();
System.out.println("LAST RECORD...");
System.out.println(rs.getInt(1) + " -> " + rs.getString(2));
rs.previous();
rs.relative(-1);
System.out.println("LAST TO FIRST RECORD...");
System.out.println(rs.getInt(1) + " -> " + rs.getString(2));
con.close();
}
Row Set
Scrollable result set have a drawback, we need to keep the database connection
open during the entire user interaction.
A more powerful approach is using the row set where we don't need to keep the
database connection open.
RowSet interface extends the ResultSet interface.
javax.sql.rowset package provides the following interfaces that extend the
RowSet interface.
1. CachedRowSet -->contains all data from a result set. We can close the
connection and still use the row set. We can even modify the data in a cached
row set. However, the modifications are not immediately reflected in the
database. We need to reconnect to the database.
2. WebRowSet--> is a cached row set that can be saved to an XML file.
3. FilteredRowSet and JoinRowSet--> support operations that are equivalent to
SELECT and JOIN operations.
4. JdbcRowSet-->adds getters and setters from the RowSet interface, turning a
result set into a bean.
Creating CachedRowSet: (javax.sql.rowset -- import it)
String sql="select * from tbltest";
ResultSet rs=st.executeQuery(sql);
CachedRowSet crs=new com.sun.rowset.CachedRowSetImpl();
crs.populate(rs);
con.close();
Specifying page size:
if the query result is very large we may limit the number of rows:
crs.setPageSize(20);
Here we will get only 20 rows. To get next rows we need to call
crs.nextPage();
