Difference between Statement/Prepared statement?

[harsh garg]

what is the difference between Statement and PreparedStatement?

[Vijitha Kumara]

Almost everyone prefer PreparedStatement over Statement most of the time. Prepared Statement is given an SQL statement when it is created so the SQL statement is sent to the DBMS where it is pre-compiled. So next time you execute the same prepared statement DBMS shouldn't need to compile it but run it directly.

[Bauke Scholtz]

Good topic titles are good keywords!

[Avyaya Avi]

When you use a Statement, the query gets compiled everytime the statement is executed.

When you use a PreparedStatement, the query is compiled once and the compiled query will be reused no matter how many number of times you execute the PrepStmt.

All in all, it means using the PreparedStatement reduces the overhead for the compiler so that the queries run a bit mroe faster than they are supposed to be when a Statement is used.

Hope this helps!!!

[Kumaresh Vidhyasagar]

Eventhough PreparedStatement has this advantage of avoiding compilation of query in DB, if you close the connection once you are done with executing the query then both Statement and PreparedStatement are same.

I think you understand what I mean to say!

[Anurag Kapur]

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 PreparedStatement pre-executes steps (1) - (3) in the execution process above. Thus, when creating a PreparedStatement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time.

[long meng]

Kumaresh Vidhyasagar wrote:Eventhough PreparedStatement has this advantage of avoiding compilation of query in DB, if you close the connection once you are done with executing the query then both Statement and PreparedStatement are same.

1.if i call preparedStatement.close(); will the Statement and PreparedStatement are same?
2.if i use a connection pool which has 10 connections, a application use connection1 for creating a preparedStatement, another
application user connection2 for creating a same preparedStatement ,will the 2nd application compile the sql ? or will it use the 1st prepared one?

[amit punekar]

Hi,
Apart from being pre-compiled the biggest difference between them is PreparedStatement allows you to use Parametric SQL.
e.g.

SELECT * FROM EMP WHERE EMP_NO=?  

This is far better than SQL formed using concatenated String.
e.g.

"SELECT * FROM EMP WHERE EMP_NO=" + request.getParameter("empnumber");    

Using concatenated string opens a kind of security hole meaning someone can send a string that might be destructive SQL or something instead of "empnumber" in above example.

Instead of this if you use Parametric SQL you have to use setXXX() method to set the parameter which is pretty safe than substituting the String variables in custom built SQL.

Use of request.getParameter() is just to illustrate the example.

Regards,
Amit

[Jeanne Boyarsky]

Long,
The caching is tied to the connection. This means the answer to question #1 is no and the answer to #2 is it will compile the sql again rather than reusing the one on the other connection.

[Tino Hertlein]

Hi harsh garg,

a couple of days ago I made a blog post about the proper use of Prepared Statements.

There are also some examples included, that might help you.