multiple database for each app

Question

Is it possible to create a spring mvc app with multiple database? I am creating a school management web app for schools. For simplify the database management, I want to map a single database for each school, but the web app remains same only change is the configuration of database. I am using jobss as server for my server and JNDI datasource configuration in my web app. And the all apps are hosting on same live server. Is it possible to do that, what is the best way to do that?

spring data source configuration:

<!-- JNDI setup -->
<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName">
        <value>java:jboss/datasources/ResponseDS</value>
    </property>
</bean>

jboss standalone.xml configuration:

<datasource jndi-name="java:jboss/datasources/ResponseDS" pool-name="ResponseDSPool">
    <connection-url>jdbc:mysql://localhost:3306/response</connection-url>
    <driver>com.mysql</driver>
    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
    <pool>
        <min-pool-size>10</min-pool-size>
        <max-pool-size>100</max-pool-size>
        <prefill>true</prefill>
    </pool>
    <security>
        <user-name>root</user-name>
        <password>password</password>
    </security>
    <statement>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
        <share-prepared-statements>true</share-prepared-statements>
    </statement>
</datasource>

score 0 · Answer 1 · edited May 23 '17 at 11:51

I don't see any issue in having multiple datasources in spring and jboss config. But it can become cumbersome in your code to maintain, So i would rather separate the DAO layers very clearly into separate packages or modules which can be reusable , testable individually and both modules should not be aware of each other database related code. This way it will be clear which module is handling which database and its transactions so that you can debug easily if there is any issue.

score 0 · Answer 2 · answered Sep 18 '15 at 04:11

If I understand the question correctly, The application code,modules everything else will remain same but data will come from different databases.

You can configure the multiple data sources but you need to have a logic in your application to select the datasource at runtime depends on some criteria say for example the school logged in user belongs to.

However, every time you add new school, you need to create new datasource and change the logic in application which is not good practice and code will go out of hand soon.

multiple database for each app

2 Answers2