Difference between Statement and PreparedStatement

user_136966 0
Viewed 282258

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 this:

"Is there any other advantage of using Prepared Statement?"

jdbc java
15 Answers

Statement is static and prepared statement is dynamic.

Statement is suitable for DDL and prepared statment for DML.

Statement is slower while prepared statement is faster.

more differences (archived)

user_30413540

I followed all the answers of this question to change a working legacy code using - Statement ( but having SQL Injections ) to a solution using PreparedStatement with a much slower code because of poor understanding of semantics around Statement.addBatch(String sql) & PreparedStatement.addBatch().

So I am listing my scenario here so others don't make same mistake.

My scenario was 

Statement statement = connection.createStatement();

for (Object object : objectList) {
    //Create a query which would be different for each object 
    // Add this query to statement for batch using - statement.addBatch(query);
}
statement.executeBatch();

So in above code , I had thousands of different queries, all added to same statement and this code worked faster because statements not being cached was good & this code executed rarely in the app.

Now to fix SQL Injections, I changed this code to , 

List<PreparedStatement> pStatements = new ArrayList<>();    
for (Object object : objectList) {
    //Create a query which would be different for each object 
    PreparedStatement pStatement =connection.prepareStatement(query);
    // This query can't be added to batch because its a different query so I used list. 
    //Set parameter to pStatement using object 
    pStatements.add(pStatement);
}// Object loop
// In place of statement.executeBatch(); , I had to loop around the list & execute each update separately          
for (PreparedStatement ps : pStatements) {
    ps.executeUpdate();
}

So you see, I started creating thousands of PreparedStatement objects & then eventually not able to utilize batching because my scenario demanded that - there are thousands of UPDATE or INSERT queries & all of these queries happen to be different.

Fixing SQL injection was mandatory at no cost of performance degradation and I don't think that it is possible with PreparedStatement in this scenario.

Also, when you use inbuilt batching facility, you have to worry about closing only one Statement but with this List approach, you need to close statement before reuse , Reusing a PreparedStatement

user_38507300
Related