Prepared SQL Statements

When you send a SQL statement to the database, the database software reads the SQL statement and verifies that it is correct. This is known as parsing the SQL statement. The database software then builds a plan, known as the execution plan , to actually run the statement. So far, all the SQL statements sent to the database through JDBC have required a new execution plan to be built. This is because each SQL statement sent to the database has been different.

Suppose you had a Java application that was performing the same INSERT statement repeatedly ”an example might be loading many new products to our example store, a process that would require adding lots of rows to the products table using INSERT statements. Let s consider an example that would actually do this. Assume that a class named Product has been defined as follows :

 class Product {  int productId;  int productTypeId;  String name;  String description;  double price; } 

The following code creates an array of five Product objects. Because the products table already contains rows with product_id values from 1 to 12, the productId attributes for the new Product objects start at 13:

 Product [] productArray = new Product[5]; for (int counter = 0; counter < productArray.length; counter ++) {  productArray[counter] = new Product();  productArray[counter].productId = counter + 13;  productArray[counter].productTypeId = 1;  productArray[counter].name = "Test product";  productArray[counter].description = "Test product";  productArray[counter].price = 19.95; } // end of for loop 

To add the rows to the products table, we ll use a for loop that contains a JDBC statement to perform an INSERT statement, and the column values will come from productArray :

 Statement myStatement = myConnection.createStatement(); for (int counter = 0; counter < productArray.length; counter ++) {  myStatement.executeUpdate("INSERT INTO products " +   "(product_id, product_type_id, name, description, price) VALUES (" +  productArray[counter]. productId + ", " +   productArray[counter]. productTypeId + ", '" +  productArray[counter].name + "', '" +   productArray[counter].description + "', " +    productArray[counter].price + ")"); } // end of for loop 

Each iteration through the loop results in an INSERT statement being sent to the database. Because the string representing each INSERT statement contains different values, the actual INSERT sent to the database is slightly different each time. This means that the database creates a different execution plan for every INSERT statement ”very inefficient.

You ll be glad to know that JDBC provides a better way to run such SQL statements. Instead of using a JDBC Statement object to run your SQL statements, you can use a JDBC PreparedStatement object. A PreparedStatement object allows you to perform the same SQL statement but supply different values for actual execution of that statement. This is more efficient because the same execution plan is used by the database when the SQL statement is run. The following example creates a PreparedStatement object containing an INSERT statement similar to the one used in the previous loop:

 PreparedStatement myPrepStatement = myConnection.prepareStatement("INSERT INTO products " +  "(product_id, product_type_id, name, description, price) VALUES (" +  "?, ?, ?, ?, ?"  ")"); 

There are two things you should notice about this example:

• The prepareStatement() method is used to specify the SQL statement.

• Question mark characters (?) are used to indicate the positions where you will later provide variables to be used when the SQL statement is actually run.

The positions of the question marks are important: they are referenced according to their position, with the first question mark being referenced using number 1, the second as number 2, and so forth.

The process of supplying Java variables to a prepared statement is known as binding the variables to the statement, and the variables themselves are known as bind variables . To actually supply variables to the prepared SQL statement, you must use set methods. These methods are similar to the get methods that I ve already discussed in the section on result sets, except that set methods are used to supply variable values, rather than read them.

For example, to bind a Java int variable named intVar to the product_id column in the PreparedStatement object previously created, you use setInt(1, intVar) . The first parameter indicates the numeric position of the question mark (?) in the string previously specified in the prepareStatement() method call. For this example, the value 1 corresponds to the first question mark, which supplies a value to the product_id column in the INSERT statement. Similarly, to bind a Java String variable named stringVar to the name column, you use setString(3, stringVar) , because the third question mark corresponds to the name column. Other methods you can call in a PreparedStatement object include setFloat() and setDouble() for setting single-precision floating point  and double-precision floating point numbers .

The following example features a loop that shows the use of set methods to bind the attributes of the Product objects in productArray to the PreparedStatement object. Notice that the execute() method is used to actually run the SQL statement:

 for (int counter = 0; counter < productArray.length; counter ++) {  myPrepStatement.setInt(1, productArray[counter]. productId);  myPrepStatement.setInt(2, productArray[counter]. productTypeId);  myPrepStatement.setString(3, productArray[counter].name);  myPrepStatement.setString(4, productArray[counter].description);  myPrepStatement.setDouble(5, productArray[counter].price);  myPrepStatement.execute(); } // end of for loop 

Once this code has completed, the products table will contain five new rows.

To set a database column to NULL using a PreparedStatement object, you may use the setNull() method. For example, the following statement sets the description column to NULL :

 myPrepStatement.setNull(4, java.sql.Types.VARCHAR); 

The first parameter in the call to setNull() is the numeric position of the column you want to set to NULL . The second parameter is an int that corresponds to the database type of the column that is to be set to NULL . This second parameter should be specified using one of the constants defined in the java.sql.Types class. For a VARCHAR2 column (the description column is defined as a VARCHAR2 ), you should use java.sql.Types.VARCHAR .