0 Replies Latest reply on May 20, 2012 4:41 PM by mahesh Rajesh

    JPA Query to join 2 Entities (INNER JOIN) !!

    mahesh Rajesh Newbie

      I used seam to generate the entities EasyCountry and EasyState. I am trying to join these 2 entities using INNER JOIN. Appreciate, If you can provide some help on how to navigate between these 2 entities.

       

      I used the following queries and It is not working.

       

      1. select c.countryCode, c.countryName,s.stateName,s.stateCode from EasyCountry c, IN (c.easyStates) as s

       

      2. select c.countryCode, c.countryName,s.stateName,s.stateCode from EasyCountry c INNER JOIN c.easyCountry s

       

      but the cartisean product of these 2 entities is working fine.

       

      select c.countryCode, c.countryName,s.stateName,s.stateCode from EasyCountry c EasyState s

       

      EasyCountry

       

      // Generated May 15, 2012 11:57:14 AM by Hibernate Tools 3.2.4.GA

      import java.util.HashSet;
      import java.util.Set;
      import javax.persistence.Column;
      import javax.persistence.Entity;
      import javax.persistence.FetchType;
      import javax.persistence.Id;
      import javax.persistence.OneToMany;
      import javax.persistence.Table;
      import org.hibernate.validator.Length;
      import org.hibernate.validator.NotNull;

      /**
      * EasyCountry generated by hbm2java
      */
      @Entity
      @Table(name = "easy_country", catalog = "mydb")
      public class EasyCountry implements java.io.Serializable {

      private String countryCode;
      private String countryId;
      private String countryName;
      private String printableCountryName;
      private Short numCode;
      private Set<EasyState> easyStates = new HashSet<EasyState>(0);
      private Set<EasyState> easyStates_1 = new HashSet<EasyState>(0);
      private Set<EasyPropertyAddress> easyPropertyAddresses = new HashSet<EasyPropertyAddress>(
      0);
      private Set<EasyPropertyAddress> easyPropertyAddresses_1 = new HashSet<EasyPropertyAddress>(
      0);
      private Set<EasyPropertyAddress> easyPropertyAddresses_2 = new HashSet<EasyPropertyAddress>(
      0);
      private Set<EasyState> easyStates_2 = new HashSet<EasyState>(0);

       

      public EasyCountry() {
      }

       

      public EasyCountry(String countryCode, String countryId,
      String countryName, String printableCountryName) {
      this.countryCode = countryCode;
      this.countryId = countryId;
      this.countryName = countryName;
      this.printableCountryName = printableCountryName;
      }

      public EasyCountry(String countryCode, String countryId,
      String countryName, String printableCountryName, Short numCode,
      Set<EasyState> easyStates, Set<EasyState> easyStates_1,
      Set<EasyPropertyAddress> easyPropertyAddresses,
      Set<EasyPropertyAddress> easyPropertyAddresses_1,
      Set<EasyPropertyAddress> easyPropertyAddresses_2,
      Set<EasyState> easyStates_2) {
      this.countryCode = countryCode;
      this.countryId = countryId;
      this.countryName = countryName;
      this.printableCountryName = printableCountryName;
      this.numCode = numCode;
      this.easyStates = easyStates;
      this.easyStates_1 = easyStates_1;
      this.easyPropertyAddresses = easyPropertyAddresses;
      this.easyPropertyAddresses_1 = easyPropertyAddresses_1;
      this.easyPropertyAddresses_2 = easyPropertyAddresses_2;
      this.easyStates_2 = easyStates_2;
      }

       

      @Id
      @Column(name = "COUNTRY_CODE", unique = true, nullable = false, length = 3)
      @NotNull
      @Length(max = 3)
      public String getCountryCode() {
      return this.countryCode;
      }

       

      public void setCountryCode(String countryCode) {
      this.countryCode = countryCode;
      }

       

      @Column(name = "COUNTRY_ID", nullable = false, length = 2)
      @NotNull
      @Length(max = 2)
      public String getCountryId() {
      return this.countryId;
      }

       

      public void setCountryId(String countryId) {
      this.countryId = countryId;
      }

       

      @Column(name = "COUNTRY_NAME", nullable = false, length = 80)
      @NotNull
      @Length(max = 80)
      public String getCountryName() {
      return this.countryName;
      }

       

      public void setCountryName(String countryName) {
      this.countryName = countryName;
      }

       

      @Column(name = "PRINTABLE_COUNTRY_NAME", nullable = false, length = 80)
      @NotNull
      @Length(max = 80)
      public String getPrintableCountryName() {
      return this.printableCountryName;
      }

       

      public void setPrintableCountryName(String printableCountryName) {
      this.printableCountryName = printableCountryName;
      }

       

      @Column(name = "NUM_CODE")
      public Short getNumCode() {
      return this.numCode;
      }

       

      public void setNumCode(Short numCode) {
      this.numCode = numCode;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyCountry")
      public Set<EasyState> getEasyStates() {
      return this.easyStates;
      }

       

      public void setEasyStates(Set<EasyState> easyStates) {
      this.easyStates = easyStates;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyCountry")
      public Set<EasyState> getEasyStates_1() {
      return this.easyStates_1;
      }

       

      public void setEasyStates_1(Set<EasyState> easyStates_1) {
      this.easyStates_1 = easyStates_1;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyCountry")
      public Set<EasyPropertyAddress> getEasyPropertyAddresses() {
      return this.easyPropertyAddresses;
      }

       

      public void setEasyPropertyAddresses(
      Set<EasyPropertyAddress> easyPropertyAddresses) {
      this.easyPropertyAddresses = easyPropertyAddresses;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyCountry")
      public Set<EasyPropertyAddress> getEasyPropertyAddresses_1() {
      return this.easyPropertyAddresses_1;
      }

       

      public void setEasyPropertyAddresses_1(
      Set<EasyPropertyAddress> easyPropertyAddresses_1) {
      this.easyPropertyAddresses_1 = easyPropertyAddresses_1;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyCountry")
      public Set<EasyPropertyAddress> getEasyPropertyAddresses_2() {
      return this.easyPropertyAddresses_2;
      }

       

      public void setEasyPropertyAddresses_2(
      Set<EasyPropertyAddress> easyPropertyAddresses_2) {
      this.easyPropertyAddresses_2 = easyPropertyAddresses_2;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyCountry")
      public Set<EasyState> getEasyStates_2() {
      return this.easyStates_2;
      }

       

      public void setEasyStates_2(Set<EasyState> easyStates_2) {
      this.easyStates_2 = easyStates_2;
      }

      }

       

      EasyState Entity

       

      // Generated May 15, 2012 11:57:14 AM by Hibernate Tools 3.2.4.GA

      import java.util.HashSet;
      import java.util.Set;
      import javax.persistence.Column;
      import javax.persistence.Entity;
      import javax.persistence.FetchType;
      import javax.persistence.GeneratedValue;
      import static javax.persistence.GenerationType.IDENTITY;
      import javax.persistence.Id;
      import javax.persistence.JoinColumn;
      import javax.persistence.ManyToOne;
      import javax.persistence.OneToMany;
      import javax.persistence.Table;
      import javax.persistence.UniqueConstraint;
      import org.hibernate.validator.Length;
      import org.hibernate.validator.NotNull;

      /**
      * EasyState generated by hbm2java
      */
      @Entity
      @Table(name = "easy_state", catalog = "mydb", uniqueConstraints = {
      @UniqueConstraint(columnNames = "STATE_NAME"),
      @UniqueConstraint(columnNames = "STATE_ID")})
      public class EasyState implements java.io.Serializable {

      private String stateCode;
      private EasyCountry easyCountry;
      private int stateId;
      private String stateName;
      private Set<EasyPropertyAddress> easyPropertyAddresses = new HashSet<EasyPropertyAddress>(
      0);
      private Set<EasyPropertyAddress> easyPropertyAddresses_1 = new HashSet<EasyPropertyAddress>(
      0);
      private Set<EasyCity> easyCities = new HashSet<EasyCity>(0);
      private Set<EasyCity> easyCities_1 = new HashSet<EasyCity>(0);
      private Set<EasyCountyInfo> easyCountyInfos = new HashSet<EasyCountyInfo>(0);
      private Set<EasyCity> easyCities_2 = new HashSet<EasyCity>(0);
      private Set<EasyCountyInfo> easyCountyInfos_1 = new HashSet<EasyCountyInfo>(
      0);
      private Set<EasyPropertyAddress> easyPropertyAddresses_2 = new HashSet<EasyPropertyAddress>(
      0);
      private Set<EasyCountyInfo> easyCountyInfos_2 = new HashSet<EasyCountyInfo>(
      0);

       

      public EasyState() {
      }

       

      public EasyState(EasyCountry easyCountry, int stateId, String stateName) {
      this.easyCountry = easyCountry;
      this.stateId = stateId;
      this.stateName = stateName;
      }

      public EasyState(EasyCountry easyCountry, int stateId, String stateName,
      Set<EasyPropertyAddress> easyPropertyAddresses,
      Set<EasyPropertyAddress> easyPropertyAddresses_1,
      Set<EasyCity> easyCities, Set<EasyCity> easyCities_1,
      Set<EasyCountyInfo> easyCountyInfos, Set<EasyCity> easyCities_2,
      Set<EasyCountyInfo> easyCountyInfos_1,
      Set<EasyPropertyAddress> easyPropertyAddresses_2,
      Set<EasyCountyInfo> easyCountyInfos_2) {
      this.easyCountry = easyCountry;
      this.stateId = stateId;
      this.stateName = stateName;
      this.easyPropertyAddresses = easyPropertyAddresses;
      this.easyPropertyAddresses_1 = easyPropertyAddresses_1;
      this.easyCities = easyCities;
      this.easyCities_1 = easyCities_1;
      this.easyCountyInfos = easyCountyInfos;
      this.easyCities_2 = easyCities_2;
      this.easyCountyInfos_1 = easyCountyInfos_1;
      this.easyPropertyAddresses_2 = easyPropertyAddresses_2;
      this.easyCountyInfos_2 = easyCountyInfos_2;
      }

       

      @Id
      @GeneratedValue(strategy = IDENTITY)
      @Column(name = "STATE_CODE", unique = true, nullable = false, length = 10)
      @Length(max = 10)
      public String getStateCode() {
      return this.stateCode;
      }

       

      public void setStateCode(String stateCode) {
      this.stateCode = stateCode;
      }

       

      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = "COUNTRY_CODE", nullable = false)
      @NotNull
      public EasyCountry getEasyCountry() {
      return this.easyCountry;
      }

       

      public void setEasyCountry(EasyCountry easyCountry) {
      this.easyCountry = easyCountry;
      }

       

      @Column(name = "STATE_ID", unique = true, nullable = false)
      public int getStateId() {
      return this.stateId;
      }

       

      public void setStateId(int stateId) {
      this.stateId = stateId;
      }

       

      @Column(name = "STATE_NAME", unique = true, nullable = false, length = 50)
      @NotNull
      @Length(max = 50)
      public String getStateName() {
      return this.stateName;
      }

       

      public void setStateName(String stateName) {
      this.stateName = stateName;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyPropertyAddress> getEasyPropertyAddresses() {
      return this.easyPropertyAddresses;
      }

       

      public void setEasyPropertyAddresses(
      Set<EasyPropertyAddress> easyPropertyAddresses) {
      this.easyPropertyAddresses = easyPropertyAddresses;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyPropertyAddress> getEasyPropertyAddresses_1() {
      return this.easyPropertyAddresses_1;
      }

       

      public void setEasyPropertyAddresses_1(
      Set<EasyPropertyAddress> easyPropertyAddresses_1) {
      this.easyPropertyAddresses_1 = easyPropertyAddresses_1;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyCity> getEasyCities() {
      return this.easyCities;
      }

       

      public void setEasyCities(Set<EasyCity> easyCities) {
      this.easyCities = easyCities;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyCity> getEasyCities_1() {
      return this.easyCities_1;
      }

       

      public void setEasyCities_1(Set<EasyCity> easyCities_1) {
      this.easyCities_1 = easyCities_1;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyCountyInfo> getEasyCountyInfos() {
      return this.easyCountyInfos;
      }

       

      public void setEasyCountyInfos(Set<EasyCountyInfo> easyCountyInfos) {
      this.easyCountyInfos = easyCountyInfos;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyCity> getEasyCities_2() {
      return this.easyCities_2;
      }

       

      public void setEasyCities_2(Set<EasyCity> easyCities_2) {
      this.easyCities_2 = easyCities_2;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyCountyInfo> getEasyCountyInfos_1() {
      return this.easyCountyInfos_1;
      }

       

      public void setEasyCountyInfos_1(Set<EasyCountyInfo> easyCountyInfos_1) {
      this.easyCountyInfos_1 = easyCountyInfos_1;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyPropertyAddress> getEasyPropertyAddresses_2() {
      return this.easyPropertyAddresses_2;
      }

       

      public void setEasyPropertyAddresses_2(
      Set<EasyPropertyAddress> easyPropertyAddresses_2) {
      this.easyPropertyAddresses_2 = easyPropertyAddresses_2;
      }

       

      @OneToMany(fetch = FetchType.LAZY, mappedBy = "easyState")
      public Set<EasyCountyInfo> getEasyCountyInfos_2() {
      return this.easyCountyInfos_2;
      }

       

      public void setEasyCountyInfos_2(Set<EasyCountyInfo> easyCountyInfos_2) {
      this.easyCountyInfos_2 = easyCountyInfos_2;
      }

      }