• 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

PreparedStatement

 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 358
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So if you're not going to execute the same PreparedStatement again there is no advantage?
 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 358
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic