many-to-many duplicate rows (Cartesian product actually)

M1ckey · **Joined:** Thu Jan 21, 2010 5:13 pm **Posts:** 7

Hey guys,

Here's my entity.

Code:

@Entity
@Table(name = "USERS")
public class User {
   // ...
   @ManyToMany(fetch = FetchType.EAGER)
   @JoinTable(name = "USER_PROFILE", joinColumns = @JoinColumn(name = "USER_ID"), inverseJoinColumns = @JoinColumn(name = "SECURITY_PROFILE_ID"))
   public Set<SecurityProfile> getSecurityProfiles() {
      return securityProfiles;
   }
}

When I try to run this code using Hibernate Criteria, I get, well, ALMOST duplicate rows which are not identical, it looks more like Cartesian product.

That's what the query looks like:

Code:

select this_.USER_ID as USER1_0_3_, this_.DEFAULT_SECURITY_PROFILE_ID as DEFAULT6_0_3_, this_.NAME as NAME0_3_, this_.FULL_NAME as FULL3_0_3_, this_.ACTIVE as ACTIVE0_3_, this_.DEFAULT_BU_ID as DEFAULT5_0_3_, securitypr2_.SECURITY_PROFILE_ID as SECURITY1_1_0_, securitypr2_.PROFILE as PROFILE1_0_, securitypr3_.USER_ID as USER1_5_, securitypr4_.SECURITY_PROFILE_ID as SECURITY2_5_, securitypr4_.SECURITY_PROFILE_ID as SECURITY1_1_1_, securitypr4_.PROFILE as PROFILE1_1_, businessda5_.BUSINESS_UNIT_ID as BUSINESS1_2_2_, businessda5_.BU_CODE as BU2_2_2_

from USERS this_, SECURITY_PROFILE securitypr2_, USER_PROFILE securitypr3_, SECURITY_PROFILE securitypr4_, BUSINESS_DATA businessda5_

where this_.DEFAULT_SECURITY_PROFILE_ID=securitypr2_.SECURITY_PROFILE_ID(+)
and this_.USER_ID=securitypr3_.USER_ID(+)
and securitypr3_.SECURITY_PROFILE_ID=securitypr4_.SECURITY_PROFILE_ID(+)
and this_.DEFAULT_BU_ID=businessda5_.BUSINESS_UNIT_ID(+)

order by this_.NAME asc

Security profile is there twice for some reason... Any ideas? Thanks!

ramesh844 · **Joined:** Sun Jun 18, 2006 1:58 pm **Posts:** 6

Did you override equals() and hashCode() methods in SecurityProfile?

hallmit · **Posted:** Tue Mar 09, 2010 5:45 pm

can you post please your Criteria query code?

nordborg · **Posted:** Tue Mar 09, 2010 5:55 pm

Quote:

Security profile is there twice for some reason

I can understand why securitypr4_ is there because that is joining the @ManyToMany that you show in the post. I suspect that you also have mapped a "default security profile" as a @ManyToOne that uses the DEFAULT_SECURITY_PROFILE_ID column, except that you have not included this in the post... Is this correct?

M1ckey · **Joined:** Thu Jan 21, 2010 5:13 pm **Posts:** 7

Thank you for your replies.

Yes, I have overriden equals and hashcode for all entity classes used in the query.

This is my criteria code:

Code:

      DetachedCriteria detachedCriteria = DetachedCriteria
            .forClass(User.class);
      detachedCriteria = detachedCriteria.addOrder(Order.asc("logonName"));

      return hibernateTemplate.findByCriteria(detachedCriteria, offset,
            fetchSize);

It's hibernate template from Spring.

That's correct, default security profile is a @ManyToOne, but for the sake of simplicity I commented it out and still I was getting Cartesian product.

My temporary solution
I put this annotation above the get method:

Code:

@Fetch(value = FetchMode.SUBSELECT)

... so that it looks like that now:

Code:

   @OneToMany(fetch = FetchType.EAGER)
   @Fetch(value = FetchMode.SUBSELECT)
   @JoinTable(name = "USER_PROFILE", joinColumns = @JoinColumn(name = "USER_ID"), inverseJoinColumns = @JoinColumn(name = "SECURITY_PROFILE_ID"))
   public Set<SecurityProfile> getSecurityProfiles() {
      return securityProfiles;
   }

But this Fetch thing is from Hibernate and I wanted to use only JPA annotations. Is there anything I can do about it?