Multiple Schema Configuration On Spring MVC + Hibernate + JPA

Question

I am currently using 1 schema(called SCHEMAADMIN) in the database for all transactions in the app(s), every tables are all in this 1 schema.

Then my company is restructuring the database management and require every app to have 1 scheme for read/write and only allows readonly/SELECT to the main schema, which is the current one I'm using(SCHEMAADMIN).

So here is my data.xml file,

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="
    http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd
    http://www.springframework.org/schema/jdbc
    http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
    http://www.springframework.org/schema/data/jpa
    http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

<jpa:repositories base-package="org.portal.data.repository" />

<tx:annotation-driven />

<bean id="transactionManager"
    class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="database" value="${database.database}"/>
            <property name="databasePlatform" value="${database.databasePlatform}"/>
            <property name="showSql" value="${database.showSql}"/>
            <property name="generateDdl" value="${database.generateDdl}"/>
        </bean>
    </property>
    <property name="packagesToScan" value="org.portal.entity"/>
</bean>

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
    <property name="driverClassName" value="${database.driverClassName}" />
    <property name="url" value="${database.url}" />
    <property name="username" value="${database.username}" />
    <property name="password" value="${database.password}" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="1800000" />
    <property name="numTestsPerEvictionRun" value="3" />
    <property name="minEvictableIdleTimeMillis" value="1800000" />
    <property name="initialSize" value="1" />
    <property name="maxActive" value="50" />
    <property name="maxIdle" value="20" />
</bean>

Then this is the config.properties file,

# database general setting
database.database=ORACLE
database.databasePlatform=org.hibernate.dialect.Oracle10gDialect
database.showSql=false
database.generateDdl=false
database.driverClassName=oracle.jdbc.driver.OracleDriver

# DEVELOPMENT local
http://localhost:8080
database.url=jdbc:oracle:thin:@192.168.1.1/orcl
database.username=schemaadmin
database.password=password
security.login.callbackUrl=http://localhost:8080/security/callback

The new schema is in the same database/url but with different username and password. I am confused on where/how to connect another one in this file. Read through some info through Google but I am practically confused right now.

Please advice. Thank you.

Answer 1

The new schema is in the same database/url but with different username and password. I

With Oracle, Schema and User are tightly related.

I propose a no JTA solution because your configuration doesn't rely on Java EE datasource.
To address this change you should consider having two sets of datasources/entityManagerfactory/transactionmanager : one for read-only user/schema and another for read-write user/schema. Of course, the common configuration could be factored in properties.

To give you an idea, I modified your xml conf and I started the job (I didn't factor properties duplication but you should) :

xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="
    http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx.xsd
    http://www.springframework.org/schema/jdbc
    http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
    http://www.springframework.org/schema/data/jpa
    http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

<jpa:repositories base-package="org.portal.data.repository" />

<!-- The original declaration is no longer valid --!>
<tx:annotation-driven  transaction-manager="transactionManagerRead" />

<bean id="transactionManagerRead"
    class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactoryRead"/>
</bean>

<bean id="transactionManagerReadWrite"
    class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactoryReadWrite"/>
</bean>

<bean id="entityManagerFactoryRead"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSourceRead" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="database" value="${database.database}"/>
            <property name="databasePlatform" value="${database.databasePlatform}"/>
            <property name="showSql" value="${database.showSql}"/>
            <property name="generateDdl" value="${database.generateDdl}"/>
        </bean>
    </property>
    <property name="packagesToScan" value="org.portal.entity"/>
</bean>
<bean id="entityManagerFactoryReadWrite"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSourceReadWrite" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="database" value="${database.database}"/>
            <property name="databasePlatform" value="${database.databasePlatform}"/>
            <property name="showSql" value="${database.showSql}"/>
            <property name="generateDdl" value="${database.generateDdl}"/>
        </bean>
    </property>
    <property name="packagesToScan" value="org.portal.entity"/>
</bean>    

And so for...

There is however a tricky matter in the spring configuration :

<tx:annotation-driven/>

It uses by default a transaction manager bean named transactionManager. So you should configure it with one of transaction managers as I did :

<tx:annotation-driven  transaction-manager="transactionManagerRead" />

Then, to apply the suitable transaction manager in your processing, you should specify in your Spring services the transactionManager to use (read or read-write). You can also define a primary transactionManager which will be used by default. By security, it should be the transactionManager with the most of limitation.

For example, in a Spring Service Bean where you do only reading, you could declare the class to use read-only transaction manager :

@Transactional(transactionManager="TransactionManagerRead")
public class CustomerSearchService(){
...

Of course, you can define the transaction manager at method level.
But ideally, if you have almost as many methods in writing and in reading, you should check if you can redesign or rearrange your classes to gather read-only and write methods. It would be more maintainable and less error-prone.