I am developing a tool that receives different connection parameters to test values in different databases (a plugin for Nagios in jNRPE that keeps an open connection to different databases).
Because the configuration is dynamic (there could be more databases or they can be removed) I cannot have a configuration file.
I want to know if I should have an instance of C3P0 per database or can I use the same instance and just change the URL each time I ask for a connection?
The code is at github:
https://github.com/angoca/db2-jnrpe/blob/master/src/main/java/com/github/angoca/db2_jnrpe/database/pools/c3p0/DBCP_c3p0.java
If not, how can I get multiple pool for multiple databases dynamically?
You'll need a different c3p0 DataSource for each JDBC url. A Connection pool must contain homogeneous Connections: all checked out Connections must be equivalent from a client's perspective. If Connections from multiple databases were included in the same pool, clients would have no way of specifying or knowing which db they were communicating with.
(If you are replicating, say, a read-only DB and you really want Connections from multiple sources to live in a single pool, because they are guaranteed to be equivalent from a client's point of view, you could do that by defining a custom, unpooled DataSource that round-robined or randomly chose a replicant, and then pooling the DataSource via c3p0's DataSources factory.)
It is very easy to dynamically create and configure c3p0 DataSources. See example code here.
If you capture your dynamic config as a map of c3p0 property names to values, there's also an alternative, more concise way to get a DataSource with that configuration.
