Hi,
I was trying to map a primary table to a secondary table on a non-primary key columns. Hibernate out of the box does not allow it and the documentation says that a primary and secondary table can only be matched on a primary key of both tables. This restricts the mapping between the two tables to a one-to-one mapping, as the two unique keys are mapped to each other.
In some scenarios we need to map a one-to-many or many-to-many composite entity. I have a solution for this, this is a bit tricky and one should create such an entity by checking the uniqueness of the result and carefully analyzing the final sql query generated.

There are two tables :
Contact_Matter ( pk(contact_sid, matter_sid), role, info)
Contact (pk(contact_sid), first_name, last_name, creadted, created_by (etc..) )

The Contact_Matter table is a Many-to-many mapping table between Contact and Matter(table not shown in this example) with additional attributes such as role of a contact on a matter, info, etc.

Problem : Get a list of contacts associated with a matter along with roles based on a matter_sid.

Solution :
Usually one uses the Many-to-many join annotation to extract the list of contact entity by joining the Contact_matter table with Contact table.
This approach has two drawbacks :
1) We execute an additional select query to get the entity first and then get the result of the join.
2) We can not get the additional attributes of the join table such as role and info in one query.

The solution is to have a composite entity with a primary and secondary table and joining them on a non-primary key(hibernate expects a primary key join with another primary key join, we'll see how to get around that).

Here is the composite entity :


@Entity
@Table(name = "contact_matter")
@SecondaryTable(name = "contact", pkJoinColumns = {@PrimaryKeyJoinColumn(name = "contact_sid")})
public class ContactMatterCompositeEntity
{
@Id
private String contact_sid;

private String matter_sid;

private String role;

@Column(table = "contact")
private String first_name;

@Column(table = "contact")
private String last_name;

public String getContact_sid()
{
return contact_sid;
}

public String getFirst_name()
{
return first_name;
}

public String getLast_name()
{
return last_name;
}

public String getMatter_sid()
{
return matter_sid;
}

public void setContact_sid(final String contact_sid)
{
this.contact_sid = contact_sid;
}

public void setFirst_name(final String first_name)
{
this.first_name = first_name;
}

public void setLast_name(final String last_name)
{
this.last_name = last_name;
}

public void setMatter_sid(final String matter_sid)
{
this.matter_sid = matter_sid;
}

public String getRole()
{
return this.role;
}

public void setRole(final String role)
{
this.role=role;
}

}


Here is the contact entity :

@Entity
@org.hibernate.annotations.Entity
@Table(name = "CONTACT")
public class ContactEntity
{
@Id
@Column(name = "CONTACT_SID", nullable = false)
private String id;


@Column(name = "FIRST_NAME", nullable = true)
private String firstName;


@Column(name = "LAST_NAME", nullable = true)
private String lastName;

public String getId()
{
return id;
}

public void setId(final String contactSID)
{
this.id = contactSID;
}

public String getFirstName()
{
return firstName;
}

public void setFirstName(final String firstName)
{
this.firstName = firstName;
}

public String getLastName()
{
return lastName;
}

public void setLastName(final String lastName)
{
this.lastName = lastName;
}
}


If you see closely the primary key for the Contact_Matter table is (contact_sid, matter_sid) as it is the many-to-many mapping table of Contact And Matter tables. We want a join between Contact_Matter and Contact tables based on the contact_sid for a given matter_sid. By default hibernate will do a join between the two tables on their primary keys and we will get a null result, as the keys will not match. What is done in this example is to use the attribute from the Contact_matter primary table on which we want to do the join with the Contact secondary table as the @id attribute and assign that annotation to it.
This way we tell hibernate to generate a sql query joining those two tables and retrieving their attributes based on the join on the contact_sid column. The contact_sid is the primary key for the Contact table, but it is a non-primary key for the Contact_matter table. This solution will work on any Many-to-many mapping between two tables. To reverse the selection, i.e getting all the matters for a contact, use the Contact_Matter as the primary table and use the Matter table as the secondary table and use the matter_sid as the id of that composite entity. This works because in the subset of the rows with same matter_sid, contact_sid will be unique and vice versa, so there will be no duplicates.

The benefits of this approach is:
1) Only one query is executed to get the result
2) No additional queries to load the parent entity.
3) Only one entity is involved with no Set or List attributes representing a join result
4) No additional queries/ logic required to retrieve attributes from the many-to-many join table such as the role or info attributes.

Below is the code to get the list of contacts for a given matter_sid with the associated role :


public List<ContactMatterCompositeEntity> getContactMatterCompositeEntity(final String matterId)
{
Criteria criteria = this.getSessionFactory().getCurrentSession().createCriteria(ContactMatterCompositeEntity.class);
criteria.add(Restrictions.eq("matter_sid", matterId));
return criteria.list();
}



The Associated SQL Query :

select
this_.contact_sid as contact1_2_0_,
this_.matter_sid as matter2_2_0_,
this_1_.first_name as first1_3_0_,
this_1_.last_name as last2_3_0_
from
contact_matter this_
left outer join
contact this_1_
on this_.contact_sid=this_1_.contact_sid
where
this_.matter_sid=?

Thanks,
Karan