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
- 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 - What about developing a custom persistent database based datasource pool? Any experience with performance here? Any advice? Any libraries that do that?
- 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?
- 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
- 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?