What are advantages of prepared statements over normal statements
Most relational databases handles a JDBC / SQL query in four steps:
- Parse the incoming SQL query
- Compile the SQL query
- Plan/optimize the data acquisition path
- Execute the optimized query / acquire and return data
A Statement will always proceed through the four steps above for each SQL query sent to the database. A Prepared Statement pre-executes steps (1) – (3) in the execution process above. Thus, when creating a Prepared Statement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time.
- Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in bacthes
- Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the
setXxx()methods to set the values
preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)"); preparedStatement.setString(1, person.getName()); preparedStatement.setString(2, person.getEmail()); preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime())); preparedStatement.setBinaryStream(4, person.getPhoto()); preparedStatement.executeUpdate();
and thus don’t inline the values in the SQL string by string-concatenating.
preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'"); preparedStatement.executeUpdate();
- There is another advantage to using prepared statements, and that is with handling String and Date/Time data. Some databases use single quotes as text delimiters, others use double quotes, and some recognize either. However if you want to insert character data containing single- and/or double-quotes, this can be tricky using a regular Statement. And if you move from one database to another this can force you to modify your SQL. The same can be said about inserting dates and times: each database has a different format that you have to follow.