how to fetch data of remote side database in hibernate

Question

I developing a application of reporting tool.
I have to connect multiple remote side database with their credentials and fetching the database value.
How can I do this?
This is my hibernate configuration file

<hibernate-configuration>
  <session-factory>

    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

    <property name="show_sql">true</property>
   <property name="hibernate.hbm2ddl.auto">update</property>

    <property name="hibernate.connection.SetBigStringTryClob">true</property>

    <property name="hibernate.jdbc.batch_size">0</property>

  </session-factory>
</hibernate-configuration>

Answer 1

For connecting with multiple databases writing one session factory is not good. so how many databases if you want to connect writing that many configuration files is best way. For example assume, i want to connect oracle, mysql database. so what i can do is i will write one configuration file for oracle i.e oracle-configuration.cfg.xml and for mysql i will create mysql-configuration.cfg.xml

Now your question to connect with remote db, you need to give the remote address with the credentials.

for example, my remote database address is 192.168.0.14. now i want to connect oracle database now the connection properties like

oracle-configuration.cfg.xml

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

<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.username">ranga</property>
<property name="connection.password">ranga</property>
<property name="connection.url">jdbc:oracle:thin:@192.168.0.14:1521:XE</property>

<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>

<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>

<!-- Disable the second-level cache -->
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>

<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">create</property>

<mapping resource="com/ranga/mapping/Persom.hbm.xml"/>

</session-factory>
</hibernate-configuration> 

Answer 2

You should have separate session factory and data source configuration for each of your DBS and you could refer to the correct one e.g by data source name.

That means if you have 3 remote databases then ideally you should have 3 sessionFacories and datasources.

Example:

hibernate-first.cfg.xml - same as you had

<hibernate-configuration>
  <session-factory>

    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

    <property name="show_sql">true</property>
   <property name="hibernate.hbm2ddl.auto">update</property>

    <property name="hibernate.connection.SetBigStringTryClob">true</property>

    <property name="hibernate.jdbc.batch_size">0</property>

    <!-- Here commes the entities mapped to tables in the FIRST database -->
    <mapping class="com.yourpackage.EntityOftheFirstDatabase" />

  </session-factory>

hibernate-second.cfg.xml

<hibernate-configuration>
  <session-factory>

    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

    <property name="show_sql">true</property>
   <property name="hibernate.hbm2ddl.auto">update</property>

    <property name="hibernate.connection.SetBigStringTryClob">true</property>

    <property name="hibernate.jdbc.batch_size">0</property>

    <!-- Here commes the entities mapped to tables in the SECOND database -->
    <mapping class="com.yourpackage.EntityOftheSecondDatabase" />

  </session-factory>

Now lets configure the session factories in spring:

<!-- Hibernate SessionFactory for the FIRST database -->
    <bean id="sessionFactoryForTheFirstDatabase" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSourceForTheFirstDatabase" />
        <property name="configLocation" value="classpath:hibernate-first.cfg.xml" />
    </bean>



<!-- Hibernate SessionFactory for the SECOND database -->
        <bean id="sessionFactoryForTheSecondDatabase" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
            <property name="dataSource" ref="dataSourceForTheSecondDatabase" />
            <property name="configLocation" value="classpath:hibernate-second.cfg.xml" />
        </bean>

DataSources

<bean id="dataSourceForTheFirstDatabase" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="your specific" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
    </bean>

The second datasource is the very same just point it to the second database. If you want to configure the Tx management than you need two transaction managers for your databses:

<bean id="firstDbServiceProxyCreator" class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
        <description>Transaction boundaries are mapped to service layer</description>
        <property name="proxyTargetClass" value="false" />
        <property name="beanNames">
          <list>
        <value>ServicesMangingoperationsAboveYourFirstDatabase1</value>
        <value>ServicesMangingoperationsAboveYourFirstDatabase2</value>
          </list>
        </property>  
        <property name="interceptorNames">
          <list>
            <value>FirstDbTxInterceptor</value>
          </list>
        </property>
    </bean>

If you want to have single transaction accross multiple databases it is also possible, just dunno whether you need it or not! Hope it was enough clear for you. I know that there are more fancy ways to define the TX manager and the spring stuff mainly if you are using the newest one, i would sugges you to give it a try as I described and after that you could tweak it - google is your friend :)