OCP Question 23, Explanation

Given the structure of the EMPLOYEE table:

Employee (id INTEGER, name VARCHAR)

Given:

public class Test {
    static Connection newConn = null;
    public static Connection getDBConnection() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, login, password)) {
            newConn = conn;
        }
        return newConn;
   }
    public static void main(String[] args) throws SQLException {
        getDBConnection();
        Statement stat = newConn.createStatement();
        stat.executeUpdate("INSERT INTO employee VALUES (2, 'Joe Random')");
    }
}

Assume that:

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

What is the result?

A. The program executes successfully and the EMPLOYEE table is updated with one record.
B. The program executes successfully and the EMPLOYEE table is NOT updated with any record.
C. A SQLException is thrown as runtime.
D. A NullPointerException is thrown as runtime.

 

The correct answer is C.

B&S809 says on page 507 that

In all of the other chapters of this book, you need to write code and try lots of examples. This chapter is different. It’s still nice to try out the examples, but you can probably get the JDBC questions correct on the exam from just reading this chapter and mastering the review questions.

Personally, I don’t see how it is possible to learn any skill at all without getting your hands dirty, so this means we’re going to set up our own DB in order to prepare for the exam. As you remember from reading B&S809, JDK comes loaded with all necessary classes for a Derby database, which we’ll be using most of the time.

Here I’m explaining how to set up a Derby DB locally and a MySQL DB on a remote server to play with them by using NetBeans and command line, too. Complete this preparatory stage first, then come back for the solution to the current Problem 23…

Alright, let’s assume that right now you have at least a Derby DB up and running. The above-mentioned tutorial showed how to re-create the DB that was used by B&S809 to illustrate their examples but the current Problem has the EMPLOYEE table rather than ANIMAL and SPECIES (in SQL names of the tables are case-insensitive; here we are simply following the notation adopted by the current Problem). No worries, let’s simply add the lacking table to the existing DB; we’ll be needing it to check our conclusion after arriving at an answer. Here’s the code that places the EMPLOYEE table into zoo (and then adds a record to it to confirm that everything works as advertised):

class Test{
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:derby:zoo";
        String q = "SELECT * FROM EMPLOYEE";
        try(
//           Connection conn = DriverManager.getConnection(url);
           Connection conn = DriverManager.getConnection(url, "myUserName", "myPassword");
           Statement stat = conn.createStatement();
            )
        {
           stat.executeUpdate("CREATE TABLE employee("
           + "id INTEGER PRIMARY KEY, "
           + "name VARCHAR(255))");

           stat.executeUpdate("INSERT INTO employee VALUES (1, 'John Smith')");
           ResultSet rs = stat.executeQuery(q);
//
           while(rs.next()) System.out.println(
                                                rs.getString(1) + " "
                                              + rs.getString(2));     //  1 John Smith
           rs.close();
        }
    }
}

As expected, a record for someone named John Smith whose id is 1 has been successfully added to our new EMPLOYEE table. Time to take a closer look at Problem 23.

The code in it is a bit sloppy; for example, the vars URL, login and password haven’t been declared but we won’t be paying attention to such minor things anymore: there are too many of them on the exam. At least, the intentions of this Problem are quite clear.

So this is what we have here: although newConn gets a non-null value in the getDBConnection() method, the try-with-resources closes the resource right after the closing paren (i.e., before return newConn;), so createStatement() throws an SQLNonTransientConnectionException.

Illustration:

public static Connection getDBConnection() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:derby:zoo")) {
        newConn = conn;
        System.out.println(newConn);  // classname + (XID = 417), (SESSIONID = 1), (DATABASE = zoo), (DRDAID = null)
    }
    System.out.println(newConn.createStatement());  // java.sql.SQLNonTransientConnectionException
    return newConn;
}

On a side note: Problem 23 makes a half-hearted attempt to follow the Singleton-with-lazy-instantiation approach but the class’s default constructor is public, which is a violation of a properly implemented Singleton Design Pattern. Not to mention the lack of synchronization… And yes, all this stuff is indeed important for the exam; I’m just taking an opportunity to remind you of it, that’s all.

Leave Comment

Your email address will not be published. Required fields are marked *