Hibernate and multiple Database connection

Tips:1

Using spring, this becomes MUCH easier

Using annotation mappings as an example: Configuration cfg1 = new AnnotationConfiguration(); cfg1.configure("/hibernate-oracle.cfg.xml"); cfg1.addAnnotatedClass(SomeClass.class); // mapped classes cfg1.addAnnotatedClass(SomeOtherClass.class); SessionFactory sf1 = cfg1.buildSessionFactory();   Configuration cfg2 = new AnnotationConfiguration(); cfg2.configure("/hibernate-mysql.cfg.xml"); cfg2.addAnnotatedClass(SomeClass.class); // could be the same or different than above cfg2.addAnnotatedClass(SomeOtherClass.class); SessionFactory sf2 = cfg2.buildSessionFactory(); Then use sf1 and sf2 to get the sessions for each database. For mapping files, you just use cfg.addClass instead of addAnnotatedClass. Put the cfg.xml files in the root package in this case. Those will have the Oracle or MySQL dialect and connection information.

 

Tips:2

Using Database.properties file

Database.properties file original

database.password=<password>

database.url=jdbc\:mysql\://localhost\:3306/twodb

database.username=<username>

database.driverClassName=com.mysql.jdbc.Driver

Database.properties file modified

database.password1=<password1>

database.url1=jdbc\:mysql\://localhost\:3306/twodbone

database.username1=<username1>

database.password2=<password1>

database.url2=jdbc\:mysql\://localhost\:3306/twodbtwo

database.username2=<username2>

database.driverClassName=com.mysql.jdbc.Driver

Persistence.xml file modified

<........name="persistenceUnit1" transaction-type="RESOURCE_LOCAL">

---------

<........name="persistenceUnit2" transaction-type="RESOURCE_LOCAL">

Note: duplicated the persistence unit code for the 2nd database and define unique ids.

ApplicationContext.xml file modified

<bean..........destroy-method="close" id="dataSource2">

      <property name="driverClassName" value="${database.driverClassName}"/>

      <property name="url" value="${database.url2}"/>

      <property name="username" value="${database.username2}"/>

      <property name="password" value="${database.password2}"/>

Web.xml file modified

<filter>

<filter-name>Spring OpenEntityManagerInViewFilter1</filter-name>

      <filter-class>

.............

</param-value>

      </init-param>

</filter>

 

 

Tips: 3

Configuring Multiple Databases in Hibernate 3.0

hibernate.cfg.xml

·         Databases are configured in an XML file called hibernate.cfg.xml.

·         There are many configuration parameters available that makes the mapping of domain model to relational model easier.

·         The same configurations can be done from your Java class uning org.hibernate.cfg.Configuration class.

The mapping details of the Employee class are available in the Employee.hbm.xml file.

To persist, the Employee object in 2 databases, we need to create 2 configuration files.

 

oracleconfigOne.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>

<property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>

<property name="hibernate.connection.url"> jdbc:oracle:thin:@10.154.117.76:1521:oracleONE</property>

<property name="hibernate.connection.username">user</property>

<property name="hibernate.connection.password">password</property>

<property name="hibernate.hbm2ddl.auto">create</property>

<property name="hibernate.show_sql">true</property>

<mapping resource="Employee.hbm.xml" />

</session-factory>

</hibernate-configuration>

 

oracleconfigtwo.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>

<property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>

<property name="hibernate.connection.url"> jdbc:oracle:thin:@10.154.117.76:1521:oracleTWO</property>

<property name="hibernate.connection.username">user</property>

<property name="hibernate.connection.password">password</property>

<property name="hibernate.hbm2ddl.auto">create</property>

<property name="hibernate.show_sql">true</property>

<mapping resource="Employee.hbm.xml" />

</session-factory>

</hibernate-configuration>

 

IEmployeeDAO.java

package hibernatepack.samples;

import java.util.List;

public interface IEmployeeDAO {

public void findAllEmployees();

public void insertEmployee(Employee e);

}

 

EmloyeeDaoImpl.java

public class EmployeeDaoImpl implements IEmployeeDAO {

SessionFactory sessionFactory1 = new  Configuration().configure("oracleconfigONE.cfg.xml").buildSessionFactory();

SessionFactory sessionFactory2 = new Configuration().configure("oracleconfigTWO.cfg.xml").buildSessionFactory();

Session session = null;

Transaction transaction = null;

public void findAllEmployees() {

ArrayList empList = new ArrayList();

try {

session = sessionFactory1.openSession();

transaction = session.beginTransaction();

transaction.begin();

Criteria crit = session.createCriteria(Employee.class);

empList = (ArrayList) crit.list();

System.out.println("Records from Oracle Database");

for (Employee emp : empList) {

System.out.println(emp.getEmpid() + " " + emp.getEmpname() + " " + emp.getSalary());

}

session.close();

session = sessionFactory2.openSession();

Criteria crit1 = session.createCriteria(Employee.class);

empList = (ArrayList) crit1.list();

System.out.println("Records from Derby Database");

for (Employee emp : empList) {

System.out.println(emp.getEmpid() + " " + emp.getEmpname() + " " + emp.getSalary());

}

session.close();

} catch (Exception he) {

he.printStackTrace();

}

}

public void insertEmployee(Employee e) {

try {

session = sessionFactory1.openSession();

transaction = session.beginTransaction();

transaction.begin();

session.save(e);

transaction.commit();

session.close();

session = sessionFactory2.openSession();

transaction = session.beginTransaction();

transaction.begin();

session.save(e);

transaction.commit();

session.close();

} catch (HibernateException he) {

he.printStackTrace();

}

}

}

Tips: 4

Multiple Databases for Different Clients

One session factory per client, created dynamically when users log in

create multiple SessionFactory (one per database)

·         Use some unique Map<SomeKey, SessionFactory>

·         If a SessionFactory hasn't been created yet, build it and store it in the map.

PUZZEL: Create Connection object and use method sessionFactory.openSession(connection); to get hibernate session, it’s correct?

A Hibernate SessionFactory can only handle one DataSource at a time, and generally speaking each DataSource refers to one and only one database. So if you need multiple databases, then the simplest solution is almost certainly multiple SessionFactory instances.

·         There should be different cfg.xml files for different databases

·         Then simply use Configuration object of the Hibernate whenever you want to connect to your second database.

Configuration config = new Configuration().configure("<complete path to your cfg.xml file>");

SessionFactory sessionFactory = config.buildSessionFactory();

Session session = sessionFactory.getCurrentSession();

session.beginTransaction();

 

Use the @Qualifier Annotation on the method that injects the sessionFactory:

 

@Autowired

public ProgramaDAOHibernate(@Qualifier("sessionFactory2") SessionFactory sessionFactory) {

super(sessionFactory);

}

 

 

Tips: 5

Configuring Hibernate to use multiple database schemas

·        An application that manages several clients (or customer if you prefer)

·        Dedicated schema on a database for each clients

·        Application handles only one client at a time, i.e. the user must switch from one client to another in the application (at runtime, no restart of the application) in order to access the data from this new client.

 

Tips: 6

Spring has “AbstractRoutingDataSource”

(Refer:  http://blog.springsource.com/2007/01/23/dynamic-datasource-routing/)

In your case, you need to switch the datasource during one request, so you'll need to have 2 transactions, switching the datasource between them by changing the datasource indicator on the ThreadLocal:

1. For these DAOs, demarcate the wrapping Service-layer either with distinct packages, class names, or method names
2. Indicate to Spring that the Service-layer method calls should run in their own transactional contexts by annotating with @Transactional(propogation=Propogation.REQUIRES_NEW)
3. Create an Aspect (using AspectJ annotation @Aspect) to fire around the service-layer method calls (using @Around) to set the ThreadLocal value before the method call, and to unset it afterwards
4. In the @Controller, simply call the Service-layer methods. The Aspect will take care of setting the values to indicate which datasource to use, and the AbstractRoutingDataSource will use that datasource in the context of each transaction.

Multi-tenancy in Hibernate (supported by Spring 3.1)