Using PreparedStatement pooling in dbcp

Question

Can someone explain how exactly prepared connection pooling using dbcp can be used? (with some example code if possible). I've figured out how to turn it on - passing a KeyedObjectPoolFactory to the PoolableConnectionFactory. But how should the specific prepared statements be defined after that? Right now I'm only using a PoolingDataSource to get connections from the pool. How do I use the prepared statements from the pool?

Answer 1

Well talking about getting connection from the pool vs getting "not-pooled" connection, do you have any change in your code :)? I bet you do not. Same way with prepared statements. Your code should not change. So, there is no useful code example to this.

You should read docs for your JDBC Datasource implementation and see what developers have to say about pooling. There is no other source of reliable info on this.

From here: This component has also the ability to pool PreparedStatements. When enabled a statement pool will be created for each Connection and PreparedStatements created by one of the following methods will be pooled:

* public PreparedStatement prepareStatement(String sql)
* public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)

So, you just keep using prepareStatement() call and your dbcp will in theory take care of pooling (i.e. if you are trying to create "select * from users u where u.name like :id", it will try to find this statement in the pool first)