Using Prepared Statements

Up to this point, a java.sql.Statement class has been used to execute the SQL statements against the database. This is not very efficient if you will be executing the same SQL statements more than a couple of times because the SQL statement must be compiled each time it is sent to the database. The database does not understand the SQL string and is not able to execute it in the string format. It must turn the SQL string into something that it can understand and execute.

The java.sql.PreparedStatement can help with this because it will compile the SQL statement one time and then you can send the PreparedStatement to the database without the database having to compile it each time. This increases performance because the statement is, in effect, precompiled.

The SQL statements have also been used without parameters in the previous examples. You can also pass parameters to the SQL statements and then reuse the same statement and pass different parameters in to it. Although you can use the PreparedStatement on a SQL statement that does not take parameters, you generally should be passing parameters to it. This will allow for better performance and reuse.

Look at an example using a PreparedStatement. For this example, the PreparedStatement is supplied that takes two input parameters. One input parameter will be a CUSTID, which is the primary key of the customer table. The second is a REPID. The example will update the customer's rep id with the new value.

Remember that in the previous example in Listing 26.9,

an update SQL string was used that was returned by the Customer class to perform the update. The update string had to be built each time and executed. It also had to be compiled each time it was executed. If you wanted to update several customers, this would be very inefficient.

This example is pretty basic, but it shows the basics of using the PreparedStatement with parameters. Notice that question marks in the UPDAATE_SQL static String. These question marks are replaced by actual values during execution of the PreparedStatement. You should also notice that a PreparedStatment is given a SQL String when it is created. It uses this String to precompile the Statement that will be executed. This is why the performance is better; the SQL String has already been compiled into something native that can be executed. Only the parameters for the questions marks need to be passed in. Listing 26.11 shows the complete example.

Listing 26.11 Source Code for UpdateCustomerRepID.java

 import java.sql.*; public class UpdateCustomerRepID {   // Private instance variables for the connection and the prepared statement   private Connection connection = null;   private PreparedStatement stmt = null;   // The SQL to be used for the PreparedStatement. The question marks are   // for the input parameters that will be passed in   private static String UPDATE_SQL = "UPDATE CUSTOMER SET REPID = ? WHERE CUSTID = ?";   // Default Constructor   public UpdateCustomerRepID( Connection conn ) throws SQLException   {     super();     connection = conn;     // Create the instance of the PreparedStatement for this class     stmt = connection.prepareStatement( UpdateCustomerRepID.UPDATE_SQL );   }   // Public Accessor for the PreparedStatement   public PreparedStatement getStatement()   {     return stmt;   }   // Public Accessor for the Connection   public Connection getConnection()   {     return connection;   }   // Print out all the Customers to the console   public void printAllCustomers()   {     // Local variable for the Statement object     Statement stmt = null;     ResultSet rs = null;     try     {       // Create a Statement object from the Connection       stmt = getConnection().createStatement();       // Ask the Customer for its insert sql statement       String sql = "SELECT CUSTID, REPID FROM CUSTOMER";       rs = stmt.executeQuery( sql );       while( rs.next() )       {         String id = rs.getString( 1 );         String repId = rs.getString( 2 );         System.out.println( "CUSTID: " + id + " REPID: " + repId );       }     }     catch( SQLException ex )     {       ex.printStackTrace();     }   }   // This method performs the update through the PreparedStatement   public void updateCustomerRepId( String customerNumber,      int repId )    throws SQLException {     stmt.setInt( 1, repId );     stmt.setString( 2, customerNumber );     stmt.executeUpdate();   }   // The main method for this example   public static void main(String[] args)   {     try     {       // Use the previous DatabaseManager class to acquire a connection       Connection conn = DatabaseManager.getConnection();       // Create an instance of the example class       UpdateCustomerRepID example = new UpdateCustomerRepID( conn );       System.out.println( "Before running the update on the records." );       example.printAllCustomers();       Statement custStatement = conn.createStatement();       ResultSet rs = custStatement.executeQuery( Customer.getReadAllSQL() );       while( rs.next() )       {         // Get the customer number(id) from the record         String customerNumber = rs.getString( 1 );         // Set the phone number to all zero's         int repId = 1;         // Execute the PreparedStatement         example.updateCustomerRepId( customerNumber, repId );       }       System.out.println( "After running the update on the records." );       example.printAllCustomers();       // Always be sure to close the connection when you are finished       conn.close();     }     catch( SQLException ex )     {       ex.printStackTrace();     }   } } 

Here is the output from running the UpdateCustomerRepID example in Listing 26.12.

Listing 26.12 Output from UpdateCustomerRepID.java

 C:\jdk1.3se_book\classes>java UpdateCustomerRepID Before running the update on the records. CUSTID: 100 REPID: 7844 CUSTID: 101 REPID: 7521 CUSTID: 102 REPID: 7654 CUSTID: 103 REPID: 7521 CUSTID: 104 REPID: 7499 CUSTID: 105 REPID: 7844 CUSTID: 106 REPID: 7521 CUSTID: 107 REPID: 7499 CUSTID: 108 REPID: 7844 CUSTID: 109 REPID: 1 After running the update on the records. CUSTID: 100 REPID: 1 CUSTID: 101 REPID: 1 CUSTID: 102 REPID: 1 CUSTID: 103 REPID: 1 CUSTID: 104 REPID: 1 CUSTID: 105 REPID: 1 CUSTID: 106 REPID: 1 CUSTID: 107 REPID: 1 CUSTID: 108 REPID: 1 CUSTID: 109 REPID: 1 C:\jdk1.3se_book\classes 

As mentioned earlier, this example is simple. How often would you want to set all the values in a column to the same value? Hopefully, the point comes across, however, that you don't need to create a new Statement each time. You can use a PreparedStatement and pass parameters to it. This will increase performance because the statement is precompiled. This also potentially reduces the number of objects in memory.