RowSetFactory factory = RowSetProvider.newFactory(); CachedRowSet rowset = factory.createCachedRowSet();This creates a CachedRowSet object from the implementation class com.sun.rowset.CachedRowSetImpl. It’s equivalent to the following statement:
CachedRowSet rowset = new com.sun.rowset.CachedRowSetImpl();However, it’s recommended to create a CachedRowSet object from a RowSetFactory because the reference implementation may be changed in future.
- Populate data from an existing ResultSet object.
- Populate data by executing a SQL command.
Let’s see each way in details. Populate data to a CachedRowSet object from a ResultSet object:Given a ResultSet object which is created from a Statement, the following code populates data from the result set to the cached row set:ResultSet result = statement.executeQuery(sql); RowSetFactory factory = RowSetProvider.newFactory(); CachedRowSet rowset = factory.createCachedRowSet(); rowset.populate(result);Now you can close the connection and still be able to scroll through rows in the rowset. Populate data to a CachedRowSet object by executing SQL command:In this case, you need to set database connection properties and SQL statement for the CachedRowSet object, and then call the execute() method. For example:
String url = "jdbc:mysql://localhost:3306/college"; String username = "root"; String password = "password"; RowSetFactory factory = RowSetProvider.newFactory(); CachedRowSet rowset = factory.createCachedRowSet(); rowset.setUrl(url); rowset.setUsername(username); rowset.setPassword(password); rowset.setCommand(sql); rowset.execute();After the CachedRowSet object is populated , you can iterate over its rows by using ResultSet’s methods because CachedRowSet extends ResultSet. For example, the following code snippet iterates all rows in the row set and print details of each row:
rowset.populate(result); while (rowset.next()) { String name = rowset.getString("name"); String email = rowset.getString("email"); String major = rowset.getString("major"); System.out.printf("%s - %s - %s\n", name, email, major); }
rowset.setTableName("student");For example, the following code snippet updates the 5th row in the row set:
rowset.absolute(5); rowset.updateString("name", name); rowset.updateString("email", email); rowset.updateString("major", major); rowset.updateRow();The following code inserts a new row to the row set:
rowset.moveToInsertRow(); rowset.updateNull("student_id"); rowset.updateString("name", name); rowset.updateString("email", email); rowset.updateString("major", major); rowset.insertRow(); rowset.moveToCurrentRow();Note that you must call updateNull(column_name) for the primary key column of the table if that column’s values are auto-generated. Otherwise an exception throws.And the following code removes the current row in the row set:
rowset.deleteRow();
rowset.acceptChanges();If the CachedRowSet object is populated from a ResultSet object, pass the Connection object to the method:
rowset.acceptChanges(connection);Note that the acceptChanges() method throws SyncProviderException if it found conflicts when trying to synchronize with the database. So you must handle this exception. Also make sure to disable auto commit mode:
connection.setAutoCommit(false);
import java.sql.*; import javax.sql.rowset.*; import javax.sql.rowset.spi.*; import java.io.*; /** * This program demonstrates how to use CachedRowSet in JDBC. * * @author www.codejava.net */ public class CachedRowSetExample { static Console console = System.console(); static String answer; static boolean quit = false; public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/college"; String username = "root"; String password = "password"; try (Connection conn = DriverManager.getConnection(url, username, password)) { conn.setAutoCommit(false); String sql = "SELECT * FROM student"; Statement statement = conn.createStatement(); ResultSet result = statement.executeQuery(sql); RowSetFactory factory = RowSetProvider.newFactory(); CachedRowSet rowset = factory.createCachedRowSet(); rowset.setTableName("student"); rowset.populate(result); while (!quit) { if (!readStudent(rowset)) continue; updateStudent(rowset); deleteStudent(rowset); insertStudent(rowset); saveChanges(rowset, conn); askToQuit(); } } catch (SQLException ex) { System.out.println(ex.getMessage()); ex.printStackTrace(); } } static void readStudentInfo(String position, ResultSet result) throws SQLException { String name = result.getString("name"); String email = result.getString("email"); String major = result.getString("major"); String studentInfo = "%s: %s - %s - %s\n"; System.out.format(studentInfo, position, name, email, major); } static boolean readStudent(ResultSet result) throws SQLException { int row = Integer.parseInt(console.readLine("Enter student number: ")); if (result.absolute(row)) { readStudentInfo("Student at row " + row + ": ", result); return true; } else { System.out.println("There's no student at row " + row); return false; } } static void updateStudent(ResultSet result) throws SQLException { answer = console.readLine("Do you want to update this student (Y/N)?: "); if (answer.equalsIgnoreCase("Y")) { String name = console.readLine("\tUpdate name: "); String email = console.readLine("\tUpdate email: "); String major = console.readLine("\tUpdate major: "); if (!name.equals("")) result.updateString("name", name); if (!email.equals("")) result.updateString("email", email); if (!major.equals("")) result.updateString("major", major); result.updateRow(); System.out.println("The student has been updated."); } } static void deleteStudent(ResultSet result) throws SQLException { answer = console.readLine("Do you want to delete this student (Y/N)?: "); if (answer.equalsIgnoreCase("Y")) { result.deleteRow(); System.out.println("The student has been removed."); } } static void insertStudent(ResultSet result) throws SQLException { answer = console.readLine("Do you want to insert a new student (Y/N)?: "); if (answer.equalsIgnoreCase("Y")) { String name = console.readLine("\tEnter name: "); String email = console.readLine("\tEnter email: "); String major = console.readLine("\tEnter major: "); result.moveToInsertRow(); result.updateNull("student_id"); result.updateString("name", name); result.updateString("email", email); result.updateString("major", major); result.insertRow(); result.moveToCurrentRow(); System.out.println("The student has been added."); } } static void saveChanges(CachedRowSet rowset, Connection conn) { answer = console.readLine("Do you want to save changes (Y/N)?: "); if (answer.equalsIgnoreCase("Y")) { try { rowset.acceptChanges(conn); } catch (SyncProviderException ex) { System.out.println("Error commiting changes to the database: " + ex); } } } static void askToQuit() { answer = console.readLine("Do you want to quit (Y/N)?: "); quit = answer.equalsIgnoreCase("Y"); } }The following screenshot illustrates how to run and use the program: