Prepared Statements

Finally, let's look at prepared statements and how we can use them in our applications. A prepared statement is basically a way to create an SQL statement that contains placeholders for data. So, a statement can be used like a method to which you pass parameters. This is a good method to use when you will be using the same statement many times with different data, and it is typically quicker to execute as well. Let's look at a sample console application that implements a prepared statement. Note that we will be using the highscore database for this application. Here is the complete source code listing:

Code Listing 16-9: Using prepared statements

import java.sql.*;     public class PreparedStatementExample {     public static void main(String[] args)     {         try         {             Class.forName("org.gjt.mm.mysql.Driver");         }         catch(ClassNotFoundException e)         {             System.out.println(e);         }                                 try         {             Connection conn;                               System.out.println("Attempting to connect...\n");                         conn = DriverManager.getConnection("jdbc:mysql:                 //localhost/highscore?user=root&password=");                          System.out.println("Connected\n");                            // Create our prepared statement             String ourStatement = "INSERT INTO scoredata VALUES                  (?, ?)";                         PreparedStatement addNewScore = conn.prepareStatement                 (ourStatement);                         // Now insert three rows of data using the prepared             // statement...             addNewScore.setString(1, "George");                  // set the name placeholder to 'George'             addNewScore.setInt(2, 1000);                         // set the score placeholder to 1000             addNewScore.executeUpdate();                         addNewScore.setString(1, "Sandra");                  // set the name placeholder to 'Sandra'             addNewScore.setInt(2, 500);                         // set the score placeholder to 500             addNewScore.executeUpdate();                         addNewScore.setString(1, "Billy");                  // set the name placeholder to 'Billy'             addNewScore.setInt(2, 200);                         // set the score placeholder to 200             addNewScore.executeUpdate();                               System.out.println("\nAttempting to disconnect...\n");                         conn.close();                         System.out.println("Disconnected\n");           }         catch(SQLException e)         {             System.out.println(e);         }        } }

When this console application is executed, it will insert three rows of data into the scoredata table in our highscore database. The following figure is a screen shot of the data contained in our scoredata table after the application has been executed:

Figure 16-10: The prepared statement has inserted three rows into our database.

Now look at what we have changed in the code to allow us to use prepared statements. First, we created the prepared statement by creating a string with the following line of code:

String ourStatement = "INSERT INTO scoredata VALUES (?, ?)";

Notice how we use the ? to define placeholders for unknown values. Note also that you can have as many placeholders as you require for your statement. So in this statement, we have defined the actual values that we are inserting into the scoredata table as unknowns.

Next we need to actually create a PreparedStatement object using the Connection object. This is accomplished with the following line of code:

PreparedStatement addNewScore = conn.prepareStatement(ourStatement);

So now that we have a PreparedStatement object, we can specify the unknowns and then execute it to insert the data into the database. We specify the unknown values using the following two lines of code in our example program:

    addNewScore.setString(1, "George");          // set the name placeholder to 'George'     addNewScore.setInt(2, 1000);                 // set the score placeholder to 1000

Notice that the first parameter of the setString/setInt method specified which placeholder (?) that you are referring to, and the first placeholder is 1 and not 0. In this code segment, the first line will set the first placeholder to the value "George," and the second line will set the second placeholder to the integer value 1000.

Finally, once we have set our placeholder values, we can then execute the prepared statement using the following line of code:

addNewScore.executeUpdate();

This process can then be repeated as many times as you wish, using the same PreparedStatement.