Should we use insertRow() with acceptChanges()?

Question

Here is a sample code in java:

    try {
        /* create connection */
        Connection conn = DriverManager.getConnection(url, username, password);
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        /* create a CachedRowSet */
        CachedRowSet cachedResult = new com.sun.rowset.CachedRowSetImpl();

        /* set connection information */
        cachedResult.setUrl(url);
        cachedResult.setUsername(username);
        cachedResult.setPassword(password);

        ResultSet result = stmt.executeQuery("SELECT * FROM tbl");

        /* populate CachedRowSet */ 
        cachedResult.populate(result);

        /* close connection */
        result.close();
        stmt.close();
        conn.close();

        /* now we edit CachedRowSet */
        while (cachedResult.next()) {
            if (cachedResult.getInt("id") == 12) {
                cachedResult.moveToInsertRow();

                /* use some updateXXX() functions */

                cachedResult.insertRow();
                cachedResult.moveToCurrentRow();
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
}

Now my question is this: 1. Should I use insertRow()? or I should use acceptChanges() instead? or maybe both? 2. where should I put acceptChanges() in this code?

Answer 1

You call acceptChanges() when you are ready to propagate the changes to the underlying data source. However, if you are doing a number of updates/inserts (for multiple rows) then you should call acceptChanges() after all updateRow() and insertRow() are done. The reason being when you invoke acceptChanges() you establish an actual connection to the database which can often be expensive. So calling it each time after each insertRow/updateRow for multiple rows is not efficient.

In your code I would put acceptChanges() after the while block ends. The reason is what I mentioned above - making the database connection only once after all updates have been done to cacheResult in the while block.