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.
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.
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
Joined: Feb 06, 2001
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.
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.
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.