7 Replies Latest reply on Jan 26, 2006 9:48 PM by David Webster

    composite PK/FK

    daniel jarrett Newbie

      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;
       }
      
      }
      
      


        • 1. Re: composite PK/FK
          Bill Burke Master

          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

          • 2. Re: composite PK/FK
            daniel jarrett Newbie

            Thanks Bill,

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

            • 3. Re: composite PK/FK
              daniel jarrett Newbie

              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;
               }
              



              • 4. Re: composite PK/FK
                Emmanuel Bernard Master

                did you remove this one

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


                • 5. Re: composite PK/FK
                  daniel jarrett Newbie

                  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;
                   }
                  
                  }


                  • 6. Re: composite PK/FK
                    daniel jarrett Newbie

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

                    • 7. Re: composite PK/FK
                      David Webster Newbie

                      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