Using Prepared Statements

The last topic I'll discuss with respect to Java is the prepared statement. I'll run through the syntax and then explain the usefulness. To begin, create an object of type PreparedStatement. This object is fed the query to be run, using placeholders for its values.

The question mark represents the data that will be added to the query. Note that no quotation marks go around the placeholder, even though it may represent string data (which otherwise has to be quoted in queries). Once you've prepared the query, you can then associate data with that placeholder:

pstmt.setString(1, "The actual string.");

This has the effect of replacing the first placeholder in the query with the text The actual string. Along with setString(), you will also use setInt(), setFloat(), and so on. Essentially these are just the companions to the getXXX() functions listed in Table 9.1.

Finally, you can run the query:

pstmt.executeUpdate();

If you want to run the query again using new values, just repeat these last two steps. You only need to prepare the statement once.

Prepared statements offer two benefits. First, they are often a faster method of running queries, particularly in applications where the same query is run multiple times, using different values for each. The second benefit is that you do not need to worry about escaping problematic characters like apostrophes. They'll automatically be handled by the process.

To demonstrate this, I'll rewrite AddInvoice.java.

To use prepared statements:

1.

Open AddInvoice.java (Script 9.4) in your text editor or IDE (if it is not already).

2.

Change the class name to AddInvoice2 (Script 9.5).

Script 9.5. Prepared statements, used in the INSERT query here, can improve the performance and security of database interactions.

Following the instructions already outlined, the setXXX() functions are used to assign specific values to the different placeholders. Each function matches the type of data expected by the database. The first argument in each is the placeholder number, starting at 1. The second argument is the actual value to be used. These correspond to command-line arguments used when running the program (see Figure 9.10).

Because all of the values are coming to the application as strings, the first two must be converted to their proper type. The Integer.parseInt() and Float.parseFloat() methods will accomplish this.

6.

Execute the query.

affected = stmt.executeUpdate();

Replace the original stmt.executeUpdate() line with this one.

7.

Save the file as AddInvoice2.java and compile.

8.

Run AddInvoice2 (Figure 9.12).

When running the program, add the three requisite arguments (for the client's ID, invoice amount, and invoice description):

java AddInvoice2 8 45.00 'Invoice Description'

Figure 9.12. The application functions the same as it had before (Figure 9.11) as far as the end user is concerned.

9.

Run AddInvoice2 using apostrophes in the description (Figure 9.13).

When running the program, add the three requisite arguments (for the client's ID, invoice amount, and invoice description):

java AddInvoice2 18 1002.48 "Larry's Invoice Description"

Figure 9.13. Apostrophes in user-submitted data will no longer cause problems, thanks to the use of prepared statements.