This post originated from an RSS feed registered with Java Buzz by David Rolfe. Original Post: Not all advice is good advice... Feed Title: OrindaBlog - Orinda Software's Corporate Blog Feed URL: http://www.jroller.com/rss/orindasoft Feed Description: We're in the business of creating products that make it easier to work with Oracle, Java and JDBC. We want this blog to be something more than a stream of product-related propaganda - our intention is two-thirds of the content will be of general relevance to people who work with Oracle, Java and PL/SQL. Entries to this blog will be made as and when we have information that we think our readers would be interested in. Latest Java Buzz Posts Latest Java Buzz Posts by David Rolfe Latest Posts From OrindaBlog - Orinda Software's Corporate Blog

The author spends the chapter conducting elaborate benchmarks which in his view demonstrate that there's nothing special about PreparedStatement and that you should really be using Statement most of the time. His reasoning is based on the premise that the additional set up costs of Prepareing a statement and sending all the bind variables etc cause applications to run more slowly than if you had simply said something like:

Statement stmt = conn.createStatement();
stmt.executeUpdate(
"values ( " + Integer.toString( i ) + ", '125678901234567890', " + 
"'1234567890', " +
"USER, to_date('" + sdf.format(new java.util.Date(System.currentTimeMillis())) 
+ "', 'YYYYMMDDHH24MISS'))");

instead of:

  PreparedStatement stmt = conn.prepareStatement(
   "insert into oehr.testxxxperf ( id, code, descr, insert_user, insert_date ) " +
   "values ( ?, ?, ?, ?, ? )");
  startTime = System.currentTimeMillis();
  int i=1;
  stmt.setInt(1,i);
  stmt.setString(2,"123456789012345678901234567890");         
  stmt.setString(3,"123456789019012345678901234567890");
  stmt.setString(4,"ZXVI01");
  stmt.setDate(5,new java.sql.Date(System.currentTimeMillis()));
  stmt.executeUpdate();

The point made is that unless you plan on issuing hundreds of statements the first method is faster because it has fewer network round trips and therefore less elapsed time. All of which is true - provided there is only 1 copy of the client software being used. In practice what will happen is that using Statement in preference to PreparedStatement will cause the database server to devote significant resources to compiling execution plans for each and every version of the insert statement being called. Indeed, not using PreparedStatement and bind variables is listed as the second most common mistake in Oracle's ' Top Ten Mistakes Found in Oracle Systems'.

There's also a less obvious but equally serious problem with cobbling together your SQL statements instead of using bind variables and PreparedStatement - sooner or later someone with an apostrophe in their name will show up and cause a syntax error:

Int empno = 42;
String empname = "O'Reilly";
String myStatement = "INSERT INTO EMP (empno, ename) VALUES (" + empno + ",'" + empname + "');";

But wait... it gets worse! If you're working on a public facing web system and some bright spark realizes your hacking together SQL statements you could have something like this happen:

Int empno = 42;
String ename = "'Smith', sal = 99999";
String myStatement = "UPDATE EMP set ename = " + ename+ " WHERE empno = " + empno');";

which means that your Statement would be:

UPDATE EMP set ename = 'Smith', sal = 99999 WHERE empno = 42;