Using the PreparedStatement pool in dbcp

Question

Can someone explain how you can use a ready-made connection pool using dbcp? (with some example, if possible). I figured out how to enable it - passing KeyedObjectPoolFactory to PoolableConnectionFactory. But how should specific prepared statements be defined after this? Right now I'm using PoolingDataSource to get connections from the pool. How to use prepared statements from the pool?

Answer 1

Well, talking about getting a connection from the pool and getting a “not merged” connection, do you have any changes in your code :)? I bet no. The same with prepared statements. Your code should not change. Thus, there is no useful code example for this.

You should read the documents for your JDBC Datasource implementation and see what the developers have to say about the pool. There is no other source of reliable information about this.

From here : This component also has the ability to combine PreparedStatements. When the agent pool for each Connection and PreparedStatements created by one of the following methods is activated, they will be combined:

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

So, you just continue to use the prepareStatement () call, and your dbcp will theoretically take care of the pool (that is, if you try to create "select * from users u, where u.name like: id", it will try to first find this statement in bullet)

Answer 2

Here is the basic code that I use.

  GenericObjectPool connectionPool = new GenericObjectPool(null); connectionPool.setMinEvictableIdleTimeMillis(1000 * 60 * 30); connectionPool.setTimeBetweenEvictionRunsMillis(1000 * 60 * 30); connectionPool.setNumTestsPerEvictionRun(3); connectionPool.setTestOnBorrow(true); connectionPool.setTestWhileIdle(false); connectionPool.setTestOnReturn(false); props = new Properties(); props.put("user", username); props.put("password", password); ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, props); PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, "SELECT 1", false, true); PoolingDataSource dataSource = new PoolingDataSource(connectionPool); 

Answer 3

The fact is that if you use one Connection , it will cache PreparedStatement , whether you want it or not, the only possible way to influence this is to use the DataSource properties or use the provider API, But these statements are not visible by other connections, and if you will prepare the same expression using a different connection, it will recreate it again. Thus, connection pools, such as DBCP under the hood, allow you to reuse PreparedStatement between different connections (instead of just Connection ) it uses PooledConnection ), they keep track of all statements prepared by all connections.

UPDATE: it seems that I was mistaken in this information, at least I could not find this function in C3P0.