• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Difference between Statement/Prepared statement?

 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
what is the difference between Statement and PreparedStatement?
 
Bartender
Posts: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Good topic titles are good keywords!
 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!!!
 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 544
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
reply
    Bookmark Topic Watch Topic
  • New Topic