Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatements

 
Clive vanHiltenn
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Tim, I'll adopt best practice and create new instances.
 
Peter Johnson
author
Bartender
Posts: 5852
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34672
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Clapham
Sheriff
Posts: 21137
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Many thanks to all who replied.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic