aspose file tools*
The moose likes JDBC and the fly likes Prepared Statement Doubt!! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statement Doubt!!" Watch "Prepared Statement Doubt!!" New topic
Author

Prepared Statement Doubt!!

Harry Singh
Ranch Hand

Joined: May 02, 2001
Posts: 124
Hi java Gurus,
I've just started JDBC and its quite interesting in some ways like transactions and also the statements part too.. well after reading a prepared statement i came through these two phrases quite often, they are 'PREPARSED' and 'PRECOMPILED'. what exactly do we mean by these words. does they mean that SQl is embedded into the statement object so thats why we say it preparsed and precompiled and how this is helpful, if they are embedded in statement object as the statements basically communicate with DB, so how does this helps?
Thanks in advance.
Regards,
Harry
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

http://www.coderanch.com/t/298008/JDBC/java/PreparedStatement-overhead
http://www.coderanch.com/t/299305/JDBC/java/Prepared-Statements


Groovy
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Every SQL statement, before executed, needs to be "compiled" by the database server - that is, the server needs to decide how to exactly execute the statement.
As this is a costly operation, most databases cache those compiled statements. That is, when you want to execute that statement for a second time, it doesn't need to be compiled again, therefore leading to better performance.
Now imagine the following two statements:
select * from table where id=1
select * from table where id=2
For the database, those are two different statements, so both get compiled independently from each other.
Now, if you use a PreparedStatement, it should look like
select * from table where id=?
In this statement, you can replace the value for the question mark, without the database needing to recompile the whole statement every time.
Did that help?


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Ko Ko Naing
Ranch Hand

Joined: Jun 08, 2002
Posts: 3178
Also in the java.sql.PreparedStatement API, it says
An object that represents a precompiled SQL statement.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.


Co-author of SCMAD Exam Guide, Author of JMADPlus
SCJP1.2, CCNA, SCWCD1.4, SCBCD1.3, SCMAD1.0, SCJA1.0, SCJP6.0
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
You guys might want to check out tips #3 and #5 from this article.
In short, PreparedStatements are faster for repeated queries but not necessarily for rare queries. Also, this excerpt from O'Reilly's Java Programming with Oracle JDBC presents some interesting performance benchmarks.


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Harry Singh
Ranch Hand

Joined: May 02, 2001
Posts: 124
Thanks guys!! yeps it helped a lot..
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
I'd like to add two things.
First, I don't buy tip #3 linked to above as it stands. It reads Use the Statement object for time-critical [...] SQL statements. Your SQL statement may be time-critical because it (a) either takes a long time to run, (b) or it doesn't take long but is executed many times over. In case (a) the difference between a PreparedStatement and a Statement is likely to be insignificant compared to the execution time of the statement itself. In case (b) a PreparedStatement is generally faster.
Second, there are other reasons to use PreparedStatement apart from execution speed. The naive approach to include String parameters in a Statement is often a source of application bugs or, worse, a source of grave security holes. Likewise, Date parameters can land you in database date format hell unless your environments are exactly the same (or unless you use SQL escapes -- anyone?). Parameters in a PreparedStatement exhibit none of these problems.
Bottom line is, in my experience the PreparedStatement represents a solid default choice in most cases, and a Statement is best used only if you have a specific reason to do so. To advise the other way around opens too many pitfalls for the unwary.
- Peter
[ October 14, 2003: Message edited by: Peter den Haan ]
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Peter has a point here. The only case where I tend to use regular Statements is when I need to construct the SQL statement dynamically in a way that would be too complicate to do with a PreparedStatement and setXXX() methods.
Vinod John
Ranch Hand

Joined: Jun 23, 2003
Posts: 162
few more Q's,
When I call close() on prepared statment object will the compiled version of the sql be discarded or the database overrule's this call and still retains it. I remotely remember reading that it depends on the driver/database/ app server ?? (think Weblogic cache the statement even if an explicit close is called), if so what does the call to close does, other than closing the result set
(Or)
you guy recommend never to close the prepared statement.
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
You should always close the PreparedStatement. If the container vendor wants to implement optimizations, it can do it transparently to your code.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Vinod John:
When I call close() on prepared statment object will the compiled version of the sql be discarded or the database overrule's this call and still retains it.[... ] (Or) you guy recommend never to close the prepared statement.
It is best to close the prepared statement. To do anything else can cause resource leaks (having to troubleshoot an Oracle "maximum number of open cursors exceeded" is no fun). As for your first question, that is implementation dependent. The database I know best is Oracle, so I'll talk a bit about that.
Oracle does not quite work the way you might expect. It maintains a database-side statement cache where recently executed statements are cached together with their execution plans. This cache is entirely independent of the lifecycle of the JDBC objects you're using: even after you close() your PreparedStatement, if you create a new one with the same SQL text immediately afterwards, Oracle will be using a cached plan and you'll get the benefits of prepared statements. Even if another client creates it, on a completely different connection.
Confusingly perphaps, this Oracle cache works for every SQL statement, including those that you create using a Java Statement object. So does that mean it won't make a difference which one you use? False -- it certainly will. When you use a Java Statement object, the SQL text will be different for every different value of your parameters. With PreparedStatements, the same SQL is used regardless of parameter values. The effectiveness of Oracle's cache will therefore generally be much lower for Statements than for PreparedStatements, and the latter will still be faster for anything executed a fair number of times.
- Peter
[ October 14, 2003: Message edited by: Peter den Haan ]
 
Consider Paul's rocket mass heater.
 
subject: Prepared Statement Doubt!!