Susie
Susie

Reputation: 5148

Hibernate query/criteria returning duplicate data

criteria = createCriteria("employee");
criteria.add(Restrictions.eq("name", "Jack"));
criteria.createAlias("certificate", "cert");
criteria.add(Restrictions.eq("cert.certType", "MSFT"));  

criteriaList = criteria.list();

Given the data below, I think the query above should have returned one record that contains a set(set size=2) of certificates but I get the same record duplicated twice(once for each record in Certificate table). Why is this happening?

Employee Table:

EMP_ID          NAME    
123             Jack                                
111             Mary            
000             Larry   

Certificate table data

emp_id      certificate_type    seq_no
123         MSFT                  1
123         MSFT                  2
111         English               1

employee.hbm.xml

<class name="com.Employee" table="Employee" entity-name="employee" mutable="false">
        <cache usage="read-only"/>
        <id name="id" column="employee_id"/>
        <set name="certificate" fetch="select" inverse="true" lazy="false" >
            <key column="employee_id" />
            <one-to-many class="com.Certificate" entity-name="CertificateType"/>
        </set>
</class>    

certificate.hbm.xml

<class name="com.Certificate" table="Certificate" entity-name="CertificateType" mutable="false">
    <cache usage="read-only"/>
    <composite-id class="com.usps.nom.tops.model.impl.DispatchLegPKImpl" mapped="true">
            <key-property name="empId" column="emp_id" />
            <key-property name="seqNo" column="SEQ_NO" />
    </composite-id>
    <property name="certType" column="certificate_type"/>
</class>

POJOs

public class Employee {
   private int id;
   private String ame;  
    //getters and setters
    public boolean equals(Object obj){}
}
public class Certificate {
   private int emp_id;
   private String certType; 
   private String seqNo;
   //getters and setters   
   public boolean equals(Object obj){}
    }

EDIT: If I put the result (ie criteriaList in my example) in a set, then it gets rid of the duplicate record.

Set<Employee> empSet = new HashSet<Employee>(criteriaList); 

Upvotes: 0

Views: 2936

Answers (1)

Serg
Serg

Reputation: 1367

I'm newbie in Hibernate, but faced with similar problem (parent records are duplicated by join)

I have added FetchMode.SUBSELECT annotation (I prefer annotations)

@OneToMany
@Fetch(FetchMode.SUBSELECT)

It looks like working perfectly for me without duplicating data.

Upvotes: 3

Related Questions