Hibernate with Multiple Databases

In single hibernate application we are able to interact with more than one Database, but, we must use the following conventions.

1. We must provide separate configuration file for each and every Database.
2. Use either single mapping file if we have same table name and same columns in both the databases tables and use different mapping file for each and every database if we have difference in table names and column names.
3. In Client application.

• Prepare separate Configuration object for each and every DB.
• Prepare separate SessionFactory object for each and every DB.
• Prepare separate Session object for each and every DB.
• Prepare separate Transaction object for each and every DB as per the requirement.
• Perform persistence operations on DB respective Session objects.

Employee.java

package com.ashok.hibernate.multipledatabase.model;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Employee {
	private String empId;
	private String empName;
	private String address;
	private double salary;
	
	public Employee() {
		super();
	}
	
	public String getEmpId() {
		return empId;
	}

	public void setEmpId(String empId) {
		this.empId = empId;
	}
	
	public String getEmpName() {
		return empName;
	}

	public void setEmpName(String empName) {
		this.empName = empName;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}
}

employee.hbm.xml

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
	<class name="com.ashok.hibernate.multipledatabase.model.Employee" table="emp">
		<id name="empId" column="emp_id" type="string" />
		<property name="empName" column="emp_name" type="string" />
		<property name="address" column="address" type="string" />
		<property name="salary" column="salary" type="double" />
	</class>
</hibernate-mapping>

hibernate_oracle.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://www.hibernate.org/dtd/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:@localhost:1521:xe</property>
      <property name="hibernate.connection.username">system</property>
      <property name="hibernate.connection.password">ashok</property>

      <mapping resource="com/ashok/hibernate/multipledatabase/employee.hbm.xml"/>
   </session-factory>
</hibernate-configuration>

hibernate_mysql.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
   <session-factory>
      <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
      <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
      <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/employee</property>
      <property name="hibernate.connection.username">root</property>
      <property name="hibernate.connection.password">ashok</property>

      <mapping resource="com/ashok/hibernate/multipledatabase/employee.hbm.xml"/>
   </session-factory>
</hibernate-configuration>

ClientApp.java

package com.ashok.hibernate.multipledatabase;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.ashok.hibernate.insert.model.Employee;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class ClientApp {
	public static void main(String[] args) throws Exception {
		SessionFactory oracleSessionFactory = null;
		SessionFactory mySqlSessionFactory = null;
		Session oracleSession = null;
		Session mysqlSession = null;
		try {
			Configuration oracleCfg = new Configuration();
			Configuration mysqlCfg = new Configuration();
			oracleCfg.configure("/com/ashok/hibernate/multipledatabase/hibernate_oracle.cfg.xml");
			mysqlCfg.configure("/com/ashok/hibernate/multipledatabase/hibernate_mysql.cfg.xml");
			
			oracleSessionFactory = oracleCfg.buildSessionFactory();
			mySqlSessionFactory = mysqlCfg.buildSessionFactory();
			
			oracleSession = oracleSessionFactory.openSession();
			mysqlSession = mySqlSessionFactory.openSession();
			
			Employee emp = oracleSession.get(Employee.class, "E0087");
			System.out.println("Employee retrived from Oracle Database");
			
			Transaction tx = mysqlSession.beginTransaction();
			mysqlSession.save(emp);
			tx.commit();
			System.out.println("Employee Inserted in MySQL Database");
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != oracleSession) {
				oracleSession.close();
			}
			if(null != mysqlSession) {
				mysqlSession.close();
			}
			if(null != oracleSessionFactory) {
				oracleSessionFactory.close();
			}
			if(null != mySqlSessionFactory) {
				mySqlSessionFactory.close();
			}
		}
	}
}

Hope this tutorial will help you.

Reference : https://www.waytoeasylearn.com/learn/hibernate-with-multiple-databases/