Quiz yourself: Use PreparedStatement to perform database CRUD operations (intermediate)

August 4, 2020 | Download a PDF of this article
More quiz questions available here

If you have worked on our quiz questions in the past, you know none of them is easy. They model the difficult questions from certification examinations. The “intermediate” and “advanced” designations refer to the exams rather than to the questions, although in almost all cases, “advanced” questions will be harder. We write questions for the certification exams, and we intend that the same rules apply: Take words at their face value and trust that the questions are not intended to deceive you but to straightforwardly test your knowledge of the ins and outs of the language.

The objective here is to test your knowledge of database create, read, update, and delete (CRUD) operations.

Assume there is an empty APP.BOOK table with the following definition:

ID: INTEGER
TITLE: VARCHAR(40)
EDITION: INTEGER

Here is code that uses this table:

PreparedStatement psInsert = conn.prepareStatement("INSERT INTO APP.BOOK (ID, TITLE, EDITION) VALUES (?, ?, ?)");
psInsert.setInt(1, 1);
psInsert.setString(2, "Java for Beginners");
psInsert.setInt(3, 1);
psInsert.executeUpdate();

psInsert.setInt(1, 2);
psInsert.setInt(3, 2);
psInsert.executeUpdate(); // line n1

PreparedStatement psUpdate = conn.prepareStatement("UPDATE APP.BOOK SET TITLE = ? WHERE ID = ?");
psUpdate.setString(1, "JDBC for Beginners");
psUpdate.setInt(2, 1);
psUpdate.executeUpdate();

psUpdate.setInt(2, 2);
psUpdate.executeUpdate(); // line n2

Which of the following will be the table state after the code runs? Choose one.

Answer. For this Java SE 11 quiz about database CRUD functionality, a first question might be about the configuration of the database and the connection to it. How do you know the code that sets everything up is correct? How do you know that the runtime code has the necessary privileges to access the tables? For the purposes of the certification exams, it has been a long-standing general guideline that you should assume all the aspects of the system that you don’t see are correct and would support the code that you do see.

This assumption of correctness used to be called out explicitly in earlier versions of the exams (see the “Assume the following” part of Java SE 8’s Review Exam Topics section), but those notes were not explicitly included in the Java 11 version of the exam.

Let’s consider the behavior of the code presented here.

All of the code is correct from a basic syntax perspective. The SQL in the first line is correct, and all the PreparedStatement parameters are correctly initialized prior to invoking psInsert.execute() on the fifth line. There is no reason shown to suppose that the default auto-commit setting has been changed, so you can conclude that at least something is written into the table. Nothing in the rest of the code could possibly delete the written item (though you will see the row is actually modified subsequently). Because the table won’t be empty, option A is incorrect.

It’s clear that the code calls executeUpdate twice on each of the PreparedStatement objects. This is entirely valid and, in fact, it is the preferred way to use these objects. The reuse saves the RDBMS from having to compile the same SQL text multiple times. As the API documentation states, “This object can then be used to efficiently execute this statement multiple times.”

You will also notice that before the second call to each PreparedStatement, the Java code does not specify new values for all the parameters. This, too, is entirely correct and allows increased efficiency when multiple updates are being made and some columns have values that have not changed. It is a requirement that all parameters must be assigned values before the first update, but this is completed correctly in both cases. If any have not been specified, the update call will throw an exception of this form:

java.sql.SQLException: At least one parameter to the current statement is uninitialized.

In this example, all the parameter values are initialized before the first use of either PreparedStatement, so that won’t be a concern.

Also, the parameter values are reused if they are not explicitly reassigned. From this you know that line n1 inserts a second book title: “Java for Beginners.” The documentation on the clearParameters method of PreparedStatement states, “In general, parameter values remain in force for repeated use of a statement. Setting a parameter value automatically clears its previous value.”

At this point, the table contents will be the following:

1	Java for Beginners	1
2	Java for Beginners	2

The second PreparedStatement is used in a manner broadly similar to the first; both parameters are given a value, and one of them is modified between the first and second calls to executeUpdate. In this case, the book title is the same in both updates, so the titles of both book records are updated to the name “JDBC for Beginners.” After line n2 executes, the table will contain this:

1	JDBC for Beginners	1
2	JDBC for Beginners	2

From this, you can conclude that option E is correct and the remaining options (B, C, and D) are incorrect.

The correct answer is option E.