File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes PreparedStatement smarter than I thought? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement smarter than I thought?" Watch "PreparedStatement smarter than I thought?" New topic
Author

PreparedStatement smarter than I thought?

Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
I used to believe that a PreparedStatement's efficiency benefit could be realized only if it was prepared once (incurring the overhead of preparing the statement) and then re-used, like this:But I recently read something (can't remember now) that made it sound like you would still get the efficiency benefits even if you create the ps every time you use it, like this:So even though I'm creating a new instance of a ps, the Connection is smart enough to realize that it already prepared that statement before and not incur the overhead of preparing the statement again.

Is that correct?
Does the statement caching occur at an even lower level? i.e. if I'm using connection pooling (so a different connection may be used each time the ps is created), is the statement cached at the db level and available for any Connection to take advantage of it? Like in this example:

BTW - I realize there are additional benefits/reasons for using PreparedStatement (like proper quote escaping).


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Chris Mathews
Ranch Hand

Joined: Jul 18, 2001
Posts: 2712
Yes, many Application Servers (such as WebLogic, WebSphere, and even Commons DBCP) support the notion of a PreparedStatement Cache for their Connection Pools so that you still get a performance enhancement even if you "create" the PreparedStatement each time. Regardless, like you mentioned, even without increased performance there are still compelling reasons to choose PreparedStatements over regular Statements.
Joe Nguyen
Ranch Hand

Joined: Apr 20, 2001
Posts: 161
Chris,
PreparedStatement caching is per connection. I am not sure how app server cache perparedStatement using different connections?

Yes, I even cache at the lower level on DB server. It saved the execution path, etc..
Chris Mathews
Ranch Hand

Joined: Jul 18, 2001
Posts: 2712
Originally posted by Joey Tran:
Chris,
PreparedStatement caching is per connection. I am not sure how app server cache perparedStatement using different connections?

Yes, I understand this. The point is that the PreparedStatement is cached when the Connection is returned to the pool. For example, see
Increasing Performance with the Statement Cache from the WebLogic docs.
Joe Nguyen
Ranch Hand

Joined: Apr 20, 2001
Posts: 161
Thanks Chris. Let me know if I understand it correctly. The connection given by a connection pool is just a connection wrapper, not a real connection.
Connection conn = ds.getConnection();
When an application creates a preparedStatment, connection pool will look up the cache and return the connection which associated with the preparedStatement in the cache. This connection may or may not be the same as the connection above.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement smarter than I thought?
 
Similar Threads
Multiple preparedStatements 1 Connection
error in inserting string into database
Performance Issues with database insert !!
Basic Java Help
Example of INSERT