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.

Answer 2

I would divide my project in multiple self sustained projects. The Wheel project will be self sufficient project which takes care of Wheel entity.

Project 1: Wheel This project will define Hibernate entities and DAO to access / modify wheel definitions. Also I would configure a separate datasource in this project which points to DB1. Entity classes:

@Entity
public class Wheel {
}

DAO classes:

@Repository
public class WheelDAO {
    @Persistence
    private EntityManager em;
}

Basically the idea is to separate application at DAO level. And manage transactions at Service level. Imaging WheelDAO (wired to DB1 datasource) and CarDAO (wired to DB2 datasource) and inject these in CarService.

DB1             DB2          DB2
 |               |            |
WheelDAO       CarDAO       MotorcycleDAO
  \_____________/                   |
   \_____|__________________________/     
         |                        |
         |                        | 
       CarService          MotorCycleService

I suggest to use Spring as IOC container to manage these dependency. Although you can achieve this without using Spring too.

Answer 3

What you need is just a db connection factory which allows you to use db that you want when you need it.

Take a look at the class below which you can adapte to resolve your issue

import java.net.URL;
import java.util.HashMap;

import javax.security.auth.login.Configuration;

public class HibernateUtil {

    private static Log log = LogFactory.getLog(HibernateUtil.class);

    private static HashMap<String, SessionFactory> sessionFactoryMap = new HashMap<String, SessionFactory>();

    public static final ThreadLocal sessionMapsThreadLocal = new ThreadLocal();

    public static Session currentSession(String key) throws HibernateException {

        HashMap<String, Session> sessionMaps = (HashMap<String, Session>) sessionMapsThreadLocal.get();

        if(sessionMaps == null) {
            sessionMaps = new HashMap();
            sessionMapsThreadLocal.set(sessionMaps);
        }

        // Open a new Session, if this Thread has none yet
        Session s = (Session) sessionMaps.get(key);
        if(s == null) {
            s = ((SessionFactory) sessionFactoryMap.get(key)).openSession();
            sessionMaps.put(key, s);
        }

        return s;
    }

    public static Session currentSession() throws HibernateException {
        return currentSession("");
    }

    public static void closeSessions() throws HibernateException {
        HashMap<String, Session> sessionMaps = (HashMap<String, Session>) sessionMapsThreadLocal.get();
        sessionMapsThreadLocal.set(null);
        if(sessionMaps != null) {
            for(Session session : sessionMaps.values()) {
                if(session.isOpen())
                    session.close();
            }
            ;
        }
    }

    public static void closeSession() {
        HashMap<String, Session> sessionMaps = (HashMap<String, Session>) sessionMapsThreadLocal.get();
        sessionMapsThreadLocal.set(null);
        if(sessionMaps != null) {
            Session session = sessionMaps.get("");
            if(session != null && session.isOpen())
                session.close();
        }
    }

    public static void buildSessionFactories(HashMap<String, String> configs) {
        try {
            // Create the SessionFactory
            for(String key : configs.keySet()) {
                URL url = HibernateUtil.class.getResource(configs.get(key));
                SessionFactory sessionFactory = new Configuration().configure(url).buildSessionFactory();
                sessionFactoryMap.put(key, sessionFactory);
            }

        } catch(Exception ex) {
            ex.printStackTrace(System.out);
            log.error("Initial SessionFactory creation failed.", ex);
            throw new ExceptionInInitializerError(ex);

        } // end of the try - catch block
    }

    public static void buildSessionFactory(String key, String path) {
        try {
            // Create the SessionFactory
            URL url = HibernateUtil.class.getResource(path);
            SessionFactory sessionFactory = new Configuration().configure(url).buildSessionFactory();
            sessionFactoryMap.put(key, sessionFactory);

        } catch(Throwable ex) {

            log.error("Initial SessionFactory creation failed.", ex);
            throw new ExceptionInInitializerError(ex);

        } // end of the try - catch block
    }

    public static void closeSession(String key) {
        HashMap<String, Session> sessionMaps = (HashMap<String, Session>) sessionMapsThreadLocal.get();
        if(sessionMaps != null) {
            Session session = sessionMaps.get(key);
            if(session != null && session.isOpen())
                session.close();
        }
    }

} // end of the class

http://www.java-forums.org/