File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes PreparedStatement with Connection Pooling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "PreparedStatement with Connection Pooling" Watch "PreparedStatement with Connection Pooling" New topic

PreparedStatement with Connection Pooling

Junaid Bhatra
Ranch Hand

Joined: Jun 27, 2000
Posts: 213
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

Joined: Jan 30, 2001
Posts: 13
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.
Junaid Bhatra
Ranch Hand

Joined: Jun 27, 2000
Posts: 213
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
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
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.

Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
maha anna
Ranch Hand

Joined: Jan 31, 2000
Posts: 1467
Yes. Generally I use this format.
Connection conn = pool.getConnection();
// Do whatever db operations;
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.
maha anna
I agree. Here's the link:
subject: PreparedStatement with Connection Pooling
It's not a secret anymore!