Traversing ORM relationships returns duplicate results

Question

I have 4 tables -- store, catalog_galleries, catalog_images, and catalog_financials.

When I traverse the relationship from store --> catalog_galleries --> catalog_images in other words: store.getCatalogGallery().getCatalogImages() I get duplicate records. Does anyone know what could be the cause of this? Any suggestions on where to look?

The store table has a OneToOne relationship with catalog_galleries which in turn has a OneToMany relationship with catalog_images and an eager fetch type. The store table also has a OneToMany relationship with catalog_financials.

Below are the relevant entities:

Store entity

@Entity
@Table(name="store")  
public class Store {
    ...
    private CatalogGallery gallery;
    ...
    @OneToOne(mappedBy="store")
    public CatalogGallery getGallery() {
        return gallery;
    }
}

CatalogGallery entity

@Entity
@Table(name="catalog_galleries")  
public class CatalogGallery {
    ...
    private Store store;
    private Collection<CatalogImage> catalogImages;
    ...
    @OneToOne
    @PrimaryKeyJoinColumn
    public Store getStore() {
        return store;
    }

    @OneToMany(mappedBy="catalogGallery", fetch=FetchType.EAGER)
    public Collection<CatalogImage> getCatalogImages {
        return catalogImages;
    }
}

CatalogImage entity

@Entity
@Table(name="catalog_images")  
public class CatalogImage {
    ...
    private CatalogGallery catalogGallery;
    ...
    @ManyToOne
    @JoinColumn(name="gallery_id", insertable=false, updatable=false)
    public CatalogGallery getCatalogGallery() {
        return catalogGallery;
    }

}

Not enough information, please provide your JPA classes with mappings. Have you tried to look at generated SQL? — andbi, Jan 06 '11 at 23:34
Are all the records duplicates? Have you checked your join columns for duplicate entries? — weltraumpirat, Jan 07 '11 at 00:54
Yes, all the records have a duplicate. In `catalog_images` there are multiple entries that have the same value in the `join` column and that is to group the related data/images. — NKing253, Jan 07 '11 at 01:43
So, you have duplicated records in the database, right? Also, why do you need `insertable=false, updatable=false` in join column? — axtavt, Jan 07 '11 at 13:18
@axtavt: The database itself does NOT have duplicate records; however, one field among the columns of `catalog_images` may have the same value and that is to group the fields. For example two rows in the table would be similar to -- 1 1 comp & 2 1 cat5 -- the first column is id, the second is gallery_id, and the third is name. gallery_id is the join column and some rows may have the same value for this column. As for the need of insertable=false, updatable=false, I'm not quite sure myself; when I ran the code I was shown an error message that indicated I needed to add those stipulations — NKing253, Jan 07 '11 at 17:56

score 5 · Answer 1 · edited May 23 '17 at 12:33

This is because of your fetch = FetchType.EAGER.
Hibernate creates JOINs and tries to get all your collections with one query.
If you really need FetchType.EAGER and don't want to replace your collection with Set you can use @Fetch (FetchMode.SELECT) annotation for your collection:

@OneToMany(mappedBy="catalogGallery", fetch=FetchType.EAGER)
@Fetch (FetchMode.SELECT)
public Collection<CatalogImage> getCatalogImages {
    return catalogImages;
}

For more information see this post

score 0 · Answer 2 · answered Jan 07 '11 at 08:51

0

If you implement the equals and hashcode methods correctly and store in a Set rather than a Collection, it is less of an issue unless the volume of duplicates is excessive.

answered Jan 07 '11 at 08:51

David O'Meara

2,983
25
38

score 0 · Answer 3 · answered Jan 07 '11 at 11:10

0

Your getter catalogImages() does not follow Java Bean naming convention. I'm not sure, but it might confuse JPA provider somehow.
By using Collection<CatalogImage> you've explicitly told JPA provider that duplicates are allowed. As it was already told, using Set instead of Collection solves duplicates-issue in most cases and allows avoiding FetchType.EAGER unless you really need it.

answered Jan 07 '11 at 11:10

andbi

4,426
5
45
70

the getter is supposed to be `getCatalogImage`, the original posting of the code was a typo. As for switching to `Set` I understand that will eliminate duplicates from being added, but I don't understand why duplicates are being created from the jpa/hibernate created sql query itself. – NKing253 Jan 07 '11 at 14:45
@NKing253, from my pov its because of one2one relation between Store and CatalogGallery or because you're querying dataset of two Stores. Depend's on your sql schema. JPA is a "black box" and best way to find what's going on, is to see generated sql. For hibernate use option hibernate.show_sql http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html#configuration-optional – andbi Jan 07 '11 at 15:12

Traversing ORM relationships returns duplicate results

3 Answers3