Hi,

I have problem in join query. I have two tables :

Table1 = collegerss
fields
======
college_rss_id - primary key - Auto Increament
college_url

Table2 = colleges
fields
======
college_name
college_rss_id = reference key of above table

So, i want to retrieve college_url using college_name. So, how i can do that ? I have defined relationship using annotations based API.

I have done using below way, but its giving some error.

Collegerss collegerss = null;

CollegesId collegeId = new CollegesId();
collegeId.setCollegeName("ABC");

Colleges college = new Colleges();
college.setId(collegeId);

String searchQuery = "from Collegerss col_rss where col_rss.collegeses = :collegeses";

EntityTransaction transaction = entityManager.getTransaction();

transaction.begin();

Query lQuery = entityManager.createQuery(searchQuery);

lQuery.setParameter("collegeses", college);

List<Collegerss> resultList = lQuery.getResultList();


So, how i can do that ? pls help me..

I would advise the following:

1- check the tables and look if the data stored in them is correct, so u are sure that the two entities really exists.

2- check the mapping of the entities again, or at least post it.

3- i have some doubt about your ID, can you post the POJO's of the two entities.

POJO's are :

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

private String collegeName;
private Integer collegeRssId;

public CollegesId() {
}

public CollegesId(String collegeName, Integer collegeRssId) {
this.collegeName = collegeName;
this.collegeRssId = collegeRssId;
}

@Column(name = "college_name", length = 80)
public String getCollegeName() {
return this.collegeName;
}

public void setCollegeName(String collegeName) {
this.collegeName = collegeName;
}

@Column(name = "college_rss_id")
public Integer getCollegeRssId() {
return this.collegeRssId;
}

public void setCollegeRssId(Integer collegeRssId) {
this.collegeRssId = collegeRssId;
}

public boolean equals(Object other) {
if ((this == other))
return true;
if ((other == null))
return false;
if (!(other instanceof CollegesId))
return false;
CollegesId castOther = (CollegesId) other;

return ((this.getCollegeName() == castOther.getCollegeName()) || (this
.getCollegeName() != null
&& castOther.getCollegeName() != null && this.getCollegeName()
.equals(castOther.getCollegeName())))
&& ((this.getCollegeRssId() == castOther.getCollegeRssId()) || (this
.getCollegeRssId() != null
&& castOther.getCollegeRssId() != null && this
.getCollegeRssId().equals(castOther.getCollegeRssId())));
}

public int hashCode() {
int result = 17;

result = 37
* result
+ (getCollegeName() == null ? 0 : this.getCollegeName()
.hashCode());
result = 37
* result
+ (getCollegeRssId() == null ? 0 : this.getCollegeRssId()
.hashCode());
return result;
}

}

@Entity
@Table(name = "colleges", catalog = "lookups")
public class Colleges implements java.io.Serializable {

private CollegesId id;
private Collegerss collegerss;

public Colleges() {
}

public Colleges(CollegesId id) {
this.id = id;
}
public Colleges(CollegesId id, Collegerss collegerss) {
this.id = id;
this.collegerss = collegerss;
}

@EmbeddedId
@AttributeOverrides({
@AttributeOverride(name = "collegeName", column = @Column(name = "college_name", length = 80)),
@AttributeOverride(name = "collegeRssId", column = @Column(name = "college_rss_id"))})
public CollegesId getId() {
return this.id;
}

public void setId(CollegesId id) {
this.id = id;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "college_rss_id", insertable = false, updatable = false)
public Collegerss getCollegerss() {
return this.collegerss;
}

public void setCollegerss(Collegerss collegerss) {
this.collegerss = collegerss;
}

}

@Entity
@Table(name = "collegerss", catalog = "lookups")
public class Collegerss implements java.io.Serializable {

private Integer collegeRssId;
private String sportsRss;
private Set<Colleges> collegeses = new HashSet<Colleges>(0);

public Collegerss() {
}

public Collegerss(String sportsRss, Set<Colleges> collegeses) {
this.sportsRss = sportsRss;
this.collegeses = collegeses;
}

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "college_rss_id", unique = true, nullable = false)
public Integer getCollegeRssId() {
return this.collegeRssId;
}

public void setCollegeRssId(Integer collegeRssId) {
this.collegeRssId = collegeRssId;
}

@Column(name = "sports_rss", length = 128)
public String getSportsRss() {
return this.sportsRss;
}

public void setSportsRss(String sportsRss) {
this.sportsRss = sportsRss;
}
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "collegerss")
public Set<Colleges> getCollegeses() {
return this.collegeses;
}

public void setCollegeses(Set<Colleges> collegeses) {
this.collegeses = collegeses;
}

}

I don't know why you need all that exactly but i would suggest the following

collegerss(college_rss_id (PK), college_url)
college(id(pk), college_name, college_rss_id(FK))

i don't if the relation between the colleges:collegerss is a one-to-one or a many-to-one however i will assume the worst case and make it a many-to-one so i would map the following pojos:

@Entity
@Table(collegerss)
public Collegerss{

@Id
@Column(name = "college_rss_id")
private long id;

@Column(name = "college_url")
private String collegeUrl;

//i specify a cascade all strategy you may change it if needed.
@OneToMany(mappedBy = "collegerss",cascade={CascadeType.ALL})
private Set<College> colleges = new HashMap<College>();

... getters and setters

}


and the second entity would be
@Entity
@Table(college)
public College{

@Id
private long id;

@Column(name = "college_name")
private String collegeName;

@ManyToOne
@JoinColumn(name = "college_rss_id")
Collegerss collegerss;

...getters and setters

}

It works, Thanks for reply.