RowSet in Java

A RowSet is an object that holds tabular data, like a ResultSet, but is easier to use and more flexible. The interface javax.sql.RowSet extends ResultSet, so everything you know about cursors and getters still applies. On top of that, a row set is always scrollable and updatable, and some kinds are disconnected: they copy the rows into memory and keep working after the database connection has been closed. In this lesson, you will learn the kinds of row set, use the most important one, CachedRowSet, and see where row sets fit today.

Kinds of RowSet

You create them with a factory: RowSetProvider.newFactory().createCachedRowSet().

ResultSet vs RowSet

Example: Working with Rows After Disconnecting

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
 
public class Rows {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:h2:mem:school;DB_CLOSE_DELAY=-1";
        CachedRowSet rows = RowSetProvider.newFactory().createCachedRowSet();
 
        try (Connection con = DriverManager.getConnection(url);
             Statement st = con.createStatement()) {
            st.execute("CREATE TABLE student ("
                + "id INT PRIMARY KEY, name VARCHAR(50), marks INT)");
            st.executeUpdate("INSERT INTO student VALUES "
                + "(1, 'Navika', 91), (2, 'Srihitha', 84), (3, 'Advitha', 78)");
            try (ResultSet rs = st.executeQuery(
                    "SELECT id, name, marks FROM student ORDER BY id")) {
                rows.populate(rs);
            }
        }
        // The connection is closed now, but the rows are still here.
        System.out.println("Rows held in memory: " + rows.size());
        while (rows.next()) {
            System.out.println(rows.getInt("id") + " " + rows.getString("name")
                + " " + rows.getInt("marks"));
        }
 
        // It scrolls both ways, like a scrollable ResultSet.
        rows.absolute(2);
        System.out.println("Row 2: " + rows.getString("name"));
        rows.previous();
        System.out.println("Before it: " + rows.getString("name"));
 
        // Change a row while disconnected, then send the change back.
        rows.absolute(3);
        rows.updateInt("marks", 88);
        rows.updateRow();
        rows.setTableName("student");
        try (Connection con = DriverManager.getConnection(url)) {
            con.setAutoCommit(false);
            rows.acceptChanges(con);
        }
        try (Connection con = DriverManager.getConnection(url);
             Statement st = con.createStatement();
             ResultSet rs = st.executeQuery(
                 "SELECT marks FROM student WHERE id = 3")) {
            rs.next();
            System.out.println("Advitha in the database: " + rs.getInt(1));
        }
        rows.close();
    }
}

Rows held in memory: 3
1 Navika 91
2 Srihitha 84
3 Advitha 78
Row 2: Srihitha
Before it: Navika
Advitha in the database: 88

Explanation:

1. rows.populate(rs) copies every row of the ResultSet into the CachedRowSet. The connection is then closed by try-with-resources.
2. The rows are still readable with the same next() and getter methods, because they live in memory.
3. Scrolling with absolute and previous works without asking for a scrollable type.
4. Updating while disconnected: updateInt and updateRow change the copy in memory only.
5. acceptChanges(con) sends the pending changes to the database through a new connection. The final query confirms that Advitha's marks in the table are now 88. The row set uses the table's primary key to find the right row.

A RowSet Can Run Its Own Query

A row set can also hold the connection details and the SQL itself, so it needs no separate Connection or Statement:

CachedRowSet rows = RowSetProvider.newFactory().createCachedRowSet();
rows.setUrl("jdbc:h2:mem:school");
rows.setCommand("SELECT id, name, marks FROM student WHERE marks >= ?");
rows.setInt(1, 80);
rows.execute();   // connects, runs the query, loads the rows, disconnects

Where RowSets Fit Today

Row sets were designed for passing data between the layers of an application, and for clients that are connected only now and then. In current applications, that job is usually done by plain objects: rows are mapped to records or classes in a DAO (see "CRUD Operations with JDBC") or by a framework such as JPA. You will meet RowSet mostly in older code, in desktop tools, and in exam and interview questions.

Common Mistakes

Loading a huge result into a CachedRowSet. Memory use grows with every row.

Expecting changes to reach the database immediately. They are local until acceptChanges.

Updating a table without a primary key. The row set can't identify which row to change.

Ignoring conflicts. If another user changed the same row meanwhile, acceptChanges throws SyncProviderException.

Practice

1. Easy: Load the students into a CachedRowSet, close the connection, and print them in reverse order.
2. Harder: Use rows.setCommand with a parameter to load only students above a mark supplied by the user, without creating a Connection yourself.

Key Takeaways