my dog learned polymorphism*
The moose likes JDBC and the fly likes Difference between Statement/Prepared statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Difference between Statement/Prepared statement?" Watch "Difference between Statement/Prepared statement?" New topic
Author

Difference between Statement/Prepared statement?

harsh garg
Ranch Hand

Joined: Jul 13, 2008
Posts: 33
what is the difference between Statement and PreparedStatement?
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3825

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.


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Good topic titles are good keywords!
Avyaya Avi
Greenhorn

Joined: Feb 04, 2009
Posts: 11
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

Joined: Dec 05, 2008
Posts: 30
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

Joined: Feb 10, 2009
Posts: 1
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.


----------------------------------------------
Anurag Kapur
http://www.linkedin.com/in/anuragkapur
-----------------------------------------------
long meng
Ranch Hand

Joined: Oct 10, 2008
Posts: 58
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?


SCJP 5.0 98%<br />SCWCD 5.0 in progress . . .
amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 511
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


Regards,
Amit
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30324
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Tino Hertlein
Greenhorn

Joined: Mar 15, 2009
Posts: 1
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.


Cheers,
Tino
 
Consider Paul's rocket mass heater.
 
subject: Difference between Statement/Prepared statement?