Difference between Statement and PreparedStatement

Question

The Prepared Statement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement.
The Prepared Statement may be parametrized

Most relational databases handles a JDBC / SQL query in four steps:
1.Parse the incoming SQL query
2. Compile the SQL query
3. Plan/optimize the data acquisition path
4. 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.

Now my question is that - "Is any other advantage of using Prepared Statement?"

Thanks In Advance.

Answer 1

PreparedStatement is a very good defense (but not foolproof) in preventing SQL injection attacks. Binding parameter values is a good way to guarding against "little Bobby Tables" making an unwanted visit.

Answer 2

nothing much to add,

1 - if you want to execute a query in a loop (more than 1 time), prepared statement can be faster, because of optimization that you mentioned.

2 - parameterized query is a good way to avoid SQL Injection, that is only available in PreparedStatement.

Answer 3

Advantages of a PreparedStatement:

1. Precompilation and DB-side caching leads to faster execution (already mentioned by you).
2. Prevention of SQL injection attacks (already mentioned by duffymo).
3. Eases setting of non-standard Java objects in a SQL string, e.g. Date, Time, Timestamp, BigDecimal, InputStream (Blob) and Reader (Clob). You could even refactor it all to using PreparedStatement#setObject() inside a loop on a List<Object> or Object[]. On most of those types you can't "just" do a toString() in a simple Statement.

Answer 4

Can't do CLOBs in a Statement.

And: (OraclePreparedStatement) ps

Answer 5

• It's easier to read
• You can easily make the query string a constant