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));
}
}
}