Roo 1.2 and Multiple Databases

[coreym]

I have a Spring Roo application that I have setup with two HSQL databases. This is not a web application. It is designed to be run from the command line. I have two entities. I want one entity to persist to one database and the other to persist to the other database. I can see both databases get created, but both tables and data get stored in the first database. I feel like I'm almost there with this so if anyone could help me out, that would be great. Here are my current settings:

persistence.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="puCust" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <!-- Uncomment the following two properties for JBoss only -->
            <!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
            <!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
        </properties>
    </persistence-unit>
<persistence-unit name="puGroup" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <!-- Uncomment the following two properties for JBoss only -->
            <!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
            <!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
        </properties>
    </persistence-unit>
</persistence>

applicationContext.xml (took out the roo generated comments to save space):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd         http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd         http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd         http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.1.xsd         http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">
    <context:property-placeholder location="classpath*:META-INF/spring/*.properties"/>
    <context:spring-configured/>
    <context:component-scan base-package="com.novexinc.foo">
        <context:exclude-filter expression=".*_Roo_.*" type="regex"/>
        <context:exclude-filter expression="org.springframework.stereotype.Controller" type="annotation"/>
    </context:component-scan>

    <bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dsCust">
        <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"/>
    </bean>
    
    <bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dsGroup">
        <property name="driverClassName" value="${database.driverClassName}"/>
        <property name="url" value="${database.url.group}"/>
        <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"/>
    </bean>
    
    <tx:annotation-driven mode="aspectj" transaction-manager="tmCust"/>
    <bean class="org.springframework.orm.jpa.JpaTransactionManager" id="tmCust">
        <property name="entityManagerFactory" ref="emfCust"/>
    </bean>
    
    <tx:annotation-driven mode="aspectj" transaction-manager="tmGroup"/>
    <bean class="org.springframework.orm.jpa.JpaTransactionManager" id="tmGroup">
        <property name="entityManagerFactory" ref="emfGroup"/>
    </bean>
    
    <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="emfCust">
        <property name="persistenceUnitName" value="puCust"/>
        <property name="dataSource" ref="dsCust"/>
    </bean>
    
    <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="emfGroup">
        <property name="persistenceUnitName" value="puGroup"/>
        <property name="dataSource" ref="dsGroup"/>
    </bean>
    
</beans>

database.properties:

database.driverClassName=org.hsqldb.jdbcDriver
database.url=jdbc\:hsqldb\:file\:puCustDB;shutdown\=true
database.username=sa
database.password=

database.url.group=jdbc\:hsqldb\:file\:puGroupDB;shutdown\=true

First entity:

@RooJavaBean
@RooToString
@RooJpaActiveRecord(persistenceUnit = "puCust", transactionManager = "tmCust")
public class Customer {

Second entity:

@RooJavaBean
@RooToString
@RooJpaActiveRecord(persistenceUnit = "puGroup", transactionManager = "tmGroup")
public class Groupe {

[drinks.sobe]

You might try using what is suggested in
14.5.1.4 Dealing with multiple persistence units
http://static.springsource.org/sprin...#orm-jpa-setup

create a "org.springframework.orm.jpa.persistenceunit.Defau ltPersistenceUnitManager"

I'm trying to create a java app that uses two databases as well. I want to create two separate maven modules as well. One for each database so that I can reuse them later.

I have even tried to create multi entity modules and tried to specify different schemas but when running the tests it can't find schemas I specified. I'm using Postgres for DB.

--Chad

[delgad9]

@coreym,

I created a solution for this configuration a while ago...
You can review it at http://viralpatel.net/blogs/2011/01/...iguration.html. Read the comments made by me since the article was published -out my control- with some minor issues.

Bottom line many-many people has successfully used it.


Cordially
jD

NOTE: I just read your question again... My configuration is for web apps. However I believe it can be easily extrapolated for including you case too.

[coreym]

@delgad9: Your post was the one that I followed when I started this exercise, after numerous searches, I think it's the standard tutorial now for multiple databases

My issue is I don't have a web.xml so I'm not really sure where to put the stuff defined as filters in your example.

[delgad9]

@coreym,

You are right, there is not much about how-to solve "real world" development issues like this one out there...

On your issue:

I don't think the configuration that your are targeting is requiring web.xml stuff at all.

Some ideas to work on...

1) Try the way of setting the persistent context in the entity classes as is shown in the article instead of using the new 1.2 roo annotations.

public class DbTwo {

    @PersistenceContext(unitName="persistenceUnit2")
    transient EntityManager entityManager;
	
	@NotNull
    private java.lang.String name;
}

2) Your code only shows one db not two in the database.properties file. Is this correct?


I hope the above helps you and thank you for your comment on the "standard". The heavy lifting is provided by the Spring framework and Hibernate teams. They deserve most of the credit for the implementing such great frameworks.

jD

[tyu]

Hi,

Thanks for the above solution, this works great for me, except for one minor issue.

When i use the above solution, and start the application in tomcat, somehow Hibernate will create tables in both Database. I understand that i can disable the "hibernate.hbm2ddl.auto" on both databases, but i do want to use that in one of the 2 databases.. Is it possible to make Hibernate aware of what tables supposed to be added(or managed) in the corresponding database of the persistent unit ??

Thanks!!!!

[delgad9]

@tyu,

Yes, check the properties in the persistence unit xml config...
There is an attribute for referencing specific tables for a particular PU.


Thx
jD

[tyu]

Hi jD,

Thanks... Hmm, yes, I had multiple <persistence-unit> sections in the persistence.xml...

However, when i use hibernate.hbm2ddl.auto="update" in one of the PU, Hibernate always "create" a union of all tables, and that's the issue i have...

Do you mean in the hibernate.hbm2ddl.auto, there's way to direct Hibernate to just create/update tables of that particularly PU ??


Thanks a lot!!!

-Thomas

[coreym]

1) Try the way of setting the persistent context in the entity classes as is shown in the article instead of using the new 1.2 roo annotations.

Yes, I have tried this without any luck. Seems to create both databases, but the data gets persisted in the first database only.

2) Your code only shows one db not two in the database.properties file. Is this correct?

The only difference between the two databases is the filename itself, so I was reusing most of the properties from the database properties in the BasicDataSource beans.

[delgad9]

@coreym,
It is kinda a crazy conf. With a lot of little things easy to miss..
To move on do following suggestion: start with the exactly original posted conf -that we know it works for sure- and little by little morphed to the one you need.

It should work this time for you.

B. Roogards
jD