I have two entities with uni-directional relation many-to-one as below:
class CarMaster {
// some properties
@Column(name = "CAR_ID")
private Long carId;
}
class CampaignMapping {
// other properties
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "CAR_ID", referencedColumnName = "CAR_ID")
private CarMaster carMaster;
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "CONTRACT_ID", referencedColumnName = "CONTRACT_ID")
private ContractMaster contractMaster;
}
I have below query in which I'm searching CampaignMapping
matching with list of car ids.
@Override
public List<CampaignMapping> findByCarMasterCarId(final Long carIds) {
final CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
final CriteriaQuery<CampaignMapping> criteriaQuery = criteriaBuilder
.createQuery(CampaignMapping.class);
final Root<CampaignMapping> campaignMappingRoot = criteriaQuery
.from(CampaignMapping.class);
// below line didn't help
//campaignMappingRoot.fetch("carMaster");
campaignMappingRoot.fetch("contractMaster");
criteriaQuery.select(CampaignMappingRoot);
// I guess issue is with this line
// causing N queries
criteriaQuery.where(criteriaBuilder.equal(
campaignMappingRoot.get("carMaster").get("carId"),
carIds));
return this.entityManager.createQuery(criteriaQuery).getResultList();
}
I was expecting to get the result in one query with IN clause. I checked hibernate logs and it is printing 1 select query with IN clause having all the ids and select queries for each for each car id in the list with equals condition. I tried with FetchType.EAGER
and campaignMappingRoot.fetch("carMaster");
but still the number of queries fired are same.
Please suggest how can I get the result without N+1 issue using CriteriaBuilder
query only.