CachedRowSet failed to insert row

I'm using CachedRowSet. But when I call the insertRow() method, there is a SQLException failed to insert row.

Here is my code:

static final String DATABASE_URL = "jdbc:mysql://localhost:3306/javapos";
static final String USERNAME = "root";
static final String PASSWORD = "sbc";

public static void main (String [] agr) throws SQLException
{
    CachedRowSetImpl rs = new CachedRowSetImpl();
    rs.setUrl(DATABASE_URL);
    rs.setUsername(USERNAME);
    rs.setPassword(PASSWORD);

    rs.setCommand("select * from uom order by itemid");
    rs.execute();

    while(rs.next()){
        System.out.println(rs.getString("itemid") + "  -  " + rs.getString("uom"));
    }

    rs.moveToInsertRow();
    rs.updateString(2,"Sample code");
    rs.insertRow();
    rs.moveToCurrentRow();

    rs.acceptChanges();
}

Solution

When you call insertRow(), the Reference Implementation of CachedRowSet performs a check if all required columns have been populated and otherwise it throws an exception (source from Grepcode CachedRowSet.insertRow(), line numbers don't exactly match):

if (onInsertRow == false ||
        insertRow.isCompleteRow(RowSetMD) == false) {
    throw new SQLException(resBundle.handleGetObject("cachedrowsetimpl.failedins").toString());
}

The check is performed in InsertRow.isCompleteRow(RowSetMetaData):

public boolean isCompleteRow(RowSetMetaData RowSetMD) throws SQLException {
    for (int i = 0; i < cols; i++) {
        if (colsInserted.get(i) == false &&
                RowSetMD.isNullable(i + 1) == ResultSetMetaData.columnNoNulls) {
            return false;
        }
    }
    return true;
}

In other words, when inserting a row you must provide a value for all columns that are not nullable (this includes the primary key). There seem to be two ways to work around this:

Setting a (random) value. This does require that your primary key is always generated (even if a value is provided).
Explicitly setting the column to null using updateNull. Using setNull doesn't work: it provides the same error, and using setObject(idx, null) results in a NullPointerException

When using your code with these changes I get an SQLException when calling acceptChanges as the implementation doesn't disable autoCommit (it seems to have been commented out), but it does explicitly call commit (which is invalid when in autoCommit). This doesn't seem to be easy to solve, except maybe explicitly providing a connection on execute, or creating your own implementation.

I think these kind of issues actually demonstrate how little the RowSet implementations are actually used (otherwise they would already have been flushed out long ago).

Note however that if this were the actual code you needed and don't need the disconnected characteristics of the CachedRowSet, then you could simply use an updatable result set.