composite PK/FK

[danjourno]

I am trying to build a entity structure where I have a country table that uses ISO 3166 country codes as their PK, and a region table which also uses ISO3166 codes as a composite PK. For example.. a record in the country_region table that refers to New South Wales in Australia will have a primary key of:

country_code_1 = AU (FK value for country table)
region_code = NS


So in summary I have a CountryRegion table that has a many(regions) to one(country) relationship with a Country table. The key for the CountryRegion table is a composite of the country code and the region code.

I have referred to the wiki for this but am still a bit stuck on how to make this work.

I'll paste my code below.. If anyone could please look over an give me pointers as to where I'm going wrong I would be very grateful.

I have a Country entity, a CountryRegion entity, and an embeddable PK object called CountryRegion


The CountryRegion entity:

import java.io.Serializable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;

import javax.persistence.*;

//import java.util.List;

@Entity
@Table(name = "country_region")
public class CountryRegion implements Serializable {

 java.lang.String name;

 java.lang.String description;

 CountryRegionPK pk;

 public CountryRegion() {
 }

 @EmbeddedId
 @AttributeOverrides({
 @AttributeOverride(name = "regionCode", column = @Column(name = "region_code")),
 @AttributeOverride(name = "country", column = @Column(name="country_code_1"))
 })
 public CountryRegionPK getPk() {
 return pk;
 }

 public void setPk(CountryRegionPK pk) {
 this.pk = pk;
 }

 @Column(name = "description")
 public java.lang.String getDescription() {
 return description;
 }

 public void setDescription(java.lang.String description) {
 this.description = description;
 }

 @Column(name = "name")
 public java.lang.String getName() {
 return name;
 }

 public void setName(java.lang.String name) {
 this.name = name;
 }

}

The Country entity:

import java.io.Serializable;

import javax.persistence.*;

@Entity
@Table(name="country")
public class Country implements Serializable {

 java.lang.String countryCode1;

 java.util.List<Location> locations;

 java.util.List<CountryRegion> countryRegion;

 @Id
 @Column(name="country_code_1")
 public java.lang.String getCountryCode1() {
 return countryCode1;
 }

 public void setCountryCode1(java.lang.String countryCode1) {
 this.countryCode1 = countryCode1;
 }


 @OneToMany(fetch=FetchType.LAZY,mappedBy="country")
 public java.util.List<Location> getLocations() {
 return locations;
 }

 public void setLocations(java.util.List<Location> locations) {
 this.locations = locations;
 }

 @OneToMany(fetch=FetchType.LAZY,mappedBy="country")
 public java.util.List<CountryRegion> getCountryRegion() {
 return countryRegion;
 }

 public void setCountryRegion(java.util.List<CountryRegion> countryRegion) {
 this.countryRegion = countryRegion;
 }




}

The Embedded primary key object:

import javax.persistence.Embeddable;


@Embeddable
public class CountryRegionPK implements java.io.Serializable{



 private Country country;
 private String regionCode;

 public CountryRegionPK() {
 super();
 // TODO Auto-generated constructor stub
 }

 public CountryRegionPK(String regionCode, Country country)
 {
 this.regionCode = regionCode;
 this.country = country;
 }

 public Country getCountry()
 {
 return country;
 }

 public void setCountry(Country country)
 {
 this.country = country;
 }

 public String getRegionCode()
 {
 return regionCode;
 }

 public void setRegionCode(String regionCode)
 {
 this.regionCode = regionCode;
 }

 public int hashCode()
 {
 return (int) country.hashCode() + regionCode.hashCode();
 }

 public boolean equals(Object obj)
 {
 if (obj == this) return true;
 if (!(obj instanceof CountryRegionPK)) return false;
 if (obj == null) return false;
 CountryRegionPK pk = (CountryRegionPK) obj;
 return pk.country.equals(country) && pk.regionCode.equals(regionCode);
 }

An example of a referring entity (Location)

import java.io.Serializable;

import javax.persistence.*;

@Entity
@Table(name="location")
public class Location implements Serializable{


 java.lang.Integer locationId;

 java.lang.String city;

 java.lang.String postalCode;

 CountryRegion countryRegion;

 Country country;

 java.util.Date creationDate;

 @Column(name="creation_date",nullable=false,updatable=false)
 public java.util.Date getCreationDate() {
 return creationDate;
 }

 public void setCreationDate(java.util.Date creationDate) {
 this.creationDate = creationDate;
 }

 @Id(generate = GeneratorType.AUTO)
 @Column(name="location_id")
 public java.lang.Integer getLocationId() {
 return locationId;
 }

 public void setLocationId(java.lang.Integer locationId) {
 this.locationId = locationId;
 }

 @Column(name="city")
 public java.lang.String getCity() {
 return city;
 }

 public void setCity(java.lang.String city) {
 this.city = city;
 }

 @ManyToOne
 @JoinColumn(nullable=false, name="country_code_1")
 public Country getCountry() {
 return country;
 }

 public void setCountry(Country country) {
 this.country = country;
 }

 @ManyToOne(fetch=FetchType.EAGER)
 @JoinColumns ( {@JoinColumn(nullable=false, name="region_code"),
 @JoinColumn(nullable=false, name="country_code_1")})
 public CountryRegion getCountryRegion() {
 return countryRegion;
 }

 public void setCountryRegion(CountryRegion countryRegion) {
 this.countryRegion = countryRegion;
 }


 @Column(name="postal_code")
 public java.lang.String getPostalCode() {
 return postalCode;
 }

 public void setPostalCode(java.lang.String postalCode) {
 this.postalCode = postalCode;
 }

}

[bill.burke]

only Basic, Column, Lob, Temporal, and Enumerated can be used to map a @Embedded class. Thus, you can't use Country as a primary key. That's one problem I see. Another is that the @OneToMany on Country to CountryRegion is not bidirectional, so the mappedby would not work. You'd have to do:

@Entity public class Country {
...
 @OneToMany
 @JoinColumn(name="country_code_1")
 List<CountryRegion> getCountryRegions() {...}

There's probably other stuff wrong, but get those to work first

[danjourno]

Thanks Bill,

I'll give that a go an let you know of the results.

[danjourno]

I've just set things up the way you have suggested.

My PK class now has a String value referring to the country_code_1 rather than the actual country object.

@Embeddable
public class CountryRegionPK implements java.io.Serializable{
.......

 public String getCountryCode1() {...}

 public void setCountryCode1(String countryCode1) {.... }

@Entity
@Table(name = "country_region")
public class CountryRegion implements Serializable {
........
 @EmbeddedId
 @AttributeOverrides({
 @AttributeOverride(name = "regionCode", column = @Column(name = "region_code")),
 @AttributeOverride(name = "countryCode1", column = @Column(name="country_code_1"))
 })

However I am still getting the error below. I'm guessing that it is due to my Location table relationship. see bottom.

org.hibernate.AnnotationException: A Foreign key refering CountryRegion has the wrong number of column. should be 2

@Entity
@Table(name="location")
public class Location implements Serializable{
...........
 @ManyToOne
 @JoinColumns ( {@JoinColumn(nullable=false, name="region_code"), @JoinColumn(nullable=false, name="country_code_1")})
 public CountryRegion getCountryRegion() {
 return countryRegion;
 }

[epbernard]

did you remove this one

@OneToMany(fetch=FetchType.LAZY,mappedBy="country")
 public java.util.List<CountryRegion> getCountryRegion() {
 return countryRegion;
 }

[danjourno]

yep.. I finally sorted this one out.

I'll post the code for my files for a reference as this is a slightly different example to the one in the wiki.

I've included the location class which shows how the composite foreign key is referenced.

@Entity
@Table(name="country")
public class Country implements Serializable {

 java.lang.String countryCode1;

 java.lang.String countryCode2;

 java.lang.String shortName;

 java.lang.String longName;

 java.lang.String countryCode3;

 java.util.List<CountryRegion> countryRegions;

 @Id
 @Column(name="country_code_1")
 public java.lang.String getCountryCode1() {
 return countryCode1;
 }

 public void setCountryCode1(java.lang.String countryCode1) {
 this.countryCode1 = countryCode1;
 }

 @Column(name="country_code_2")
 public java.lang.String getCountryCode2() {
 return countryCode2;
 }


 public void setCountryCode2(java.lang.String countryCode2) {
 this.countryCode2 = countryCode2;
 }

 @Column(name="country_code_3")
 public java.lang.String getCountryCode3() {
 return countryCode3;
 }

 public void setCountryCode3(java.lang.String countryCode3) {
 this.countryCode3 = countryCode3;
 }

 @Column(name="long_name")
 public java.lang.String getLongName() {
 return longName;
 }

 public void setLongName(java.lang.String longName) {
 this.longName = longName;
 }

 @Column(name="short_name")
 public java.lang.String getShortName() {
 return shortName;
 }

 public void setShortName(java.lang.String shortName) {
 this.shortName = shortName;
 }



 @OneToMany(mappedBy="country")
 public java.util.List<CountryRegion> getCountryRegions() {
 return countryRegions;
 }

 public void setCountryRegions(java.util.List<CountryRegion> countryRegions) {
 this.countryRegions = countryRegions;
 }



}

@Entity
@Table(name = "country_region")
public class CountryRegion implements Serializable {

 java.lang.String name;

 java.lang.String description;

 CountryRegionPK pk;

 Country country;

 java.util.List<Location> locations;

 java.util.List<Company> companies;

 public CountryRegion() {
 }

 // mapping of table columns to PK fields
 // note that the length had to be set so that PK size didn't exceed MySQLs 1024 byte max
 @EmbeddedId
 @AttributeOverrides({
 @AttributeOverride(name = "regionCode", column = @Column(name = "region_code", length=3)),
 @AttributeOverride(name = "countryCode1", column = @Column(name="country_code_1", length=2))
 })
 public CountryRegionPK getPk() {
 return pk;
 }

 public void setPk(CountryRegionPK pk) {
 this.pk = pk;
 }

 @Column(name = "description")
 public java.lang.String getDescription() {
 return description;
 }

 public void setDescription(java.lang.String description) {
 this.description = description;
 }

 @Column(name = "name")
 public java.lang.String getName() {
 return name;
 }

 public void setName(java.lang.String name) {
 this.name = name;
 }

 // must use insertable=false and updateable=false when referencing country_code_1 for the second time.
 @ManyToOne(fetch=FetchType.EAGER)
 @JoinColumn(name="country_code_1", insertable=false, updatable=false)
 public Country getCountry() {
 return country;
 }

 public void setCountry(Country country) {
 this.country = country;
 }

 @OneToMany(fetch=FetchType.LAZY,mappedBy="countryRegion")
 public java.util.List<Company> getCompanies() {
 return companies;
 }

 public void setCompanies(java.util.List<Company> companies) {
 this.companies = companies;
 }

 @OneToMany(fetch=FetchType.LAZY,mappedBy="countryRegion")
 public java.util.List<Location> getLocations() {
 return locations;
 }

 public void setLocations(java.util.List<Location> locations) {
 this.locations = locations;
 }

}

@Embeddable
public class CountryRegionPK implements java.io.Serializable{



 private String countryCode1;
 private String regionCode;

 public CountryRegionPK() {
 super();
 // TODO Auto-generated constructor stub
 }

 public CountryRegionPK(String regionCode, String countryCode1)
 {
 this.regionCode = regionCode;
 this.countryCode1 = countryCode1;
 }

 public String getCountryCode1()
 {
 return countryCode1;
 }

 public void setCountryCode1(String countryCode1)
 {
 this.countryCode1 = countryCode1;
 }

 public String getRegionCode()
 {
 return regionCode;
 }

 public void setRegionCode(String regionCode)
 {
 this.regionCode = regionCode;
 }

 public int hashCode()
 {
 return (int) countryCode1.hashCode() + regionCode.hashCode();
 }

 public boolean equals(Object obj)
 {
 if (obj == this) return true;
 if (!(obj instanceof CountryRegionPK)) return false;
 if (obj == null) return false;
 CountryRegionPK pk = (CountryRegionPK) obj;
 return pk.countryCode1.equals(countryCode1) && pk.regionCode.equals(regionCode);
 }
}

@Entity
@Table(name="location")
public class Location implements Serializable{


 java.lang.Integer locationId;

 java.lang.String city;

 java.lang.String postalCode;

 CountryRegion countryRegion;

 java.util.Date creationDate;

 @Column(name="creation_date",nullable=false,updatable=false)
 public java.util.Date getCreationDate() {
 return creationDate;
 }

 public void setCreationDate(java.util.Date creationDate) {
 this.creationDate = creationDate;
 }

 @Id(generate = GeneratorType.AUTO)
 @Column(name="location_id")
 public java.lang.Integer getLocationId() {
 return locationId;
 }

 public void setLocationId(java.lang.Integer locationId) {
 this.locationId = locationId;
 }

 @Column(name="city")
 public java.lang.String getCity() {
 return city;
 }

 public void setCity(java.lang.String city) {
 this.city = city;
 }


 // country_region table is mapped by multiple columns.
 
@ManyToOne
 @JoinColumns ( {@JoinColumn(nullable=false, name="region_code"),
 @JoinColumn(nullable=false, name="country_code_1")})
 public CountryRegion getCountryRegion() {
 return countryRegion;
 }

 public void setCountryRegion(CountryRegion countryRegion) {
 this.countryRegion = countryRegion;
 }


 @Column(name="postal_code")
 public java.lang.String getPostalCode() {
 return postalCode;
 }

 public void setPostalCode(java.lang.String postalCode) {
 this.postalCode = postalCode;
 }

}

[danjourno]

i forgot to add referencedColumName to the JoinColumn Annotation when defining the foreign composite key relationship.

[dwebster]

So, for further enlightenment could you provide a code snippet of a stateless session bean that contains a method for returning a list of regions from a specific country code? A partial compound key lookup. I have never gotten one of those to work.

Thanks