What is the best practice managing multiple dynamically created databases through Spring and Hibernate?

Question

Let say that for each customer you have to dynamically create a database when customer subscribes to services, all databases are based on same schema.

As customers are authenticated (one master database managing all customer details), their unique username is used to access the corresponding database and retrieve needed information.

Question 1: Can the above be considered a good approach to this kind of problem or is there a better solution?

Question 2: In case there is no better solution, how can this be implemented using Spring & Hibernate?

Edit: What I need to know, is how to implement datasource creation upon customer subscription without editing the Spring configuration file. It needs to be automated.

Did you actually manage to create and use the dynamically created dbs? — Lipis, Apr 24 '14 at 08:15
@Adnan Doric can you please see this question and help me in shap of boilerplate or any git open repo? https://stackoverflow.com/questions/53297213/how-to-switch-database-on-runtime-in-springboot-and-springdatajpa-hibernate — Irfan Nasim, Nov 15 '18 at 06:51

score 6 · Accepted Answer · edited May 23 '17 at 11:46

6

Question 1: There are various options. This article talks about these options with pros and cons of each option.

Question 2:

Spring supports dynamic data source routing. May be you should start from there.
You can also create the data sources dynamically provided you let Spring manage the data sources for you. All you have to do is register a bean of type com.mchange.v2.c3p0.ComboPooledDataSource or org.apache.commons.dbcp.BasicDataSource in the running Spring app ctx. Read the article Altering your applicationContext at runtime on how do this.

Community

1
1

answered Feb 08 '11 at 04:04

Aravind Yarram

78,777
46
231
327

Thank you for your input, but I fail to see how the spring example can help, the beans for each service are _hardcoded_ in the XML. If another customer is _dynamically_ added, the XML needs to be modified, right? I would like to know how to handle the case where customers are added dynamically (not just predefined). – a.s.t.r.o Feb 08 '11 at 04:27
@Adnan Doric - that is why I've mentioned you need to START from there and if required customize the provided implementation – Aravind Yarram Feb 08 '11 at 04:29
I don't know how to start implementing that, that's why I asked the question in the first place. I can see lot of implementations of datasources **swapped at runtime**, but nothing with datasources **dynamically created at runtime** datasources. – a.s.t.r.o Feb 08 '11 at 11:56
Please read my comment below on how I successfully managed to set up the dynamic switching using Postgres database – Simon Mbatia May 17 '15 at 10:39

score 3 · Answer 2 · edited Sep 07 '16 at 07:19

I struggled with this problem for quite some time and I managed to hack it! Such that if a new DB for a client is added, the database is instantly accessible via the software since each client has a dedicated DB and the schema design is the same.

The data source is modified at runtime to connect to the DB you want. Typically our convention is the account name of the user is the Db name e.g. https://serverurl/accountname

Here is the breakdown:

Applicationcontext.xml

<bean id="dataSource" class="com.package.util.TenantRouter">
    <property name="targetDataSources">
        <map>
            <entry key="db" value-ref="db"/>
        </map>
    </property>
    <property name="defaultTargetDataSource" ref="parentDataSource"/>
</bean>

<bean id="parentDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://localhost:6432/db?autoReconnect=true"/>
    <property name="username" value="DBUSER"/>
    <property name="password" value="DBPASS"/>
</bean>

<bean id="db" parent="parentDataSource">
    <property name="url" value="jdbc:postgresql://localhost:5432/db?autoReconnect=true"/>
    <property name="username" value="DBUSER"/>
    <property name="password" value="DBPASS"/>
</bean>

In the TenantRouter class, these two methods are IMPERATIVE:

@Override
protected Object determineCurrentLookupKey() {
    String tenant="defaultdb";
    if (UserContextUtil.getUserContext()!=null){
        tenant = UserContextUtil.getUserContext().getTenant().toString();
    }
    return tenant;
}

@Override
protected DataSource determineTargetDataSource() {
    //current DB
    String db_name = (String) determineCurrentLookupKey();
    //System.out.println("THIS DB:"+db_name);
    DriverManagerDataSource ds = new DriverManagerDataSource();  
    ds.setDriverClassName("org.postgresql.Driver"); 

    String url="jdbc:postgresql://localhost:5432/"+db_name+"?autoReconnect=true";
    //System.out.println("URL:"+url);
    ds.setUrl(url);  
    ds.setUsername(username);  
    ds.setPassword(password);
    return ds;
}

The UserContextUtil.getUserContext().getTenant().toString() returns the requested Db to connect to which is supplied in the URL of the client.

I hope this helps somebody who undergoes such headache.

Cheers!

can you check this question and guide me accordingly @Simon Mbatia https://stackoverflow.com/questions/53297213/how-to-switch-database-on-runtime-in-springboot-and-springdatajpa-hibernate — Irfan Nasim, Nov 15 '18 at 07:13

What is the best practice managing multiple dynamically created databases through Spring and Hibernate?

2 Answers2