DBCP - Prepared statement - exactly how does this work?
So, gleaming what I can from the internet, DBCP supports prepared statements.
I have now set up my PoolableConnectionFactory accordingly, passing a KeyObjectPoolFactory during construction.
So, in my DAO - after retrieving a connection from the pool - I call
PreparedStatement stmt = connection.prepareStatement("select info from brains")
.
I assume that if I make this call more than once on the same connection, it will be handled transparently by DBCP (i.e. It will recognise the prepared statement already exists for the connection, will not attempt to set it again, and will simply return the already prepared statement)?
Now, do I have to close the statement after every usage? Or will this action remove the prepared statement from the connection, negating all advantages gained from preparing? Or does DBCP simply ignore this, and keep the prepared statement for as long as it's connection is being pooled?
Sorry if this seems like a silly question, I really am struggling to find good examples on this!
Cheers,
Lee.
[1113 byte] By [
BigLeea] at [2007-11-27 3:43:18]
# 2
I would close it to be safe. It all depends on your code. I would say it is good practice, then again I am not certified. If you noticed prepared statements allow you to assign values to conditions later.
For example, a prepared statement can be
stmt = conn.prepareStatement ("SELECT info FROM myTable WHERE id = ? AND name = ?");
You can then later add
stmt.setString(index, "mrSmiley");
# 3
Sorry - I didnt make my question overly clear.
I know all about Prepared Statements, and that I need them. I just wanted to know how Apache DBCP handled them. Their documentation is non-existent.
If I continually call connection.prepareStatement() with the same SQL, will it ignore anything after the first call? And should I explicitly close the statement, or is that done when the pool is disposed off?
Hope this makes sense!
# 4
I doubt any general connection pools could do anything to cache prepared statements.
If the actual jdbc driver and/or the database doesn't do it then the pool would have to retain connections along with the statement. But that would circumvent the primary intent of a connection pool (connections that any one can use.)
A driver specific pool might be able to do something special I suppose.
