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 PreparedStatements 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 "PreparedStatements" Watch "PreparedStatements" New topic
Author

PreparedStatements

Clive vanHiltenn
Greenhorn

Joined: Feb 03, 2010
Posts: 6
Hello,

I'd be grateful for a view on the use of PreparedStatements in Java.

I have a class with a number of methods. At the moment I have one PreparedStatement variable which is used by all methods requiring a PreparedStatement. I wonder whether having just one shared PreparedStatement means that I'm losing the benefit of MySQL cacheing the query, because each time a different method uses the PreparedStatement it 'flushes' the previous query from MySQL. Or is that not how it works? Is it a case of once MySQL has the query text it retains it?

Thanks,

Clive
Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

Best practice is: Don't share preparedStatements.

as for your caching question:
http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html

It reads to me that the query is cached in the database regardless of what happens to the PreparedStatement object
Clive vanHiltenn
Greenhorn

Joined: Feb 03, 2010
Posts: 6
Thanks Tim, I'll adopt best practice and create new instances.
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5812
    
    7

It is my understanding that when you create a PreparedStatement that the SQL text is sent to the database server and if the text exactly matches a s statement in the prepared statement cache then that prepared statement is reused. If it does not match exactly a new statements is created in the cache. An LRU algorithm is used to flush an older statement in the event that the cache is full (see the max_prepared_stmt_count setting).

But like Tim said, create a new PreparedStatement object each time - don't reuse the one object. The object is not expensive to create.


JBoss In Action
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

Peter is correct. The SQL is what matters. Depending on your driver, the connection might matter as well. Some drivers cache locally so the SQL test and the connection object in the pool combine for the cache.

And the PreparedStatement shouldn't be reused.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Clive vanHiltenn wrote:At the moment I have one PreparedStatement variable which is used by all methods requiring a PreparedStatement. I wonder whether having just one shared PreparedStatement...


I think perhaps you are confusing variables with the objects they refer to. It really doesn't matter whether at some point in the code, variable "ps" refers to different PreparedStatement objects at different times. At least, as far as what happens to those objects and what happens to the database, it really isn't relevant.
Clive vanHiltenn
Greenhorn

Joined: Feb 03, 2010
Posts: 6
Many thanks to all who replied.
 
 
subject: PreparedStatements
 
Similar Threads
PreparedStatement not working
Passing variables
PreparedStatement related query
PreparedStatement storage & lifetime
How to construct the SQL statement ?