JPA Query to join 2 Entities (INNER JOIN) !!
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;
}
}