How to avoid hibernate from doing left outer join with OneToMany while using Criteria

Question

I have a Request object which has OneToMany mapping to KPIs (Request has a list of KPIs) ... Now I need to get a certain Request using some parameters set in criteria , according to data (not DB constraints) the parameters passed should only return one Request object , the problem is that hibernate query created has left outer join to the KPIs , so if the Request has 5 KPIs inside, 5 duplicate rows are returned ... I need only one ... how can I fulfill this

code for reading :

        Criteria criteria = session.createCriteria(GroupKpiRequest.class);
    criteria.add(Restrictions.eq("levelCategory",LevelCategory.COMPANY));
        if(cycleNumber == 0){
            cycleNumber = getActiveCycleNo();
        }
        criteria.add(Restrictions.eq("cycleNumber",cycleNumber));

        List<GroupKpiRequest> matchingRequests = (List<GroupKpiRequest>) 
criteria.list();
        if(matchingRequests != null && matchingRequests.size() == 1 ){
            GroupKpiRequest companyRequestToDelete  = 
 matchingRequests.get(0);

Request class:

public class GroupKpiRequest {
 .
 .
 .
    @OneToMany(mappedBy="groupKpiRequest", cascade={CascadeType.ALL}, fetch=FetchType.LAZY)
    @Cascade({
    org.hibernate.annotations.CascadeType.SAVE_UPDATE,
    org.hibernate.annotations.CascadeType.DELETE,
    org.hibernate.annotations.CascadeType.MERGE,
    org.hibernate.annotations.CascadeType.PERSIST,
    org.hibernate.annotations.CascadeType.DELETE_ORPHAN//,
    org.hibernate.annotations.CascadeType.EVICT
    })
@OrderBy("groupKpiId")
@Fetch(FetchMode.SUBSELECT)
private List<GroupKPI> kpiSet;

KPI class :

public class GroupKPI implements Serializable{
@OneToOne(fetch=FetchType.EAGER)
@JoinColumn(name="CALCULATION_METHOD",referencedColumnName="METHOD_NAME")
private CalculationMethod calculationMethod;

@ManyToOne
@JoinColumns({
    @JoinColumn(name="GRP_KPI_REQ_ID",referencedColumnName="GRP_KPI_REQ_ID" , nullable=false)

    }
 )
 @Fetch(FetchMode.JOIN)
 private GroupKpiRequest groupKpiRequest;

query :

select ..... 
from
    REQUEST this_ 
left outer join
    GROUP_KPI kpiset2_ 
        on this_.GRP_KPI_REQ_ID=kpiset2_.GRP_KPI_REQ_ID 
left outer join
    CALC_METHOD calculatio3_ 
        on kpiset2_.CALCULATION_METHOD=calculatio3_.METHOD_NAME 
left outer join
    ORGANIZATION organizati4_ 
        on this_.CYCLE_NO=organizati4_.CYCLE_NO 
        and this_.ORG_ID=organizati4_.ORG_ID 
where
    this_.deleted = 0 
    and this_.GRP_KPI_REQ_ID=? 
order by
    kpiset2_.GRP_KPI_ID asc

score 0 · Accepted Answer · answered Oct 28 '17 at 21:39

0

Obviously you have a left outer join cause of this

order by
    kpiset2_.GRP_KPI_ID asc

The reason of the order that there is @OrderBy("groupKpiId") annotation on the kpiSet field.

To remove duplicate rows you can try to use DISTINCT_ROOT_ENTITY transformer

Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct

It is better not to use @OrderBy, in my opinion, cause it is not a very flexible approach.

answered Oct 28 '17 at 21:39

v.ladynev

19,275
8
46
67

actually removing the OrderBy didn't fix the problem but introducing the Distinct option did it ... I found that hibernate does this left join by default according to https://stackoverflow.com/questions/40626002/jpa-one-to-many-join-table-give-incorrect-child-records – osama yaccoub Oct 29 '17 at 06:31
@osamayaccoub I don't think that question describes the reason. There is an implicit join there: `Join packagesItem = pRoot.join("item")`. – v.ladynev Oct 29 '17 at 15:34

How to avoid hibernate from doing left outer join with OneToMany while using Criteria

1 Answers1