PreparedStatement with Connection Pooling

[Junaid Bhatra]

What advantage does a PreparedStatement object have over a simple Statement object when we close the PreparedStatement or Connection objects? Also if we use a Connection Pool, what use are PreparedStatements, since we do not know which Connection we are getting from the Connection pool.

[Shripad Bokil]

Hi,
The advantage of prepared statement is you can use prepared statement repeatedly in your program as you are specifying the values to be intered by question mark & those question marks are replaced by the values which you get somewhere in the program.
In other case i.e. in simple statements the values have to be hardcoded.
Shripad

[Junaid Bhatra]

I know what PreparedStatements are and what advantages they offer over simple Statements. What you might not know is that PreparedStatements are only useful if you re-use them over the same Connection object. Thus if you close the Connection, or in a common scenario, use a ConnectionPool, then what's the advantage of using PreparedStatements. As the database would again need to re-compile and execute the query.

[Thomas Paul]

In that case, PreparedStatement offers no advantage.
Connection pooling can be implemented different ways. One way which would make sense with PreparedStatement is to get a Connection object from the pool and then use it over and over again until you no longer need it and then return it to the pool.

[maha anna]

Yes. Generally I use this format.
Connection conn = pool.getConnection();
// Do whatever db operations
pool.free(conn);
So in this case , I retain the same conn object for all of my db operations and return to pool after I am done with it.
It makes sense, to use PreparedStatements, if we are going to execure the same SQL query more than once, with different parameters.
regds
maha anna