Loading a single POJO from two databases {newbieQ}

drekka · **Joined:** Thu Mar 30, 2006 9:21 pm **Posts:** 11

Hibernate version: 3.1.1

Hi all,
I'm new to hibernate and have a perculiar situation. I am creating a database that sites to the side of another system without maintaining a direct link. Therefore the datastructures within it have fields which are effectively foreign keys to this other database.

In my POJOs and Hibernate I am trying to figure out how to load the POJOs with data from two different databases. In other words a POJO may contain fields from two different tables which reside in different databases with the id field being the only common data. The best I have come up with so far is to query one for a list of items, then loop through them and use .refresh(Object) to load the second block of data from the second database. Here's some sample code:

Code:

public void load() {

   //Start transactions.
   this.lpisession.beginTransaction();
   this.commissionsession.beginTransaction();

   //Set a calendar to be the start of the year.
   Calendar cal = Calendar.getInstance();
   cal.clear();
   cal.set(2006,1,1,0,0,0);
   
   // get some data from the database to prove that hibernate is working.
   List<Order> results = 
      (List <Order>) this.lpisession.createCriteria(Order.class)
      .add(Restrictions.ge("entryDt", cal.getTime()))
      .addOrder(org.hibernate.criterion.Order.asc("orderId"))
      .list();

      System.out.println("List class is an instance of " + results.getClass().getName());

      for (Order o : results) {
         System.out.println(o.getAccountId() + " => " + o.getOrderId());
         try {
            //Now load the rest of the object fields from the other database.
            this.commissionsession.refresh(o);
         }
         catch (UnresolvableObjectException uro) {
            //Do nothing - we are not concerned about missing rows in
            //the commission database as these are normal.
         }

         System.out.println("\tOrderId   = " + o.getOrderId());
         System.out.println("\tAccountId = " + o.getAccountId());
         System.out.println("\tCurrency  = " + o.getCurrency());
         System.out.println("\tStatus    = " + o.getStatusId());
      }

      this.lpisession.getTransaction().rollback();
      this.commissionsession.getTransaction().rollback();

}

The following is the lpi hbm file for Order:

Code:

<hibernate-mapping>
   <class name="com.lp.commissions.Order" table="order_header" mutable="false">
      <cache usage="read-only" />
      <id name="orderId" column="order_id">
         <generator class="native" />
      </id>
      <property name="accountId" type="int" column="account_id" />
      <property name="currency" type="string" column="currency" />
      <property name="entryDt" type="timestamp" column="entry_dt" />
   </class>
</hibernate-mapping>

And here is the mapping for the commissions Orders table:

Code:

<hibernate-mapping>
   <class name="com.lp.commissions.Order" table="orders">
      <id name="orderId" column="lpi_order_id">
         <generator class="native" />
      </id>
      <property name="statusId" type="int" column="status_id" />
   </class>
</hibernate-mapping>

The only thing that I would like to change is to be able to bulk load based on a collection of objects. I cannot see anyway to do this other than what I have done.

Have I missed something or is there a better way to do this ?

Thanks
Derek

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Many DBMSs allow for linked servers: that is, one server proxies another, so "select * from linkedserver.dbo.Table" gets your current server to fetch rows from a remote server, then returns them to you. If you can make use of that, then you can use a single connection, single session factory, even a single session to get data from both servers.

If that's not feasible, then your current plan is fine. Proper encapsulation of your own data access objects and factories can make this transparent, but you're still going to need two session factories, and thus two steps in fully loading a distributed object. If there's no need to have all properties loaded all of the time, I'd recommend leaving it to business logic to decide when to load from the secondary database, and to ram this fact home by implementing the logical object as two POJOs. So you'd have DAO.getOrder(id), and that returns an object that includes a getCommissionOrder() method that all the 2ndary database's fields are in.