I have 2 tables with different composite keys.
How do I perform a join between these tables, rather how do we
do this in the hbm file?

Can any one of you please share an example?
Any help would be appreciated.

Thanks

As far as Hibernate is concerned, a composite key may be handled as an assigned identifier of value type (the Hibernate type is a component). Suppose the primary
key of our user table consisted of a USERNAME and an ORGANIZATION_ID. We could add a property named organizationId to the User class and use the following
mapping:
<class name="User" table="USER">
<composite-id>
<key-property name="username"
column="USERNAME"/>
<key-property name="organizationId"
column="ORGANIZATION_ID"/>
</composite-id>
<version name="version"
column="VERSION"
unsaved-value="0"/>
...
</class>
The code to save a new User would look like this:
User user = new User();
// Assign a primary key value
user.setUsername("john");
user.setOrganizationId(42);
// Set property values
user.setFirstname("John");
user.setLastname("Doe");
session.saveOrUpdate(user); // will save, since version is 0
session.flush();
But what object could we use as the identifier when we called load() or get()? It’s possible to use an instance of the User; for example:
User user = new User();
// Assign a primary key value
user.setUsername("john");
user.setOrganizationId(42);
// Load the persistent state into user
session.load(User.class, user);

In this code snippet, User acts as its own identifier class. Note that we now have to implement Serializable and equals()/hashCode() for this class. It’s much more elegant to define a separate composite identifier class that declares just the key properties.
We call this class UserId:
public class UserId extends Serializable {
private String username;
private String organizationId;
public UserId(String username, String organizationId) {
this.username = username;
this.organizationId = organizationId;
}
// Getters...
public boolean equals(Object o) {
if (this == o) return true;
if (o = null) return false;
if (!(o instanceof UserId)) return false;
final UserId userId = (UserId) o;
if (!organizationId.equals(userId.getOrganizationId()))
return false;
if (!username.equals(userId.getUsername()))
return false;
return true;
}
public int hashCode() {
return username.hashCode();
)
}

It’s critical that we implement equals() and hashCode() correctly, since Hibernate uses these methods to do cache lookups. Composite key classes are also expected to implement Serializable.
Now, we’d remove the userName and organizationId properties from User and add a userId property. We’d use the following mapping:

<class name="User" table="USER">
<composite-id name="userId" class="UserId">
<key-property name="userName"
column="USERNAME"/>
<key-property name="organizationId"
column="ORGANIZATION_ID"/>
</composite-id>
<version name="version"
column="VERSION"
unsaved-value="0"/>
...
</class>
We could save a new instance using this code:
UserId id = new UserId("john", 42);
User user = new User();
// Assign a primary key value
user.setUserId(id);
// Set property values
user.setFirstname("John");
user.setLastname("Doe");
session.saveOrUpdate(user); // will save, since version is 0
session.flush();
The following code shows how to load an instance:
UserId id = new UserId("john", 42);
User user = (User) session.load(User.class, id);
Now, suppose the ORGANIZATION_ID was a foreign key to the ORGANIZATION table, and that we wished to represent this association in our Java model. Our recommended
way to do this is to use a <many-to-one> association mapped with insert="false"update="false", as follows:
<class name="User" table="USER">
<composite-id name="userId" class="UserId">
<key-property name="userName"
column="USERNAME"/>
<key-property name="organizationId"
column="ORGANIZATION_ID"/>
</composite-id>
<version name="version"
column="VERSION"
unsaved-value="0"/>
<many-to-one name="organization"
class="Organization"
column="ORGANIZATION_ID"
insert="false" update="false"/>
...
</class>
This use of insert="false" update="false" tells Hibernate to ignore that property
when updating or inserting a User, but we may of course read it with john.getOrganization().
An alternative approach is to use a <key-many-to-one>:
<class name="User" table="USER">
<composite-id name="userId" class="UserId">
<key-property name="userName"
column="USERNAME"/>
<key-many-to-one name="organization"
class="Organization"
column="ORGANIZATION_ID"/>
</composite-id>
<version name="version"
column="VERSION"
unsaved-value="0"/>
...
</class>
However, it’s usually inconvenient to have an association in a composite identifier class, so this approach isn’t recommended except in special circumstances.
Since USER has a composite primary key, any referencing foreign key is also composite.
For example, the association from Item to User (the seller) is now mapped to a composite foreign key. To our relief, Hibernate can hide this detail from the Java code. We can use the following association mapping for Item:
<many-to-one name="seller" class="User">
<column name="USERNAME"/>
<column name="ORGANIZATION_ID"/>
</many-to-one>
Any collection owned by the User class will also have a composite foreign key—for example, the inverse association, items, sold by this user:
<set name="items" lazy="true" inverse="true">
<key>
<column name="USERNAME"/>
Handling special kinds of data 337
<column name="ORGANIZATION_ID"/>
</key>
<one-to-many class="Item"/>
</set>
Note that the order in which columns are listed is significant and should match the order in which they appear inside the <composite-id> element.


Hope this helps...
P.S. do not forget the credits..

Hi aq12ws,

Thanks for your comments. However, how can we specify a join between the 2 tables without using the mapping that you have specified.

Using the join keyword, can we not do the join (provided the pk's of the 2 tables are different).

Thanks

Please reply ASAP if we can use the join keyword for this?

Thanks

In HQL, the theta-style syntax is useful when your join condition isn’t a foreign key relationship mapped to a class association. For example, suppose we store the User’s details in userdetails instead of mapping an association from userdetails to User. The classes don’t “know” anything about each other, because they aren’t associated.
We can then find all the Users and their details with the following theta-style join:
from User user, userdetails where user.username = userdetails.username
The join condition here is the username, presented as an attribute in both classes. If both entities have the same username, they’re joined (with an inner join) in the result. The query result consists of ordered pairs:
Iterator i = session.createQuery(
"from User user, UserDetails userdetails " +
"where user.username = userdetails.username"
)
.list().iterator();
while ( i.hasNext() ) {
Object[] pair = (Object[]) i.next();
User user = (User) pair[0];
UserDetails udet = (UserDetails ) pair[1];
}

You cannot do anything in the hbm.xml to do this kind of a thing ....
Hope this helps ...

Don't forget the credits ...

Hi aq12ws,

Thanks for sharing.

I have found that we can do something like this in the hbm file:

<join table="PO_ITEM_SHIP_WIN">
<key>
<column name="acs_cust_acct_cd" not-null="true"/>
<column name="PURCHASE_ORDER_NBR" />
<column name="PO_LI_SEQ_NBR" />
<column name="PO_ITM_SHP_SEQ_NBR" />
</key>

Regards