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:
- 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.
Now my question is this:
"Is there any other advantage of using Prepared Statement?"
Best Answer
Advantages of a
PreparedStatement
:Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.
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
PreparedStatement
setXxx()
methods to set the valuesand thus don't inline the values in the SQL string by string-concatenating.
Eases setting of non-standard Java objects in a SQL string, e.g.
Date
,Time
,Timestamp
,BigDecimal
,InputStream
(Blob
) andReader
(Clob
). On most of those types you can't "just" do atoString()
as you would do in a simpleStatement
. You could even refactor it all to usingPreparedStatement#setObject()
inside a loop as demonstrated in the utility method below:Which can be used as below: