OCP Question 25, Explanation

Given the records from the Player table:

----------------
| id  |  name  |
----------------
| 11  |  Ted   |
----------------
| 12  |  John  |
----------------
| 13  |  Dick  |
----------------

and given the code fragment:

try {
    Connection conn = DriverManager.getConnection(URL, login, password);
    Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_UPDATABLE);
    stat.execute("SELECT * FROM Player");
    ResultSet rs = st.getResultSet();
    while (rs.next()) {
        if (rs.getInt(1) == 12) {
            rs.updateString(2, "Matt");
        }
    }
    rs.absolute(2);
    System.out.println(rs.getInt(1) + " " + rs.getString(2));
} catch (SQLException ex) {
    System.out.println("Exception is raised");
}

Assume that:

  • The required database driver is configured in the classpath.
  • The appropriate database accessible with the URL, login, and password exists.

What is the result?

A. The Player table is updated with the row: 112 Matt and the program prints: 112 John
B. The Player table is updated with the row: 112 John and the program prints: 112 Matt
C. The Player table is not updated and the program prints: 112 John
D. The program prints Exception is raised

 

The correct answer is C.

Click here to see how to set up a DB for your practice if you haven’t done so yet. Now to the Problem at hand.

The answer to it is written almost in the open on page 520 of B&S809:

Most of the time, you will use INSERT, UPDATE, or DELETE SQL statements to change the database rather than a ResultSet.

Which – after looking at the code, of course – automatically means that the correct option is either C or D. Now, what could be the reasons for an SQLException? There ain’t any! All LOCs are clean, rs.absolute() wants to access the second row, which is perfectly available to it because the table contains three records… Let’s verify our conclusion:

public class Test {
    public static void main(String[] args) throws SQLException {
        String URL = "jdbc:derby:zoo";
        try {
        //    Connection conn = DriverManager.getConnection(URL);
            Connection conn = DriverManager.getConnection(URL, "myUserName", "myPassword");
            Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                  ResultSet.CONCUR_UPDATABLE);

        //****** Creating PLAYER table **************************
            stat.executeUpdate("CREATE TABLE player("
            + "id INTEGER PRIMARY KEY, "
            + "name VARCHAR(255))");

        //****** Populating PLAYER table ************************
            stat.executeUpdate("INSERT INTO player VALUES (11, 'Ted')");
            stat.executeUpdate("INSERT INTO player VALUES (12, 'John')");
            stat.executeUpdate("INSERT INTO player VALUES (13, 'Dick')");

            stat.execute("SELECT * FROM Player");
            ResultSet rs = stat.getResultSet();
            while (rs.next()) {
                if (rs.getInt(1) == 12) {
                    rs.updateString(2, "Matt");
                }
            }
            rs.absolute(2);
            System.out.println(rs.getInt(1) + " " + rs.getString(2));        // 12 John
        } catch (SQLException ex) {
            System.out.println("Exception is raised" + ex.getMessage());
        }
    }
}

And below is how we could achieve the goal of updating the 2nd record in the Player table by doing it though ResultSet rather than by executing queries directly on the DB:

public class Test {
    public static void main(String[] args) throws SQLException {
        String URL = "jdbc:derby:zoo";
        try {
//           Connection conn = DriverManager.getConnection(URL);
            Connection conn = DriverManager.getConnection(URL, "myUserName", "myPassword");
            Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                                  ResultSet.CONCUR_UPDATABLE);

            stat.execute("SELECT * FROM Player");
            ResultSet rs = stat.getResultSet();
            System.out.println("Concurrency mode: " +
                               rs.getConcurrency());    // 1008, i.e. CONCUR_UPDATABLE
            System.out.println("Sensitivity mode: " +
                               rs.getType());           // 1004, i.e. TYPE_SCROLL_INSENSITIVE

            while (rs.next()) {
                if (rs.getInt(1) == 12) {
                    System.out.println("Before updating ResultSet: " + rs.getString(2));
                    rs.updateString(2, "Matt");

//                   rs.updateRow();

                    System.out.println("After updating ResultSet: " + rs.getString(2));
                }
            }

            rs.absolute(2);
            System.out.println("2nd record contains: " +
                               rs.getInt(1) + " " + rs.getString(2));        // 12 John

//           conn.commit();                            // needed to commit all changes to disk

        } catch (SQLException ex) {
            System.out.println("Exception is raised" + ex.getMessage());
        }
    }
}

The code prints:

Concurrency mode: 1008
Sensitivity mode: 1004
Before updating ResultSet: John
After updating ResultSet: Matt
2nd record contains: 12 John

 

The value 1008 is one of the constants defined in the java.sql.ResultSet interface, and means that the result set is updateable. The value 1004 means that the ResultSet object “…is scrollable but generally not sensitive to changes to the data that underlies the ResultSet“.

Now, Derby doesn’t support TYPE_SCROLL_SENSITIVE (which is 1005); the ResultSet’s type gets downgraded to 1004 (ref.to https://db.apache.org/derby/docs/10.5/devguide/cdevconcepts22641.html), so to see ‛sensitivity’ in action we’d need to switch to a MySQL DB. This. however, doesn’t affect our conclusion because, after all, the Problem talks about updating a table, not a result set.

Uncomment the rs.updateRow(); LOC, and the program will output this:


Before updating ResultSet: John
After updating ResultSet: Matt
2nd record contains: 12 Matt

 

Okay, this time the Player table was updated. Now run the program again without modifying it in any way; you’ll get this:


Before updating ResultSet: John    <- which means that the updated values weren’t saved
After updating ResultSet: Matt
2nd record contains: 12 Matt

 

Uncomment the conn.commit(); LOC, and you’ll finally get what you’re after (run the program twice to see changes):


Before updating ResultSet: Matt
After updating ResultSet: Matt
2nd record contains: 12 Matt

Leave Comment

Your email address will not be published.