I'm trying to figure out if it's efficient for me to cache all of my statements when I create my database connection or if I should only create those that are most used and create the others if/when they're needed..
It seems foolish to create all of the statements in all of the client threads. Any feedback would be greatly appreciated.
A bit decent database will already cache them. Just fire Connection#prepareStatement()
at the moment you actually need to execute the query. You actually also have no other choice since connection, statement and resultset ought to be acquired and closed in the shortest possible scope, i.e. in a try-finally
block in the very same method as you execute the query.
Opening and closing the connection on every query in turn may indeed be expensive. A common solution to that is using a connection pool, for example c3p0.
I think you're worrying too much, prepared statements already benefit from several level of caching:
PreparedStatement
objects for each database connection in the pool (and return a cached PreparedStatement
on subsequent calls to preparedStatement
on a connection).So actually, I would even say that you might be looking in the wrong direction. The best practice if you want to design a scalable solution is to use a connection pool and to not held a connection longer than needed and to release it (to release database resources) when you're done with it.
This sounds to me like the kind of premature optimization that I wouldn't worry about until I have some information telling me that it mattered. If your database access is inefficient, I'd suspect your schema or access of values before I'd think of caching prepared statements.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With