Java Enterprise Best Practices

Use PreparedStatement

JDBC provides three kinds of statement classes: Statement, PreparedStatement, and CallableStatement. All too often, however, discussions of which kind of statement to use focus purely on performance. That’s not to say that the choice of statement class doesn’t impact performance. As a general rule, CallableStatement instances based on database-stored procedures provide the best performance, with PreparedStatement instances close behind. Finally, Statement instances generally perform significantly worse than the other kinds of statements. Focusing purely on performance, however, disguises two important facts:

• The difference between CallableStatement and PreparedStatement is generally negligible.

• There are nontrivial situations in which a Statement gives you optimal performance.

The primary difference in performance among the different statement types concerns how the SQL parsing occurs. With Statement-based calls, the driver sends the SQL to the database, which parses it every time you execute the statement. Calls through a PreparedStatement (as the name implies) are “prepared” before they are executed. In other words, the driver sends the SQL to the database for parsing when the statement is created but before it is executed. By the time you call execute( ), the statement has been preparsed by the database. And if you’re truly lucky, the same SQL has already been executed, and no parsing even needs to occur. Finally, a CallableStatement is “precompiled” ...