java DAO update query failure

Question

I am trying to write DAO method to update value in postgres table "accounts" with just two columns: "id" string "balance" int

public Account setAccountBalance(String id, Integer balance) {
    Handle h = dbi.open();

    try{
        return h.createQuery("UPDATE accounts SET balance=" + balance.intValue() +
                            " WHERE id=\'" + id +"\';")
                .mapTo(Account.class)
                .first();
    } finally {
        h.close();
    }
}

But on execute I see the following exception: org.skife.jdbi.v2.exceptions.NoResultsException: Query did not have a result set, perhaps you meant update? [statement:"UPDATE accounts SET balance=20 WHERE id='1';", located:"UPDATE accounts SET balance=20 WHERE id='1';", rewritten:"UPDATE accounts SET balance=20 WHERE id='1';", arguments:{ positional:{}, named:{id:'1'}, finder:[]}]

Any idea if the problem is in query syntax, or use of DAO?

Answer 1

Looks like you are using JDBI. According to the documentation, SQL UPDATEs can be performed through Handle.execute(), as follows:

h.execute("UPDATE accounts SET balance=? WHERE id=?", balance.intValue(), id);

But the execute method doesn't return a result set and therefore cannot be used for creating an Account object. You would need to issue a separate query for doing that, maybe something like

return h.createQuery("SELECT id, balance FROM accounts WHERE id = :id")
            .bind("id", id)
            .mapTo(Account.class)
            .first();