Connection pool for dynamic database connections

Question

The problem setup is based on a webservice (Spring/Java, Tomcat7 and MySql) where every user gets their own database, hence each request needs their own connection. As all databases are created dynamically during runtime, configuring them statically before startup is not an option.

To optimise database connection usage, an implementation of a database connection pool would be great, right?

With Java/Spring: How would I create a connection pool for dynamic databases? I am a bit struck by the lack of clean options here!

Problem: Tomcat's Connection Pool (and as far as i understand C3P0 as well) treats each new DataSource instance as a whole new connection pool -> stack-reference

1. Is it a good idea to create a static datasource with a generic MySql connection (without specifing the database on connection) and use a connection pool with this datasource together with adapted SQL statements?
   stack-reference
2. What about developing a custom persistent database based datasource pool? Any experience with performance here? Any advice? Any libraries that do that?
3. Or would it be feasable to workaround Tomcat's DataSource problem by creating Tomcat JNDI Datasources dynamically by manipulating it's context.xml dynamically from Java?
4. I can't believe that there aren't more plain/simple solutions for this. Grails/Hibernate struggles with this, Java/JDBC struggles with this, ... is it such a rare use-case to separate userdata on a user basis by creating user specific databases dynamically? If so, what would be a better setup?

EDIT

5. Another option is the suggestion from @M.Deinum to use a single configured datasource and dynamically hotswap it for the right connection ->M.Deinum Blog and stack-reference.
   How does that perform with a connection pool like the ones above?

Answer 1

I believe that HikariCP works without having to specify a single database.

Answer 2

Once the databases are created in runtime, you have to create the pools also in runtime. I am afraid the spring infrastructure is not giving you any help here, as it is tuned for the usual static use case.

I'd have a map of pools:

• have a

   Map < connectionUrlString,List< c3poPool > > map

• when requesting a connection, get the corresponding c3po pool from the map
• and you can get the best of both worlds, since the real connection pool for each dynamically created database is handled by a c3po instance, but you can create new instances in runtime

This works as a low-level solution. If you want to go further, you can wrap this logic into a db connection provider, and register that as a "driver". This way any part of your application requests a new connection, you can just return one connection from the existing pools (and if a totally new connection is requested, create a new pool for that).