How to connect multiple database servers using Hibernate?

You may also like to read:

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.

Advantages of Hibernate

You get many advantages out of the box from ORM like Hibernate framework, 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

Why need to connect 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’re developing a replacement application for a legacy system.

The legacy applications 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.

Generally if both files are present in the classpath, then hibernate.cfg.xml will override the hibernate.properties file.

One of these 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 and a Microsoft SQL Server database 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();

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 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.

How to connect multiple database servers using Hibernate?
I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger and writing blogs is my passion. Connect me on Roy Tutorials | Twitter | Facebook | Linkedin | Reddit | Ello | Tumblr

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top