0

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.

Pawan Kumar
  • 259
  • 1
  • 6
  • 17

0 Answers0