13

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

  1. 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?
Community
  • 1
  • 1
N.R.
  • 131
  • 1
  • 4
  • Not sure how hibernate struggles with this as that comes out of the box with multi tenancy support, which is basically what you want. – M. Deinum May 27 '15 at 09:59
  • Care to give an example? Would be delighted to see a working solution! My experience with hibernate multi tenant support let me understand that datasources still must be present/configurated before startup. How can i use hibernate without such datasources and create my connections dynamically? – N.R. May 27 '15 at 10:01
  • By implementing your own `MultiTenantConnectionProvider` and also the default `DataSourceBasedMultiTenantConnectionProviderImpl` uses JNDI for dynamic lookups so those don't need to be available at startup. You can add datasources to your JNDI and Hibernate will do a lookup. – M. Deinum May 27 '15 at 10:26
  • Thanks for the input. I don't know enough about hibernate to comment on that, but implementing another ConnectionProvider falls under what i meant by "...struggles with this...". Do you have any experience on doing that without an ORM? – N.R. May 27 '15 at 10:37
  • You seem to focus on the negative, I also stated that the default uses JNDI for lookups and does so dynamically. So basically the only thing you need to do is register a new datasource in JNDI and be done with it. – M. Deinum May 27 '15 at 10:39
  • 1
    And yes I have experience with it even before multitenancy was a thing, I [blogged](https://mdeinum.wordpress.com/2007/01/05/one-application-per-client-database/) about it too. It was for both frontend, backend or datasources... We replaced 50 application instances with a single one... In 2006... (Blogged about it later). – M. Deinum May 27 '15 at 10:41
  • 1
    ok, thanks for the hibernate help! Also thanks for your blog entry! In your blog you describe a solution using HotSwappableTargetSources. Isn't that a variation of the options (1-3) above? How does this perform for stateless REST and connection pooling? – N.R. May 27 '15 at 10:51
  • 1
    Quite well with several thousands of concurrent users :)... But as mentioned that was before multi tenancy was hot. I would suggest trying what is available first before trying your own. (We needed more then only hibernate like templating the website, for which we integrated with Springs Theming support). – M. Deinum May 27 '15 at 11:16

3 Answers3

3

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

ErikHH
  • 93
  • 3
1

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).

Gee Bee
  • 1,794
  • 15
  • 17
0

First than all, sorry for my english, i'm improving every day.

In my experience, I had a similar situation and it was resolve with spring framework. Let me explain you how you'd solve that question.

  1. Make a spring config file with these characteristics: a) A resource loader: This one is the responsible of load properties from configurations files or from database, those properties will be the appropriates to establish the database connection. b) A pool database configuration parameterized with the properties that you'll load.

  2. Create a locator class: In this class you'll need a HashMap

  3. Use the multi context feature of spring: The idea is assign a code to every one connection that you establish and later load that connection like an application context with spring, then in the locator class, put in the map that context and use it as frequent as you need.

I think is you follow these steps, you can create dynamic pool or database connection as you want.

maframaran
  • 170
  • 9