• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Joining three tables with Hibernate mappings when key column names don't match

 
K Conway
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I'm new to Java and Hibernate. My first assignment is to display information about recent activities in a portlet. I have all of this working except one piece where I need to display a client's name next to the recent activity. That client's name comes from two tables away; CLIENT related to POLICY related to RECENT_ACTIVITY.

One caveat: Our portal server is one generation back, (soon to be upgraded) but doesn't currently support annotations, so I have to do all of this in XML mappings.

Also, our DB Admin is a pain, insisting on his own naming conventions.

The first table is RECENT_ACTIVITY. I've created the class and XML mapping, and it all works both with persisting and querying. It has a calendar timestamp as the ID, contains a few fields telling what the recent activity was, and a PACL_POL_ID column (the policy ID) that the activity was on. This table has a many-to-one relation with the POLICY table: For each RECENT_ACTIVITY, there is one and only one POLICY, a POLICY can have zero-to-many RECENT ACTIVITIES.

The next table is POLICY. I've created a class and XML mapping for that, and have gotten it included as an object in the RecentActivity class. The POLICY table uses PCT_POL_ID as the ID column (notice a different name from the RECENT_ACTIVITY table), and includes about 50 fields. One of those fields is the PCT_HO_CLI_ID (Client ID). This relates to the CLIENT table. For each POLICY, there is one and only one client; a CLIENT can have from zero to many POLICIES.

I have also created a class and XML mapping for the CLIENT table. The key for this table is PCL_HO_CLI_ID (again, same data but different name from the POLICY table). This has also been included as an object in the Policy class, and is therefore can be accessed from the RecentActivity class. All I need out of the CLIENT table is firstName, lastName, middleInit, and suffix.

This seems like a lot of data to serialize and send across the Web service.

What I really need is something like SQL would return.
SELECT RECENT_ACTIVITY.*, CLIENT.FirstName, CLIENT.LastName, CLIENT.MiddleInit, CLIENT.Suffix from RECENT_ACTIVITY inner join POLICY on RECENT_ACTIVITY.PACL_POL_ID = POLICY.PCT_POL_ID inner join CLIENT on CLIENT.PCL_HO_CLI_ID = POLICY.PCT_HO_CLI_ID where RECENT_ACTIVITY.PACL_ID_TS < (TODAY() - 30) AND RECENT_ACTIVITY.USERID = currentUserId

How can I map my object to include the four name pieces from CLIENT (firstName, lastName, middleInit, suffix) so that I don't have to include the entire POLICY object and entire CLIENT object serialized through the web service?

Any help would be appreciated.

Thanks in Advance

Kent
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic