Manual Method without criteriaQuery
I have used this code to get result set which contains of Thirdparty by using a left join with another table ThirdPartyHasOwner which has two primary keys and is a foreign key itself. Now the below code retrieves the correct result data set.
Query query = emManager.createQuery("SELECT c FROM ThirdParty c LEFT JOIN ThirdPartyHasOwner b ON b.id.third_party_Id = c.id WHERE b.id.ownerId=1");
List<ThirdParty> thirdParties = query.getResultList();
With Criteria Builder and Criteria Query
But When using with the criteria builder and Query the result set gives a wrong dataset. The code is given below So to check whether both above manual query and criteria query gives the same query I added Property <property name="eclipselink.logging.level" value="FINE"/> for which both the above code without criteria query and the below code with criteria query both gave the same query Both the code and console result are given below
CriteriaBuilder cb = emManager.getCriteriaBuilder();
CriteriaQuery<ThirdParty> cq = cb.createQuery(ThirdParty.class);
Root<ThirdParty> a = cq.from(ThirdParty.class);
Join<ThirdParty, ThirdPartyHasOwner> b = a.join("thirdPartyHasOwners", JoinType.LEFT);
ParameterExpression<Integer> balance = cb.parameter(Integer.class);
Path<Integer> path = b.get("id").get("ownerId");
cq.where(cb.gt(path, balance));
cq.select(a);
TypedQuery<ThirdParty> queryS = emManager.createQuery(cq);
List<ThirdParty> results = queryS.setParameter(balance, 1).getResultList();
The Console result (The first one is for the Criteria Query and the second one is for the manual method)
[EL Fine]: sql: 2017-01-04 06:42:44.026--ServerSession(514728045)--Connection(1288428548)--Thread(Thread[http-bio-8080-exec-3,5,main])--SELECT t1.Id, t1.ADDRESS, t1.CONTACTNO, t1.CREATEDDATE, t1.EMAIL, t1.NAME FROM third_party t1 LEFT OUTER JOIN third_party_has_owner t0 ON (t0.THIRD_PARTY_ID = t1.Id) WHERE (t0.owner_id > ?)
bind => [1]
[EL Fine]: sql: 2017-01-04 06:48:26.109--ServerSession(514728045)--Connection(1288428548)--Thread(Thread[http-bio-8080-exec-3,5,main])--SELECT t1.Id, t1.ADDRESS, t1.CONTACTNO, t1.CREATEDDATE, t1.EMAIL, t1.NAME FROM third_party t1 LEFT OUTER JOIN third_party_has_owner t0 ON (t0.THIRD_PARTY_ID = t1.Id) WHERE (t0.owner_id = ?)
bind => [1]
result for both manual and criteria query (result variable has the wrong data which is the result of using criteria query and thirdparties variable has the correct data set which is the result of the manual method)
Last but not least I am using javax.persistence.persistence-api which is eclipselink
This is the overview of the Database tables
Model Class For Third Party
/**
* The persistent class for the third_party database table.
*
*/
@Entity
@Table(name="third_party")
@NamedQuery(name="ThirdParty.findAll", query="SELECT t FROM ThirdParty t")
public class ThirdParty implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="Id")
private int id;
private String address;
private String contactNo;
@Temporal(TemporalType.TIMESTAMP)
private Date createdDate;
private String email;
private String name;
//bi-directional many-to-one association to ThirdPartyHasOwner
@OneToMany(mappedBy="thirdParty")
private List<ThirdPartyHasOwner> thirdPartyHasOwners;
//bi-directional many-to-one association to ThirdSeatAllocation
@OneToMany(mappedBy="thirdParty")
private List<ThirdSeatAllocation> thirdSeatAllocations;
public ThirdParty() {
}
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
public String getAddress() {
return this.address;
}
public void setAddress(String address) {
this.address = address;
}
public String getContactNo() {
return this.contactNo;
}
public void setContactNo(String contactNo) {
this.contactNo = contactNo;
}
public Date getCreatedDate() {
return this.createdDate;
}
public void setCreatedDate(Date createdDate) {
this.createdDate = createdDate;
}
public String getEmail() {
return this.email;
}
public void setEmail(String email) {
this.email = email;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public List<ThirdPartyHasOwner> getThirdPartyHasOwners() {
return this.thirdPartyHasOwners;
}
public void setThirdPartyHasOwners(List<ThirdPartyHasOwner> thirdPartyHasOwners) {
this.thirdPartyHasOwners = thirdPartyHasOwners;
}
public ThirdPartyHasOwner addThirdPartyHasOwner(ThirdPartyHasOwner thirdPartyHasOwner) {
getThirdPartyHasOwners().add(thirdPartyHasOwner);
thirdPartyHasOwner.setThirdParty(this);
return thirdPartyHasOwner;
}
public ThirdPartyHasOwner removeThirdPartyHasOwner(ThirdPartyHasOwner thirdPartyHasOwner) {
getThirdPartyHasOwners().remove(thirdPartyHasOwner);
thirdPartyHasOwner.setThirdParty(null);
return thirdPartyHasOwner;
}
public List<ThirdSeatAllocation> getThirdSeatAllocations() {
return this.thirdSeatAllocations;
}
public void setThirdSeatAllocations(List<ThirdSeatAllocation> thirdSeatAllocations) {
this.thirdSeatAllocations = thirdSeatAllocations;
}
public ThirdSeatAllocation addThirdSeatAllocation(ThirdSeatAllocation thirdSeatAllocation) {
getThirdSeatAllocations().add(thirdSeatAllocation);
thirdSeatAllocation.setThirdParty(this);
return thirdSeatAllocation;
}
public ThirdSeatAllocation removeThirdSeatAllocation(ThirdSeatAllocation thirdSeatAllocation) {
getThirdSeatAllocations().remove(thirdSeatAllocation);
thirdSeatAllocation.setThirdParty(null);
return thirdSeatAllocation;
}
}
Model Class For Third Party has Owner
/**
* The persistent class for the third_party_has_owner database table.
*
*/
@Entity
@Table(name="third_party_has_owner")
@NamedQuery(name="ThirdPartyHasOwner.findAll", query="SELECT t FROM ThirdPartyHasOwner t")
public class ThirdPartyHasOwner implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
private ThirdPartyHasOwnerPK id;
@Temporal(TemporalType.DATE)
private Date createdDate;
//bi-directional many-to-one association to Owner
@ManyToOne
private Owner owner;
//bi-directional many-to-one association to ThirdParty
@ManyToOne
@JoinColumn(name="third_party_Id")
private ThirdParty thirdParty;
public ThirdPartyHasOwner() {
}
public ThirdPartyHasOwnerPK getId() {
return this.id;
}
public void setId(ThirdPartyHasOwnerPK id) {
this.id = id;
}
public Date getCreatedDate() {
return this.createdDate;
}
public void setCreatedDate(Date createdDate) {
this.createdDate = createdDate;
}
public Owner getOwner() {
return this.owner;
}
public void setOwner(Owner owner) {
this.owner = owner;
}
public ThirdParty getThirdParty() {
return this.thirdParty;
}
public void setThirdParty(ThirdParty thirdParty) {
this.thirdParty = thirdParty;
}
}
So Did I do something wrong with the criteria query or is it some weird bug?
Though the log was fine still I have used a different method which gives the different data set.
Before Code
CriteriaBuilder cb = emManager.getCriteriaBuilder();
CriteriaQuery<ThirdParty> cq = cb.createQuery(ThirdParty.class);
Root<ThirdParty> a = cq.from(ThirdParty.class);
Join<ThirdParty, ThirdPartyHasOwner> b = a.join("thirdPartyHasOwners", JoinType.LEFT);
ParameterExpression<Integer> balance = cb.parameter(Integer.class);
Path<Integer> path = b.get("id").get("ownerId");
cq.where(cb.gt(path, balance));
cq.select(a);
TypedQuery<ThirdParty> queryS = emManager.createQuery(cq);
List<ThirdParty> results = queryS.setParameter(balance, 1).getResultList();
After Code
CriteriaBuilder cb = emManager.getCriteriaBuilder();
CriteriaQuery<ThirdParty> cq = cb.createQuery(ThirdParty.class);
Root<ThirdParty> a = cq.from(ThirdParty.class);
Join<ThirdParty, ThirdPartyHasOwner> b = a.join("thirdPartyHasOwners", JoinType.LEFT);
ParameterExpression<Integer> balance = cb.parameter(Integer.class);
cq.where(cb.equal( b.get("id").get("ownerId"),balance));
cq.select(a);
TypedQuery<ThirdParty> queryS = emManager.createQuery(cq);
List<ThirdParty> results = queryS.setParameter(balance, 1).getResultList();
The code which is being changed is cq.where(cb.gt(path, balance)); to cq.where(cb.equal( b.get("id").get("ownerId"),balance)); for criteria builder equal is used here is equal to Where b.owner_id = balance balance is a parameter