hibernate duplicate domains while getting data

Question

I have two tables where there is a one to many relationship between those tables. here is a tables: Category table (Parent table)

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;

@Column(name="CATEGORY_NAME")
private String categoryName;

//bi-directional many-to-one association to TmCategoryPropertiesMapping
@OneToMany(mappedBy="tmCategory", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<TmCategoryPropertiesMapping> tmCategoryPropertiesMappings;
.............
....... getter and setters

and the other entity: category Mapping table (child table)

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;

@Column(name="CREATED_BY")
private BigInteger createdBy;

@Column(name="CREATED_DATE")
private Timestamp createdDate;

@Column(name="PROPERTY_ID")
private BigInteger propertyId;

//bi-directional many-to-one association to TmCategory
@ManyToOne
@JoinColumn(name="CATEGORY_ID")
private TmCategory tmCategory;

Here if I get the categories using owner ID I am getting duplicates in my result. Even though I have only 3 entries in category table but I am getting 10 entities in my result. What is the reason for this? any how can I overcome from this? The table data is here:

The result I get here is id= 1,1,2,2,2,3,3,3,3

Answer 1

You have made the toMany association EAGER. This means that every time a category is loaded, all its mappings are also etrieved. Which means that, instead of retrieving one row per category, N rows are retrieved.

To deduplicate the result list, you simply need

select distinct

instead of

select

But I would not make the toMany association eager. If you really need categories with their mappings in some use case, the explicitely fetch them, instead of fetching them every time, even when not needed:

select distinct c from Category c
left join fetch c.tmCategoryPropertiesMappings
where ...

Answer 2

Okay I got it. This happens with relationships with tables with eager fetching. To come out of this problem we need to specify distinct in criteria itself and here is the solution:

DetachedCriteria criteria = DetachedCriteria.forClass(Category.class);
        criteria.add(Restrictions.eq("ownerId", BigInteger.valueOf(id)));
    criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
        List<Category> categories = getHibernateTemplate().findByCriteria(criteria);

Note the line

            criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

this removes the duplicates from the result set.