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.

Answer 1

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

Question 2:

1. Spring supports dynamic data source routing. May be you should start from there.
2. 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.

Related

1. Configure spring datasource for hibernate and @Transactional
2. DBCP
3. c3p0

Answer 2

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!