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.

PreparedStatement pstmt = con.prepareStatement("INSERT INTO tablename (column) 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. [View full width] 1 import java.sql.*; 2 3 // Script 9.5 'AddInvoice2.java' 4 5 public class AddInvoice2 { 6 7 public static void main(String args[]) throws Exception { 8 9 // Initialize variables. 10 Connection con = null; 11 PreparedStatement stmt = null; 12 ResultSet rs = null; 13 int affected = 0; 14 int id = 0; 15 16 try { 17 18 // Connect to MySQL. 19 String url = "jdbc:mysql:///accounting"; 20 Class.forName("com.mysql.jdbc.Driver").newInstance(); 21 con = DriverManager.getConnection(url, "username", "password"); 22 23 // Prepare the query. 24 stmt = con.prepareStatement("INSERT INTO invoices (client_id, invoice_amount, invoice_description) VALUES (?, ?, ?)"); 25 26 // Assign values. 27 stmt.setInt(1, Integer.parseInt(args[0])); 28 stmt.setFloat(2, Float.parseFloat(args[1])); 29 stmt.setString(3, args[2]); 30 31 // Execute the query. 32 affected = stmt.executeUpdate(); 33 34 // Confirm that the insert worked. 35 if (affected == 1) { 36 rs = stmt.getGeneratedKeys(); 37 rs.next(); 38 id = rs.getInt(1); 39 System.out.println("Invoice number " + id + " has been created!"); 40 } else { 41 System.out.println("The invoice could not be added to the database!"); 42 } 43 } 44 45 // Catch exceptions. 46 catch (SQLException e) { 47 System.out.println("Problem: " + e.toString()); 48 } 49 50 // Clean up. 51 finally { 52 if (rs != null) { 53 try { 54 rs.close(); 55 } catch (SQLException e) { 56 // Do nothing with exception. 57 } 58 rs = null; 59 } 60 if (stmt != null) { 61 try { 62 stmt.close(); 63 } catch (SQLException e) { 64 // Do nothing with exception. 65 } 66 stmt = null; 67 } 68 if (con != null) { 69 try { 70 con.close(); 71 } catch (SQLException e) { 72 // Do nothing with exception. 73 } 74 } 75 } 76 77 } // End of main(). 78 79 } // End of class AddInvoice2.
3.	Change the declaration of the stmt variable (line 11) to PreparedStatement stmt = null; You can also name the variable pstmt if you want to be more precise.
4.	Remove the createStatement() line and replace it with [View full width] stmt = con.prepareStatement("INSERT INTO invoices (client_id, invoice_amount, invoice_description) VALUES (?, ?, ?)"); Another benefit of prepared statements, in my opinion, is that this syntax is a lot cleaner than that in the original script.
5.	Assign values to the placeholders. stmt.setInt(1, Integer.parseInt(args[0])); stmt.setFloat(2, Float.parseFloat(args[1])); stmt.setString(3, args[2]); 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.

Tips

• Prepared statements can be used with any type of query, not just INSERTs.

• The process demonstrated here is also referred to as bound variables.