17.4 Using Prepared Statements

If you are going to execute similar SQL statements multiple times, using parameterized (or "prepared") statements can be more efficient than executing a raw query each time. The idea is to create a parameterized statement in a standard form that is sent to the database for compilation before actually being used. You use a question mark to indicate the places where a value will be substituted into the statement. Each time you use the prepared statement, you simply replace the marked parameters, using a set Xxx call corresponding to the entry you want to set (using 1-based indexing) and the type of the parameter (e.g., setInt , setString ). You then use executeQuery (if you want a ResultSet back) or execute / executeUpdate to modify table data, as with normal statements.

For instance, in Section 18.5, we create a music table summarizing the price and availability of concerto recordings for various classical composers. Suppose, for an upcoming sale, you want to change the price of all the recordings in the music table. You might do something like the following.

 
 Connection connection =   DriverManager.getConnection(url, username, password);  String template =   "UPDATE music SET price = ? WHERE id = ?";   PreparedStatement statement =   connection.prepareStatement(template);  float[] newPrices = getNewPrices(); int[] recordingIDs = getIDs(); for(int i=0; i<recordingIDs.length; i++) {  statement.setFloat(1, newPrices[i]);  // Price   statement.setInt(2, recordingIDs[i]); // ID  statement.execute(); } 

The performance advantages of prepared statements can vary significantly, depending on how well the server supports precompiled queries and how efficiently the driver handles raw queries. For example, Listing 17.10 presents a class that sends 100 different queries to a database, using prepared statements, then repeats the same 100 queries, using regular statements. On one hand, with a PC and fast LAN connection (100 Mbps) to an Oracle9i database, prepared statements took only about 62 percent of the time required by raw queries, averaging 0.61 seconds for the 100 queries as compared with an average of 0.99 seconds for the regular statements (average of 5 runs). On the other hand, with MySQL (Connector/J 3.0) the prepared statement times were nearly identical to the raw queries with a fast LAN connection, with only about an 8 percent reduction in query time. To get performance numbers for your setup, download DriverUtilities.java from http://www.coreservlets.com/, add information about your drivers to it, then run the PreparedStatements program yourself. To create the music table, see Section 18.5.

Be cautious though: a prepared statement does not always execute faster than an ordinary SQL statement. The performance improvement can depend on the particular SQL command you are executing. For a more detailed analysis of the performance for prepared statements in Oracle, see http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html.

However, performance is not the only advantage of a prepared statement. Security is another advantage. We recommend that you always use a prepared statement or stored procedure (see Section 17.5) to update database values when accepting input from a user through an HTML form. This approach is strongly recommended over the approach of building an SQL statement by concatenating strings from the user input values. Otherwise, a clever attacker could submit form values that look like portions of SQL statements, and once those were executed, the attacker could inappropriately access or modify the database. This security risk is often referred to as an SQL Injection Attack. In addition to removing the risk of a such an attack, a prepared statement will properly handle embedded quotes in strings and handle noncharacter data (e.g., sending a serialized object to a database).

Core Approach

graphics/bwopenglobe_icon.gif

To avoid an SQL Injection Attack when accepting data from an HTML form, use a prepared statement or stored procedure to update the database.


Listing 17.10 PreparedStatements.java
 package coreservlets; import java.sql.*; import coreservlets.beans.*; /** An example to test the timing differences resulting  *  from repeated raw queries vs. repeated calls to  *  prepared statements. These results will vary dramatically  *  among database servers and drivers. With our setup  *  and drivers, Oracle9i prepared statements took only 62% of  *  the time that raw queries required, whereas MySQL  *  prepared statements took nearly the same time as  *  raw queries, with only an 8% improvement.  */ public class PreparedStatements {   public static void main(String[] args) {     if (args.length < 5) {       printUsage();       return;     }     String vendor = args[4];     // Use DriverUtilities2.loadDrivers() to load     // the drivers from an XML file.     DriverUtilities.loadDrivers();     if (!DriverUtilities.isValidVendor(vendor)) {       printUsage();       return;     }     String driver = DriverUtilities.getDriver(vendor);     String host = args[0];     String dbName = args[1];     String url =       DriverUtilities.makeURL(host, dbName, vendor);     String username = args[2];     String password = args[3];     // Use "print" only to confirm it works properly,     // not when getting timing results.     boolean print = false;     if ((args.length > 5) && (args[5].equals("print"))) {       print = true;     }     Connection connection =       ConnectionInfoBean.getConnection(driver, url,                                        username, password);     if (connection != null) {       doPreparedStatements(connection, print);       doRawQueries(connection, print);     }     try {       connection.close();     } catch(SQLException sqle) {       System.err.println("Problem closing connection: " + sqle);     }   }   private static void doPreparedStatements(Connection conn,                                            boolean print) {     try {  String queryFormat =   "SELECT id FROM music WHERE price < ?";   PreparedStatement statement =   conn.prepareStatement(queryFormat);  long startTime = System.currentTimeMillis();       for(int i=0; i<100; i++) {  statement.setFloat(1, i/4);   ResultSet results = statement.executeQuery();  if (print) {           showResults(results);         }       }       long stopTime = System.currentTimeMillis();       double elapsedTime = (stopTime - startTime)/1000.0;       System.out.println("Executing prepared statement " +                          "100 times took " +                          elapsedTime + " seconds.");     } catch(SQLException sqle) {       System.err.println("Error executing statement: " + sqle);     }   }   public static void doRawQueries(Connection conn,                                   boolean print) {     try {       String queryFormat =         "SELECT id FROM music WHERE price < ";       Statement statement = conn.createStatement();       long startTime = System.currentTimeMillis();       for(int i=0; i<100; i++) {         ResultSet results =           statement.executeQuery(queryFormat + i/4);         if (print) {           showResults(results);         }       }       long stopTime = System.currentTimeMillis();       double elapsedTime = (stopTime - startTime)/1000.0;       System.out.println("Executing raw query " +                          "100 times took " +                          elapsedTime + " seconds.");     } catch(SQLException sqle) {       System.err.println("Error executing query: " + sqle);     }   }   private static void showResults(ResultSet results)       throws SQLException {     while(results.next()) {       System.out.print(results.getString(1) + " ");     }     System.out.println();   }   private static void printUsage() {     System.out.println("Usage: PreparedStatements host " +                        "dbName username password " +                        "vendor [print].");   } } 

The preceding example illustrates how to create a prepared statement and set parameters for the statement in a command-line program. For Web development, you may want to submit prepared statements to the database from a JSP page. If so, the JSP Standard Tag Library (JSTLsee Volume 2 of this book) provides an sql:query action to define a prepared statement for submission to the database and an sql:param action to specify parameter values for the prepared statement.



Core Servlets and JavaServer Pages (Vol. 1.Core Technologies)
Core Servlets and Javaserver Pages: Core Technologies, Vol. 1 (2nd Edition)
ISBN: 0130092290
EAN: 2147483647
Year: 2002
Pages: 194

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net