Site icon NgDeveloper

How to avoid implicit cross joins in JPA ?

Mirthbees

How to avoid implicit cross joins in JPA ?

When you join two tables and trying to select (column select) from the child table then you will come across this implicit cross join issues.

For example we have two entities namely store, coupon and mapping follows,

Store Entity:

@Entity
public class Store {
@OneToMany(mappedBy = "store")
private List couponList;

public Long getStoreId() {
return storeId;
}
}

Coupon Entity:

I have created one empty and one long argument constructor for coupon entity class. Because I want to select couponId after join and criteriaQuery.multiselect() requires the equivalent constructor to be available already in the entity.

@Entity
public class Coupon {

public Coupon(){

}

Coupon(Long couponId){
this.couponcount = couponId;
}

@ManyToOne
private Store store;
public Store getStore() {
return store;
}

public void setStore(Store store) {
this.store = store;
}
}

Here’s the join using JPA (not using hibernate):

Refer here to join two tables using hibernate:

                CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
		CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Store.class);
		Root storeRoot = criteriaQuery.from(Store.class);
		Root couponRoot = criteriaQuery.from(Coupon.class);
		final List predicates = new ArrayList();

		if (store.getStoreName() != null && !store.getStoreName().isEmpty()) {
		Predicate storeNameLikePredicate = criteriaBuilder.like(storeRoot.get("storeName"),
		"%" + store.getStoreName() + "%");
		predicates.add(storeNameLikePredicate);
		}

		storeRoot.join("couponList",javax.persistence.criteria.JoinType.LEFT);
		criteriaQuery.multiselect( couponRoot.get("couponId"));

		if(predicates.size()>0){
		criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
		}
		criteriaQuery.groupBy(storeRoot.get("storeName"));

		List matchedStoreList = entityManager.createQuery(criteriaQuery).getResultList();
		}

Here we have both Root with Coupon entity class and join with couponList (attribute of store entity), also the selected column couponId is from couponRoot, so left outer join happened between the store and couponList (attribute) and cross join happened between the coupon Entity and store entity.

Root couponRoot = criteriaQuery.from(Coupon.class);
storeRoot.join("couponList",javax.persistence.criteria.JoinType.LEFT);
criteriaQuery.multiselect( couponRoot.get("couponId"));

Hibernate geneated query:

Hibernate: select coupon1_.coupon_id as col_0_0_ from store store0_ left outer join coupon couponlist2_ on store0_.store_id=couponlist2_.store_store_id cross join coupon coupon1_ where store0_.store_name like ? group by store0_.store_name

Code Fix to resolve Implicity cross join:

Here I don’t have coupon Root and selecting everything from couponList (onlyfor coupon table, for store table still I can use storeRoot only). (attribute name of store entity only). so no cross join. 🙂

Join<Store,Coupon> storeCouponJoin = storeRoot.join("couponList",javax.persistence.criteria.JoinType.LEFT);
criteriaQuery.multiselect(storeCouponJoin.get("couponId"));

 

Hibernate generated query after the code fix:

select couponlist1_.coupon_id as col_0_0_ from store store0_ left outer join coupon couponlist1_ on store0_.store_id=couponlist1_.store_store_id where store0_.store_name like ? group by store0_.store_name

If we stop selecting the column from coupon entity we can avoid this issue, also join two tables and multiselect using the joined table name only, instead of root type.

Feel free to share your thoughts in the below comments section.

Exit mobile version