Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Updating a ResultSet is not updating the database

800560Sep 24 2009 — edited Oct 15 2014
I'm trying to teach myself SQL and JDBC using the Derby database. Please see the code below, which toggles a value each time it runs. When I execute a query and update a result set row, the update does not get written to the database unless I use commit() on the connection or close() on the result set, statement, or connection. If I do not use one of those, the while loops show the change, but the database does not get updated. According to every tutorial and document I've seen, the updateRow() should update the database. My example code is based on the code from the Sun JDBC tutorial and it says,

At this point, the price in uprs for French Roast Decaf will be 10.99, but the price in the table COFFEES in the database will still be 9.99. To make the update take effect in the database and not just the result set, we must call the ResultSet method updateRow.

So, am I missing something in my understanding or is there something about Derby that is different or what?
public class Test {
    public static void main(String[] args) throws Exception {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        String url = "jdbc:derby:Databases/CoffeyBreak";
        Connection conn = DriverManager.getConnection(url);
        System.out.println(conn.getAutoCommit()); // Shos true
        conn.setAutoCommit(true);

        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet uprs = stmt.executeQuery("SELECT SUP_ID,COF_NAME, PRICE FROM COFFEES");


        while(uprs.next()) {
            System.out.println(uprs.getObject(1)+"\t"+uprs.getObject(2)+"\t"+uprs.getObject(3));
        }

        uprs.first();
        String str = uprs.getString(2).equals("Columbian") ? "Folgers":"Columbian";
        uprs.updateString("COF_NAME", str);
        uprs.updateRow();
        conn.commit(); //Without a conn.commit() or uprs.close() or stmt.close or conn.close(), the database is not updated

        System.out.println();
        uprs.beforeFirst();
        while(uprs.next()) {
            System.out.println(uprs.getObject(1)+"\t"+uprs.getObject(2)+"\t"+uprs.getObject(3));
        }
    }
} 
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 26 2009
Added on Sep 24 2009
10 comments
179 views