How do I map composite primary key/foreign key

[Jay Abrahm]

I have the following table structure

Year - Master Table
YearId (PK)
YearNo

Manager - Master Table
ManagerId (PK)
ManagerName

AuditDepartments - Master Table
DepartmentId (PK)
DepartmentName

YearDepartment
YearDepartmentId (PK)
DepartmentId (FK)
YearId (FK)
YearDepartmentAuditName
YearDepartmentAuditComments

DepartmentMonths
ManagerId (PK/FK)
YearDepartmentId (PK/FK)
JanAudit
FebAudit
MarAudit
AprAudit

How do I map the composite primary key of DepartmentMonths to YearDepartment and Manager using annotations ?
Do I have to use @Embeddable annotation or is there another way to do this.

[Jay Abrahm]

It is working now but the primary key should have been a composite key of managerId & yearDepartmentId

I had to battle with it a bit but adding updatable = false, insertable = false fixed it.

@Id
@GeneratedValue
@Column(name="Manager_Id")
private int managerId;

@ManyToOne
@JoinColumns( { @JoinColumn(name = "Manager_Id", referencedColumnName = "Manager_Id", updatable = false, insertable = false) })
private Manager manager;

@Column(name="Year_Department_Id")
private int yearDepartmentId;

@ManyToOne
@JoinColumns( { @JoinColumn(name = "Year_Department_Id", referencedColumnName = "Year_Department_Id", updatable = false, insertable = false) })
private YearDepartmentId yearDepartmentId;

[Cameron Wallace McKenzie]

You'll need an annotation or something on yearDepartentId.

@Column(name="Year_Department_Id") 
private int yearDepartmentId; 

You can always use a compound key class. Here's an example where I have a copound key of userId and bankId

@Embeddable
public class CompoundKey implements 
                             java.io.Serializable{
  private Long userId;
  private Long bankId;
  public CompoundKey() {}
  public CompoundKey(Long user, Long bank) {
    userId = user;
    bankId = bank;
  }

  public Long getBankId() {return bankId;}
  public void setBankId(Long bankId) {
    this.bankId = bankId;
  }
  public Long getUserId() {return userId;}
  public void setUserId(Long userId) {
    this.userId = userId;
  }
}

Then in your code, you declare the @Id of this type:

package com.examscam.mappings;
import javax.persistence.*; import org.hibernate.Session;
import com.examscam.HibernateUtil;
@Entity
public class Interest {

  private CompoundKey id;
  private double rate;

  @Id
  public CompoundKey getId() {return id;}
  public void setId(CompoundKey id) {this.id=id;}
  public double getRate() {return rate;}
  public void setRate(double rate) {this.rate=rate;}

  
}

How To Use Compound Primary Keys In Your Code

Other options include using the @IdClass annotation or @EmbeddedId. This tutorial covers each of them. But that other field needs to be treated somehow.

[Jay Abrahm]

Thanks, the tutorial was very helpful. I was able to get the composite key setup.

I have a couple of questions though.
1. The tutorial does not show how list can be retrieved for a composite key instead of

Query query = sessionFactory.getCurrentSession().createQuery(
        "from DepartmentMonths departmentMonths where departmentMonths.id.managerId=? and departmentMonths.id.yearDepartmentId=? ");
        query.setInteger(0, 1695);
        query.setInteger(1, 1);
        return query.list();  

2. I have noticed @Id being used for the getter instead of the variable in many examples. Is there any advantage in doing this.

@Id
private DepartmentMonthsPK id;

@Embeddable
public class DepartmentMonthsPK implements Serializable{
	private static final long serialVersionUID = 3543236089104499992L;

	@Column(name="manager_Id")
	private int managerId;
	
	@Column(name="Year_Department_Id")
	private int yearDepartmentId;
	
	public DepartmentMonthsPK() {
	}
	
	public DepartmentMonthsPK(int managerId, int yearDepartmentId) {
//		super(); Do we need this call
		this.managerId = managerId;
		this.yearDepartmentId = yearDepartmentId;
	}
	
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + managerId;
		result = prime * result + yearDepartmentId;
		return result;
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		DepartmentMonthsPK other = (DepartmentMonthsPK) obj;
		if (managerId != other.managerId)
			return false;
		if (yearDepartmentId != other.yearDepartmentId)
			return false;
		return true;
	}
	
}

[Jay Abrahm]

Forget it, I figured out how to retrieve the hibernate objects from the composite key.

I had a question on how to display data which is a combination of YearDepartment and DepartmentMonths. DepartmentMonths is like a join table between YearDepartment and Manager which has additional data.

How do get data from a join table which has other attributes ?

The following post talks about it but I could see no solution

http://www.coderanch.com/t/218431/ORM/java/Hibernate-Annotations-many-many-association

[Jay Abrahm]

I was able to get the mappings done but I still need help with the query. I got help from the following URL Mapping Association tables with attributes

query = sessionFactory.getCurrentSession().createQuery(
"from YearDepartment yearDepartment where yearDepartment.departmentMonths.id.managerId=?");

I get the following exception
org.hibernate.QueryException: illegal attempt to dereference collection
How do I get just one record from the collection ?

YearDepartment
@OneToMany(fetch = FetchType.EAGER, mappedBy = "id.yearDepartment")
public List<DepartmentMonths> departmentMonths;

@Entity
@Table(name="Department_Months")
@AssociationOverrides({
@AssociationOverride(name = "id.managerId", joinColumns = @JoinColumn(name = "manager_Id")),
@AssociationOverride(name = "id.yearDepartmentId", joinColumns = @JoinColumn(name = "year_Department_Id"))
        })
DepartmentMonths

@Id
private DepartmentMonthsPK id;
	
@Transient
public int getManagerId() {
	return getId().getManagerId();
}

public void setManagerId(int managerId) {
	getId().setManagerId(managerId);
}

@Transient
public int getYearDepartmentId() {
	return getId().getYearDepartmentId();
}

public void setYearDepartmentId(int yearDepartmentId) {
	getId().setYearDepartmentId(yearDepartmentId);
}

DepartmentMonthsPK
@Column(name="manager_Id")
private int managerId;

@Column(name="year_Department_Id")
private int yearDepartmentId;