Java 17 Recipes

 How It Works
While standard JDBC statements get the job done, the harsh reality is that they can 
sometimes be insecure and cumbersome to work with. For instance, bad things can 
occur if a dynamic SQL statement queries a database. A user-accepted string is assigned 
to a variable and concatenated with the intended SQL string. In most ordinary cases, 
the user-accepted string would be concatenated, and the SQL string would query the 
database as expected. However, an attacker could decide to place malicious code inside 
the string (a.k.a. SQL injection), which would then be inadvertently sent to the database 
using a standard Statement object.
Chapter 12 Working With Databases

---

452
The use of PreparedStatements prevents such malicious strings from being 
concatenated into a SQL string and passed to the DBMS because they use a different 
approach. PreparedStatements use substitution variables rather than concatenation to 
make SQL strings dynamic. They are also precompiled, which means that a valid SQL 
string is formed prior to the SQL being sent to the DBMS. Moreover, PreparedStatements 
can help your application perform better because if the same SQL must be run more 
than once, it must be compiled only once. After that, the substitution variables are 
interchangeable, but the overall SQL can be executed by PreparedStatement very 
quickly.
Let’s look at how a PreparedStatement object 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 is used in place of that question mark when the SQL is executed. The next step 
for using a PreparedStatement object is to declare a variable of type PreparedStatement. 
This can be seen with the following line of code.
PreparedStatement pstmt = null;
PreparedStatement implements AutoCloseable, and therefore it can be utilized 
within the context of a try-with-resources block. Once PreparedStatement has 
been declared, it can be put to use. However, the use of PreparedStatement might 
not cause an exception to be thrown. Therefore, if try-with-resources is not used, 
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 necessary to send the SQL string 
to the database and retrieve results.
Chapter 12 Working With Databases

---

453
try(PreparedStatement pstmt = conn.prepareStatement(sql);) {
pstmt.setString(1, recipeNumber);
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString(2) + ": " + rs.getString(3) +
" - " + rs.getString(4));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
First, you can see that the Connection object instantiates a PreparedStatement 
object. The SQL string is passed to the PreparedStatement object’s constructor 
on creation. Since PreparedStatement is instantiated within the try-with- 
resources construct, it is automatically closed when it is no longer in use. Next, the 
PreparedStatement object sets values for any substitution variables that have been 
placed into the SQL string. As you can see, the PreparedStatement setString() 
method is used in the example to set the substitution variable at position 1 equal to 
the contents of the recipeNumber variable. The positioning of the substitution variable 
is associated with the placement of the question mark (?) within the SQL string. The 
first question mark within the string is assigned to the first position, the second one is 
assigned to the second position, and so forth. If more than one substitution variable 
were assigned, there would be more than one call against the PreparedStatement 
object, assigning each of the variables until each one has been accounted for. 
PreparedStatement objects can accept substitution variables of many different data 
types. For instance, if an int value were assigned to a substitution variable, a call to the 
setInt(position, variable) method would be in order. See the online documentation 
or your IDE’s code completion for a complete set of methods that can be used for 
assigning substitution variables using PreparedStatement objects.
Once all the variables have been assigned, the SQL string can be executed. The 
PreparedStatement object contains an executeQuery() method that executes a SQL 
string that represents a query. The executeQuery() method returns a ResultSet object, 
which contains the results fetched from the database for the particular SQL query. 
Next, ResultSet can be traversed to obtain the values retrieved from the database. 
Again, positional assignments retrieve the results by calling the ResultSet object’s 
corresponding getter methods and passing the position of the column value that you 
Chapter 12 Working With Databases

---

454
want to obtain. The position is determined by the order in which the column names 
appear within the SQL string. In the example, the first position corresponds to the 
RECIPE_NUMBER column, the second corresponds to the RECIPE_NAME column, and so 
forth. If the recipeNumber string variable was equal to 12-1, the results of executing the 
query in the example would look like the following.
12-1: Installing MySQL - downloading and installation of a MySQL Database
Of course, if the substitution variable is not set correctly or if there is an issue 
with the SQL string, an exception is thrown. This would cause the code contained 
within the catch block to be executed. You should also be sure to clean up after using 
PreparedStatements by closing the statement when you are finished using it. If you’re 
not using a try-with-resources construct, it is a good practice to put all the cleanup 
code within a finally block to be sure that the PreparedStatement object is closed 
properly, even if an exception is thrown. In the example, the finally block looks like the 
following.
finally {
if (pstmt != null){
try {
pstmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
You can see that the PreparedStatement object that was instantiated, pstmt, is 
checked to see whether it is NULL. If not, it is closed by calling the close() method.
Working through the code in the solution to this recipe, you can see that similar 
code processes database INSERT, UPDATE, and DELETE statements. The only difference 
in those cases is that the PreparedStatement executeUpdate() method is called rather 
than the executeQuery() method. The executeUpdate() method returns an int value 
representing the number of rows affected by the SQL statement.
The use of PreparedStatement objects is preferred over JDBC Statement objects. 
This is because they are more secure and perform better. They can also make your code 
easier to follow and maintain.
Chapter 12 Working With Databases

---

455