Sunday, June 1, 2003 06:59 AM
You speak of connection pools so I assume you are using JDBC within an application server. If so, then it is possible to use prepared statement pooling but the details are application server specific.
In weblogic, for example, you can enable a prepared statement cache pool size where you configure your connection pool. Whenever you use prepared statements (whether through EJBs or raw JDBC), the app server will look in its cache to see whether that statement has already been parsed and compiled. If it exists, it will bypass compilation and use the cached result. So there's nothing to change in you code. You still code all the steps as usual but the app server may silently optimize your code.
There is one caveat. For example, if you cache a statement such as select * from xxxTable and then drop and recreate xxxTable, the next time you run the cached statement, it will fail because the exact xxxTable that existed when the statement was prepared, no longer exists. This because statements are bound to database objects whose IDs change everytime they are created. So watch out for this.
I believe it is also possible to change the parameters and call execute() a second time provided you didn't close the connection (but I never personally tried this myself). But this might be more complicated because you'd have to provide your own connection management functionality to keep track of open connections. However, this may be simple if, for example, you are calling the prepared statement many times within a loop.