Spring Roo App with multiple datasources

[mserioli]

Hi everyone.

My Roo application needs to access two different db.

The main is a mysql db, setted up by Roo shell persistence command.

The other one is a mssql db and I use it for reading the users details. I create a reference to this datasource in applicationContext. Then I've created by hand the User class in my domain package setting the datasource defined in applicationContext. Then I've created the controller and the views, so I can show for example the list of all the users in user table.

Now, in my Job entity (stored in the main db), i need to put a reference to the User.

@NotNull
@ManyToOne(targetEntity = User.class)
@JoinColumn
private User user;

But when I run the project I get this error:

Invocation of init method failed; nested exception is org.hibernate.AnnotationException: @OneToOne or @ManyToOne on domain.Job.client references an unknown entity: domain.User.

So I put the @Entity annotation in the User class but i got this error:

Invocation of init method failed; nested exception is org.hibernate.AnnotationException: No identifier specified for entity: domain.User

What I do wrong?

Is this the best way for handling multiple datasources in Roo?

Thank you for any reply
Marco

[Rod Johnson]

You can't manage a relationship between two different databases using JPA, unless you have linked the databases somehow--I know you can do that in Oracle, but you probably can't do it across different vendors.

So you will have to manage that relationship yourself in custom code, and will need 2 entity managers. Your transaction management will also get complicated, however you do this.

So I'm afraid there is no easy solution...

[mserioli]

Hi and thank you for your reply!

Luckily I found a solution in the forum: http://forum.springsource.org/showpo...77&postcount=4

Now it works, but I have another question. Hibernate creates in my second db a version column.. how I can disable this? The db must be readonly. I don't need to handle the version and I don't want allow changes at the db!

Thank you for any reply
Marco

[pgrimard]

This probably isn't the best solution, but in my experience if I have the following code in my entity class, Hibernate doesn't query for the version.

@Transient
@Version
int version = 0;

[mserioli]

When I try to do the code suggested by pgrimard

@Transient
@Version
int version = 0;

I get the following error in Roo shell:

User provided @javax.persistence.Version field but failed to provide a public 'getVersion()' method in ...

Another thing I found is that when I modify the code of any of my domain class or controller class I need to re-launch the application or I get this error:

org.hibernate.exception.JDBCConnectionException: Cannot open connection

[pgrimard]

Because you're providing your own "version" property, you do need to create your own getter/setter methods for it too, that's what Roo is telling you.

If your getting connection exceptions, I suggest you double check how you've got your EntityManagerFactory beans setup. I suspect you had to do some sort of customization if you've got multiple data sources.

[mserioli]

Thank you for your reply pgrimard!

I've solved the first mistake.. but not found a solution for org.hibernate.exception.JDBCConnectionException.

Summarizing, I've defined 2 persistence unit in persistence.xml file

<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="update"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/17025" />
            <property name="hibernate.connection.username" value="root" />
            <property name="hibernate.connection.password" value="password" />
        </properties>
    </persistence-unit>
    
    <persistence-unit name="persistenceUnitOther" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
            <!--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:sqlserver://remoteHost:3224;databaseName=dbName;user=root;password=password" />            
        </properties>
    </persistence-unit>

So in my application-context I defined a DefaultPersistenceUnitManager and configured it to search configuration in persistence.xml

<bean id="pum" class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
	<property name="persistenceXmlLocations">
	    <list>	     		
	    	<value>classpath*:META-INF/persistence.xml</value>
	    </list>
	  </property>	  	
</bean>

Then I defined my 2 entity manager:

<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="entityManagerFactoryOther">
        <property name="persistenceUnitManager" ref="pum"/>
        <property name="persistenceUnitName" value="persistenceUnitOther" />
    </bean>

Then i defined the 2 transaction manager:

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

And at the end I declared:

<tx:annotation-driven mode="aspectj" transaction-manager="transactionManager" />	

<tx:annotation-driven mode="aspectj" transaction-manager="transactionManagerOther" />

If someone have suggestions..

[mserioli]

I've more details about exception:

Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Cannot open connection