• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Difference between Statement/Prepared statement?

 
harsh garg
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what is the difference between Statement and PreparedStatement?
 
Vijitha Kumara
Bartender
Posts: 3913
9
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2458
 
Avyaya Avi
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Apart from being pre-compiled the biggest difference between them is PreparedStatement allows you to use Parametric SQL.
e.g.


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


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
author & internet detective
Marshal
Posts: 34669
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic