How to connect multiple database servers using Hibernate?

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.

Leave a Reply

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