CODEWITHSUNDEEP
javajdbcsql-updateh2cachedrowset
Developer Marius Žilėnas
Developer Marius Žilėnas

Reputation: 6119

CachedRowSet update a record in H2

I have a H2 DB with a table 

CREATE TABLE income_expense
(
  amount VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  ondate VARCHAR(255) NOT NULL
);

and some random data like INSERT INTO income_expense VALUES ('10','Something','2015-04-15');

then I connect to this DB with JDBC and try to do an UPDATE through CachedRowSet:

public void doUpdate()
{
    try
    { 
        Class.forName("org.h2.Driver");
        setConnection(
                DriverManager.getConnection("jdbc:h2:~/thisdb", "sa", ""));

        CachedRowSet crs2 = new CachedRowSetImpl();
        crs2.setType(CachedRowSet.TYPE_SCROLL_INSENSITIVE);
        crs2.setConcurrency(CachedRowSet.CONCUR_UPDATABLE);
        crs2.setCommand(
                "SELECT amount, name, ondate FROM income_expense");
        crs2.execute(getConnection());
        crs2.absolute(1);
        crs2.updateString("amount", "22");
        crs2.updateString("name"  , "33");
        crs2.updateString("ondate", "44");
        crs2.updateRow();
        crs2.acceptChanges();
    }
    catch (ClassNotFoundException | SQLException e)
    {
        System.out.println("Error occured." + e);
    }
}

this update fails with a message javax.sql.rowset.spi.SyncProviderException: 1 conflicts while synchronizing.

What am I doing wrong to update a record?

Upvotes: 0

Views: 728

Answers (1)

Developer Marius Žilėnas
Developer Marius Žilėnas

Reputation: 6119

Well... CachedRowSet UPDATE worked only after I added a PRIMARY KEY to the table:

ALTER TABLE income_expenses ADD COLUMN id INT NOT NULL AUTO_INCREMENT;
ALTER TABLE income_expenses ADD CONSTRAINT PRIMARY KEY (id);

and specified numbers of columns which form a key for uniquely identifying a row and included that column in a SELECT:

//First column from a command will be the key
crs2.setKeyColumns(new int[]{1});
crs2.setCommand("SELECT id, amount, name, ondate FROM income_expense");

Upvotes: 1

Related Questions