• 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

PreparedStatements

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 820
IntelliJ IDE VI Editor Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Tim, I'll adopt best practice and create new instances.
 
author
Posts: 5856
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Many thanks to all who replied.
 
reply
    Bookmark Topic Watch Topic
  • New Topic