4

I have a basic question about the Tomcat 7 JDBC Connection Pool: is a separate pool created for each separate database (i.e., URL)? Or is a single pool created that holds open connections from any number of different databases?

For example, if I open connections to databases A and B by doing this:

PoolProperties poolProperties = new PoolProperties();
poolProperties.setDriverClassName("org.postgresql.Driver");
poolProperties.setUrl("jdbc:postgresql://myserver/db_a");
poolProperties.setInitialSize(1);
poolProperties.setMaxActive(10);
poolProperties.setMaxIdle(1);
poolProperties.setMinIdle(0);

and then this:

PoolProperties poolProperties = new PoolProperties();
poolProperties.setDriverClassName("org.postgresql.Driver");
poolProperties.setUrl("jdbc:postgresql://myserver/db_b");
poolProperties.setInitialSize(1);
poolProperties.setMaxActive(10);
poolProperties.setMaxIdle(1);
poolProperties.setMinIdle(0);

Have I just created one pool with a maxActive of 10, or two pools, each with a maxActive of 10? If it's one pool, what if I had changed maxActive to, say, 30 when opening the connection for database B? Does the first call to setMaxActive win, or does the second call override, or does this cause a separate pool to be created?

ricb
  • 1,197
  • 2
  • 12
  • 23
  • I don't think you have created any pool. You have just specified two sets of pool configuration (that you would later use to create two pools). Can you show the code where you use these `poolProperties`? – Thilo Oct 25 '14 at 03:15
  • Howdy. That part is working just fine, so I didn't want to clutter up my post with it. Basically I create a DataSource instance from the pool properties with "org.apache.tomcat.jdbc.pool.DataSource(poolProperties)", then I create a Spring JdbcTemplate object using the DataSource with code like this, "namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);" That all works great. I am just asking what determines when two separate connection pools are created (if ever). Thanks. – ricb Oct 25 '14 at 03:33
  • Of course you have two independant pools: you're creating two DataSources, each with its own properties. – JB Nizet Oct 25 '14 at 06:41

1 Answers1

3

Okay, I did some digging and figured this out myself. (Thanks for the many kind folks on the tomcat-users mailing list!)

JB Nizet is right: if you are creating Tomcat database connection pools from Java code, each DataSource you instantiate literally is/represents a separate connection pool. This was surprising to me; coming from a .NET background, I assumed the Tomcat connection pooling would work like SqlServer/ADO.NET connection pooling: if you use two identical connection strings to get two database connections, these will both come from the same connection pool. However, in Tomcat, when instantiating DataSource objects from Java code, each new DataSource instance is a whole new connection pool. So, if you want to persist these connection pools across JAX-RS web service calls, for example, you need to build your own database-pool (DataSource) cache, put the DataSource instances (one per database) into it, and store it in an object that JAX-RS will persist across web service calls. I just did this, and it is working fine.

btw, Tomcat database connection pooling does offer functionality similar to SqlServer/ADO.NET connection pooling, you just have to use JNDI resources to create your DataSource instances. (In my case this is not an option, since databases are created dynamically in my application, and JNDI definitions are generally created from config files that Tomcat reads at startup.)

ricb
  • 1,197
  • 2
  • 12
  • 23
  • 1
    "*from config files that Tomcat reads at startup*" - that's not true. Tomcat watches the global `config.xml` and if that is changed, it will reload it and change the DataSources accordingly. –  Oct 28 '14 at 21:50
  • Well, that would be useful. There is no "config.xml" under my tomcat/conf directory. You mean context.xml? – ricb Oct 29 '14 at 01:34
  • Yes, sorry I meant `context.xml` –  Oct 29 '14 at 08:26