Hibernate: Using two different DataBase schemas in the same application

Question

Context

I'm creating a database environment where I'd like to split data in several different schemas to be used for different groups of users. Although, one of these databases should be shared to everyone due to it contains common entities.

Suppose databases:

• DB1 - Common entities;
  • Wheels entity
• DB2 - Group "A";
  • Cars entity
• DB3 - Group "B";
  • Motorcycles entity

I have three different projects:

• Project 1:
  • Wheels bean
• Project 2:
  • Cars constructor
• Project 3:
  • Motorcycles constructor

Problem

I'm trying to access wheels (Project 1) from projects/schemas (2,"A") and (3,"B")

First question: Is it possible? Second: How can I do it?

hibernate.cfg.xml in project 2 is configured to

<property name="hibernate.connection.url">jdbc:mysql://99.999.999.99:3306/DB2</property>

This necessarily must restrict all the connections to DB2, or there's another way to add a new connection or work with all databases in 3306 port, or at least DB1?

Mapping the entities from project1 in project 2 seems not to be succeeded too, like:

<mapping class="com.company.project1.Wheels"
        package="com.company.project1.Wheels" resource="com/company/project1/Wheels.hbm.xml"/>

Configuration

• Eclipse Indigo
• MySql 5.5
• Hibernate 3.0 (mapping through xml instead annotations)
• Win 7

Thanks for helping!

Answer 1

You can use @Table(catalog="") to specify database to which they belong to and then also can make relation across database.

in your case Wheel maps to DB1, Car to DB2 and MotorCycle to DB3 using catalog attribute.

i have used this solution with MySQL and MSSQL and works perfectly fine. only constraint this has all three DB has to be in same database server and user which is being used to access db should have appropriate permission to all DB.

As this solution just adds schema name against table in all queries.