Two databases

[arzall]

Hi, Im new in Spring Roo and sorry for my english, Im spanish.
I am not sure if this post has to be here or in data access.

My problem:
Im trying to use two databases, really one database is read only. I read many posts this week but no result .

I need 4 or 5 read only entities from one database, and rest of entities from the other database.

My last try was duplicate the persistence unit on persistence.xml, and datasource, transaction manager and entity manager factory on the aplicationContext file. And I get the exception about 2 beans entity manager factory were found.

Also I don't know how to choose the entity manager factory on the entities. I tried adding a public method to set the entity manager factory but i get an exception about spring find more than one bean of type entityManagerFactory.

Thanks for any suggestions or an example

[Stefan Schmidt]

This is more of a Spring framework question than Roo. Out of the box Roo will configure your app with only one database. But you can then manage your configurations to use the second DB. See more info here http://static.springsource.org/sprin...pa-multiple-pu

HTH,
Stefan

[arzall]

Hi Stefan, thanks for the reply. I have seen this doc yet and really it was my last try, but i don't know how to choose the datasource.

Anyway, thanks you for point me in the right direction. I will investigate it.

[arzall]

Hi Stefan. Finally I have it working.
These are my files:

persistence.xml

<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <!--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="create"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/dpm" />
            <property name="hibernate.connection.username" value="root" />
            <property name="hibernate.connection.password" value="123456" />
        </properties>
    </persistence-unit>
    
    <persistence-unit name="persistenceUnitEW" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <!--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.url" value="jdbc:mysql://localhost:3306/2010ac" />
            <property name="hibernate.connection.username" value="root" />
            <property name="hibernate.connection.password" value="123456" />
        </properties>
    </persistence-unit>

aplicationContext.xml

<context:property-placeholder location="classpath*:META-INF/spring/*.properties"/>
	<context:spring-configured/>
	<context:component-scan base-package="es.grupoduo.dpm">
		<context:exclude-filter expression=".*_Roo_.*" type="regex"/>
		<context:exclude-filter expression="org.springframework.stereotype.Controller" type="annotation"/>
	</context:component-scan>

    <bean id="pum" class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
		<property name="persistenceXmlLocations">
	    	<list>	     		
	    		<value>classpath*:META-INF/persistence.xml</value>
	    	</list>
	  	</property>	  	
	</bean>	    
    
    <bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean>
    
    <bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManagerEW">
        <property name="entityManagerFactory" ref="entityManagerFactoryEW"/>
    </bean>
    
    <tx:annotation-driven mode="aspectj" transaction-manager="transactionManager"/>
    <tx:annotation-driven mode="aspectj" transaction-manager="transactionManagerEW"/>
    
    <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
        <property name="persistenceUnitManager" ref="pum"/>
        <property name="persistenceUnitName" value="persistenceUnit" />
    </bean>
    
    <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactoryEW">
        <property name="persistenceUnitManager" ref="pum"/>
        <property name="persistenceUnitName" value="persistenceUnitEW" />
    </bean>

One entity:

@Entity
@RooJavaBean
@RooToString
@RooEntity(identifierColumn = "CODIGO")
@Table(name = "clientes")
public class Cliente {

    @NotNull
    private String nombre;   
    
    @PersistenceContext(unitName="persistenceUnitEW")
    transient EntityManager entityManager;
}

Thanks

[Stefan Schmidt]

Nice work! - and thanks for sharing, I am sure other Roo users will find this useful . Maybe you want to summarize your steps in a blog article?

-Stefan

[michartmann]

Hi,
We are trying to implement Spring Roo on our legacy project but are facing some problems using to different entitymanagerFactories

Arzall were able to use following on his Roo Entity
@PersistenceContext(unitName="persistenceUnitEW")
transient EntityManager entityManager;
However this is not allowed by STS and the latest Roo 1.x release. Are there changes to the way Roo entity aspects are generated since it worked for arzall but not on the latest release?

Cheers,
Michael

[netname]

I followed the above suggestion and I was able to compile and run transactions from two different databases, but the database (MySQL) is not really being updated with the transactions. I do not get any errors when persisting but the data is not actually saved.

I will appreciate any suggestions.

Thanks,

Alberto

[netname]

Hi,

Ken rimple from Spring Roo in Action helped me to sort this out: http://www.manning-sandbox.com/threa...39189&tstart=0

I also added the request for Roo to improve it's support for two or more databases. The Issue is: https://jira.springframework.org/browse/ROO-1243

Thanks,

[sanjoykr78]

Hi,

I have done the same thing as outline by arzall. My problem is all entities get created in both databases.

My two entites have the below config:

For DB1

@Entity
@RooJavaBean
@RooToString
@RooEntity
public class Person {

@PersistenceContext(unitName="persistenceUnit")
transient EntityManager entityManager;

}


For DB2

@Entity
@RooJavaBean
@RooToString
@RooEntity
public class Title {

@PersistenceContext(unitName="staticPersistenceUni t")
transient EntityManager entityManager;

}

Any idea guys why this is happening.

Thanks

[delgad9]

Guys, I believe I got it right now...

Please visit my showcases web site to see it working at https://pragmatikroo.org/showcases. Select the "Two databases" (TDBs) showcase.

TDBs is Roo web app with independent CRUD operations against two mysql db. No "all entities get created in both databases" issue.


This is what I did.


On the application.xml file: I created another datasource, entityManager and transactionManager beans for the 2nd db. So each db had is own set of beans. Not to mention that each bean should have unique id.

On the database.properties: Added parameters for 2nd db and modified to properly work with application.xml.


On the persistence.xml: Duplicate original persistence unit and named both with different names. Use the <class></class> and the <exclude-unlisted-classes>true</exclude-unlisted-classes> elements. The <classes> element should group the entities only managed with correspondent PU.

Important: Add a persistenceUnitName property to each one of your entityManagerFactory with the name you set you persistent unit of the persistence.xml file.

I mean: <property name="persistenceUnitName" value="your persistence unit name here"/>


On the web.xml: Add a second OpenEntityManagerInViewFilter and set the entityManagerFactoryBeanName parameter with the id of entityManageFactories defined in application.xml

Finally on Entities files: annotate EntityManager variable with @PersistenceContext(unitName="you persistence unit name here").


Note: As you can see this configuration uses a connection pool. As the original single source created by Roo.

Well, If interested try it and post how it goes on your box.

Feedback is always welcome

Cheers
jD