Working with Databases - Java 8 Recipes - page 454

ables are interchangeable, but the overall SQL can be executed by the Pre-
paredStatement very quickly.
Let's take a look at how a PreparedStatement works in practice. If you look
at the first example in the solution to this recipe, you can see that the database table
RECIPES is being queried, passing a RECIPE_NUMBER and retrieving the results for
the matching record. The SQL string looks like the following:
String sql = "SELECT ID, RECIPE_NUMBER, RECIPE_NAME,
DESCRIPTION " +
"FROM RECIPES " +
"WHERE RECIPE_NUM = ?";
Everything looks standard with the SQL text except for the question mark ( ? ) at the
end of the string. Placing a question mark in a string of SQL signifies that a substitute
variable will be used in place of that question mark when the SQL is executed. The
next step for using a PreparedStatement is to declare a variable of type Pre-
paredStatement . This can be seen with the following line of code:
PreparedStatement pstmt = null;
A PreparedStatement implements AutoCloseable , and therefore it can be
utilized within the context of a try-with-resources block. Once a Pre-
paredStatement has been declared, it can be put to use. However, use of a Pre-
paredStatement might not cause an exception to be thrown. Therefore, in the
event that try-with-resources is not used, a PreparedStatement should
occur within a try-catch block so that any exceptions can be handled gracefully.
For instance, exceptions can occur if the database connection is unavailable for some
reason or if the SQL string is invalid. Rather than crashing an application due to such
issues, it is best to handle the exceptions wisely within a catch block. The following
try-catch block includes the code that is necessary to send the SQL string to the
database and retrieve results:
try(PreparedStatement pstmt
= conn.prepareStatement(sql);) {
pstmt.setString(1, recipeNumber);
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString(2) + ": "