Working with RowSet

6.1 Introduction to RowSet

As you already know, the JDBC standard is almost 20 years old and a little outdated. New types and new classes are slowly being added to it, but this can not be done beautifully everywhere. And one of those places is ResultSet .

The database can be made more efficient, but the ResultSet interface is a poor fit. In addition, we do not explicitly create its objects anywhere, they are returned to us by the executeQuery().

The creators of JDBC did not think long and made a mechanism that is completely parallel to everything that was before. And it became known as RowSet .

Here are its main benefits:

• RowSet extends the ResultSet interface, so its functions are more powerful than those of ResultSet.
• RowSet navigates more flexibly through table data and can scroll back and forth.
• RowSet maintains cached data that can be used even after the connection is closed.
• RowSet supports new connection method, you can connect to database without connection. It also supports reading the XML data source.
• RowSet supports data filter.
• RowSet also supports table join operations.

Rowset types:

• CachedRowSet
• FilteredRowSet
• JdbcRowSet
• JoinRowSet
• WebRowSet

6.2 Creating a RowSet object

There are three different ways to get a work object.

First, it can be filled with data from a ResultSet obtained in the classical way.

For example, we can cache ResultSet data using CachedRowSet :

Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("SELECT * FROM user");
 
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet crs = factory.createCachedRowSet();
crs.populate(results);		// Use ResultSet to populate

Second, you can create a completely self-contained RowSet object by creating your own database connection for it:

JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
rowSet.setUrl("jdbc:mysql://localhost:3306/test");
rowSet.setUsername("root");
rowSet.setPassword("secret");
 
rowSet.setCommand("SELECT * FROM user");
rowSet.execute();

And thirdly, you can connect the RowSet to an already existing connection:

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
JdbcRowSet rowSet = new JdbcRowSetImpl(con);

rowSet.setCommand("SELECT * FROM user");
rowSet.execute();

6.3 Examples of working with RowSet

Example one: caching .

Let's write a code where we use CachedRowSet to cache all the data and read it from an already closed connection:

Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("SELECT * FROM user");

RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet crs = factory.createCachedRowSet();
crs.populate(results);	// Use ResultSet to populate

connection.close();		// Close the connection

// Cache data is still available
while(crs.next()) {
  	System.out.println(crs.getString(1)+"\t"+ crs.getString(2)+"\t"+ crs.getString(3));
}

Example two: changing rows via RowSet :

// Connect to the database
 CachedRowSet crs = rsf.createCachedRowSet();
 crs.setUrl("jdbc:mysql://localhost/test");
 crs.setUsername("root");
 crs.setPassword("root");
 crs.setCommand("SELECT * FROM user");
 crs.execute();

// This type of operation can only change standalone RowSet
// First, move the pointer to an empty (new) string, the current position is remembered
 crs.moveToInsertRow();
 crs.updateString(1, Random.nextInt());
 crs.updateString(2, "Clone" + System.currentTimeMillis());
 crs.updateString(3, "Female");
 crs.insertRow();  // Add the current (new) line to the rest of the lines
 crs.moveToCurrentRow(); // Return a pointer to the line where it was before insertion

 crs.beforeFirst();
 while(crs.next()) {
 	System.out.println(crs.getString(1) + "," + crs.getString(2) + "," + crs.getString(3));
}

// And now we can upload all our changes to the database
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/dbtest", "root", "root");
con.setAutoCommit(false); // Needed for synchronization
crs.acceptChanges(con);// Synchronize data to database

If you are interested in how it works, you can read the topic in the official documentation. My task at the moment is simply to tell what it is.