Hello everybody,

I wonder if someone can tell me how can I manage the following scenario using Hibernate 3.2.x with EJB3/JPA (running on JBoss 4.2.x with MySQL 5.0).

I have an new application using it's own database schema. This application must have access to some tables of a legacy application on the same database server using another database schema. One usage is a simple foreign key relation to a table from the legacy application; other usages will be writting same exchange data to other tables of the legacy app.

The legacy tables I have to use in the new app are described as normal JPA entities, the schema property is used on the @Table annotation. So my first idea was to just create 2 different persistent units in "persistence.xml" with different jta-datasources.

I then saw that Hibernate's validation at server startup complained about missing tables. By debugging what's going on I saw that Hibernate tried to locate ALL tables known from entity annotations in EACH of the datasources (schemas). Maybe this can be managed by unusing the "autodetect" feature and specifying the entities to be used for each schema with the <class> element?

The other question I have is this: If I have 2 persistent units I automatically have to work with 2 EntityManagers. But how can I then relate an Entity from one persistent context to an entity from the other persistent context (because there is a foreign key relation)?

I would be glad if someone could lead me to a simple solution.

Carlo

What about having just one persistence unit managing all this complexity transparently for you application?
Did you read about Hibernate Shards?
http://www.hibernate.org/hib_docs/shards/reference/en/html/preface.html

Besides this, I wouldn't recommend having foreign keys between databases.

Sanne

Thanks for this tip on Shards; but because I don't know Shards I didn't want to start another costly experience on this in my project -:)

I finally found out what I have to do to use different schemas for my entities:
(see also http://forum.hibernate.org/viewtopic.php?p=2377634#2377634)
- create a different persistent unit for each schema in persistence.xml (meaning: you have to use different EntityManagers in your app!)
- force Hibernate to not use autodetection on annotated classes whilest validating the mappings, but to use the <class> elements from persistence.xml
- create different "orm" files for each persistent units if you have externalized queries and declare them in the <mapping-file> element of persistence.xml
- annotate the EnityManager instances you are using in your app with @PersistenceContext(unitName="xxxxxx"

Finally, I changed my foreign key relationship from one schema to the other by using something like a "weak" reference: I do not map the related entity but use just an integer attribute to hold the primary key of the entity from the other schema. This of course means: you have to do the housekeeping yourself and you cannot rely on the automatics you normally can use with an ORM.

Carlo[/url]

There is another thing I had to learn when using two (or more) database schemes and different persistent units. If you use these in an EJB environment (with JBoss), you run into problems if the persistent units are both used in the same transactions - clearly a scenario for 2PC, but which I try to avoid.

Because I use the second database schema (at the point where I am) only in readonly mode, I didn't bother to annotate the corresponding Session Bean with TransactionAttribute.REQUIRES_NEW.
I also think it would be no problem using REQUIRES_NEW in write mode: because if something goes wrong, an exception would be thrown which would be detected in the method of the suspended transaction (which then can do a rollback).

Any other opinions on this scenario?

Carlo

I guess having two persistence units probably works, but maybe you could also get away with just specifying the schema attribute on the @Table annotation. If your db user has access to both schemas and they are even in the same db this approach should work as and is probably easier to manage than two entity managers. Haven't tried it though

--Hardy

Definetely NOT. That was my first trial and it led me to some disappointment: the "Schema=" annotation on @Table has just the effect that on all generated SQL statements the table name is qualified with the schema name.

The effect of this is that Hibernate's DDL generator does the right thing (BESIDES: is there an option to mark a table/entity as NOT for generating?), but when starting the application Hibernate's validator (which you won't missing!) runs into errors. THIS tool does NOT use the schema names. Instead it tries to find ALL mapped tables in BOTH schemas - which does not work. I think this is a BUG - it should differentiate the validation of the mappings along the schema definitions.

Therefore my finally solution was to work with two persistent units on different database schemas.

Carlo