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?
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.
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.
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.
0
