Introduction
This tutorial we show you how to connect multiple database servers using Hibernate framework. We know Hibernate is an ORM (Object Relational Mapping) framework for the automated persistence of objects in a Java application to the tables in a relational database.
Here we will assume that an application will connect to MySQL and SQLServer databases at the same time. You may have different database servers where you need to establish connections using Hibernate ORM framework.
Advantages of Hibernate
ORM like Hibernate framework provides a number of significant advantages out of the box, such as, given below:
Improved Productivity
- High-level object-oriented API
- Less Java code to write
- No SQL to write
Improved Performance
- Sophisticated caching
- Lazy loading
- Eager loading
Improved Maintainability
- A lot less code to write
Improved Portability
- ORM framework generates database-specific SQL for you
Need for connecting to Multiple Databases
During the development of a complex application, you may need to connect to more than one database server. This requirement typically arises when you are developing a replacement application for a legacy system.
The legacy application has its own database servers, and you may have another one. The new application code must communicate effectively with all the servers.
Hibernate Configuration Files
When you instantiate a Configuration object, the system will, by default, attempt to find either a hibernate.properties or a hibernate.cfg.xml file in the application’s classpath directory (src/main/resources).
Generally if both files are present in the classpath, then hibernate.cfg.xml will override the hibernate.properties file.
One of these configuration files contains the database configuration information needed to contact a database server. To communicate with another database server, you need another hibernate configuration file similar to hibernate.cfg.xml.
For example, let’s say we have a MySQL database server and a Microsoft SQL database server that will be used in an application.
We will let the default classpath hibernate.cfg.xml file contain the connection and mapping information for the MySQL database. For the Microsoft SQL Server database, we will build another classpath configuration file called sqlserver.cfg.xml.
The configuration files for both servers look similar to the below files where we put the required connection details as well as other configuration details.
MySQL hibernate.cfg.xml
The typical hibernate configuration file (hibernate.cfg.xml) for MySQL database would look similar to the below file, where you mention your database dialect, connection details etc.
<?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.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/cdcol?zeroDateTimeBehavior=convertToNull</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
</session-factory>
</hibernate-configuration>
SQL server sqlserver.cfg.xml
The typical hibernate configuration file (sqlserver.cfg.xml) for SQL Server database would look similar to the below file, where you mention your database dialect, connection details etc.
<?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.SQLServerDialect</property>
<property name="hibernate.connection.driver_class">com.microsoft.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc.Microsoft://localhost/cdcol</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
</session-factory>
</hibernate-configuration>
Building SessionFactory
Now build SessionFactory
for both servers. We need to have singleton SessionFactory
class for each server.
Here you will see how to create SessionFactory
from the above hibernate configuration files for each server.
MySQL SessionFactory
Configuration configuration = new Configuration();
SessionFactory mysqlSessionFactory = configuration.buildSessionFactory();
Please make sure to check how to build SessionFactory
according to the Hibernate version.
SQL Server SessionFactory
configuration = new Configuration().configure("sglserver.cfg.xml");
SessionFactory sqlserverSessionFactory = configuration.buildSessionFactory();
Usage of SessionFactory
Now you can use the different SessionFactory
object to get short-lived single threaded Session
object for different servers.
You can execute HSQL or SQL on the different servers as per your requirements.
That’s all. Hope you got idea how to connect multiple database servers using Hibernate framework.
Thanks for reading.