This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement" Watch "PreparedStatement" New topic
Author

PreparedStatement

David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

So I heard that using a PreparedStatement is more efficient than executing a query directly on a String. I'm not disagreeing since this is the way I tend to go about it, but it would be nice to why.
I'm assuming the difference is all at the database end, is that true and approximately what is going on under the covers?
Dave.
rani bedi
Ranch Hand

Joined: Feb 06, 2001
Posts: 358
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
Performance is increased if you have to use the same SQL statement again n again.


Cheers,<br />Rani<br />SCJP, SCWCD, SCBCD
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

So if you're not going to execute the same PreparedStatement again there is no advantage?
Alan Watts
Greenhorn

Joined: May 14, 2001
Posts: 28

I gather this Extension of Statement is of relevance to sophisticated applications such as parametric SQL statements that have a a bit of overhead. So, the advantage would be pure efficiency.
Cheers
Alan
rani bedi
Ranch Hand

Joined: Feb 06, 2001
Posts: 358
If you want to execute a SQL Statement many times, its better to use PreparedStatement object. It reduces execution time.
In most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. The PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first.
Another advantage of using PreparedStatement when your SQL statements can take parameters. You can use the same statement and supply it with different values each time you execute it.
john_guthrie
Greenhorn

Joined: Jun 14, 2001
Posts: 16
One thing to note. The "fact" that a PreparedStatement is optimized for reuse may or may not be true on the backend database. In some cases, when you issue a prepareStatement a query plan for that statement is built in the database engine, all ready to run. But query plans take up space and if the database is heavily used your plan may get flushed before you even get a chance to use it.
Also, some JDBC implementations have a default behavior of not even building the query plan (jConnect, Sybase's JDBC driver, is an example of this). They basically ignore the prepareStatement and treat the thing like any other statement (note that with jConnect you *can* force it to actually prepare the statement by setting a property when you build the connection - see Sybase's documentation).
Having said that, I find PreparedStatements easier to work with from a programming aspect. It would be nice though, for debugging, if there were an easy way of getting back as a String the SQL that was actually executed, parameters and all.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Ta ppls.
I agree with the fact that PreparedStatements are cleaner to use. It's the way I've always done it.
So, to put the original question in a context:
I've written a framework that allows you to extract the common db operations and only requires you to implement the sql data specific to the current query.
eg

(I realise there are some problems above but you should be able to get the picture)
Here's the next question:
So, if PreparedStatements are reusable can I create a PreparedStatement once and cache it for successive queries that would set the parameters themselves?
I believe that this would have to be done via some extension to ConnectionPooling cos you'd be managing resources at a lower level, but is it possible?
Dave.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement
 
Similar Threads
PreparedStatements
Not enough storage error
How to escape this JDBC string when right side is a little complicated?
prepareStatement()
jdbc