Does an @ManyToMany ALWAYS generate a left outer join SQL?

RepublicanJew · **Joined:** Mon Feb 20, 2006 8:05 pm **Posts:** 8

Howdy, Hibernators. I'm having a problem whereby I find myself too unskilled with Hibernate to implement an @ManyToMany association mapping that generates anything other than a left outer join. This is unfortunately against a fairly large table, which is yieldinging unacceptable performance. Nothing I have tried changes the underlying query to be an inner join, which I believe should be the sort of SQL generated in this case. Here are my specifics:

Hibernate version:
Hibernate Core 3.2.4.SP1
Hibernate Annotations 3.3.0.GA

Name and version of the database you are using:
Micro$oft $QL-$erver 2005

My guesses at relevant mapping annotations
There are only 2 entities involved in the problem I am having, Function (which holds info for a Functional Area of our company) and HrProfile (which holds personal information is hydrated from a legacy table maintained by a non-hibernate-using team). HrProfile is an immutable hombre and he doesn't have any associations to Function. Function, however has the notion of default owners of that function, which I chose to implement as a unidirectional Many-To-Many with HrProfile.

The Function class is annotated as:

Code:

@Entity
@Table( name = "Functions" )
public class Function implements Dumpable, SimpleAuditAware

So no surprises, there. The property of Function that implements the association is:

Code:

   @ManyToMany( cascade = CascadeType.ALL, fetch = FetchType.LAZY )
   @JoinTable( name = "FunctionsToDefaultOwners", 
      joinColumns = { @JoinColumn( name = "FunctionId", nullable = false ) }, 
      inverseJoinColumns = { @JoinColumn( name = "Emplid", nullable = false ) } )
   @OrderBy( "lastName" )
   private Set<HrProfile> getDefaultOwners()

Yes, I know the property is private, but I have a transient public property that exposes an immutable copy of the collection, so users of my class have to go thru the class to mutate the collection.

The generated SQL (show_sql=true):

Code:

select 
   defaultown0_.FunctionId as FunctionId1_, defaultown0_.Emplid as Emplid1_, 
   hrprofile1_.emplid as emplid0_0_, hrprofile1_.DeptName as DeptName0_0_, 
   hrprofile1_.DisplayFirstName as DisplayF3_0_0_, hrprofile1_.EmailAddress as EmailAdd4_0_0_, 
   hrprofile1_.EmplStatus as EmplStatus0_0_, hrprofile1_.LastName as LastName0_0_, 
   hrprofile1_.PreferredFirstName as Preferre7_0_0_, hrprofile1_.JobTitle as JobTitle0_0_, 
   hrprofile1_.WorkPhone as WorkPhone0_0_, hrprofile1_.FirstName as FirstName0_0_, 
   hrprofile1_.FirstNameSearch as FirstNa11_0_0_, hrprofile1_.Name as Name0_0_, 
   hrprofile1_.LastNameSearch as LastNam13_0_0_, hrprofile1_.SupervisorId as Supervi14_0_0_ 
from 
   FunctionsToDefaultOwners defaultown0_ 
      left outer join 
   Employees hrprofile1_ 
      on defaultown0_.Emplid=hrprofile1_.emplid 
where 
   defaultown0_.FunctionId=? 
order by 
   hrprofile1_.LastName asc

I originally didn't have the nullable annotation parameters in there for the @JoinColumns, and I truly thought that adding them would change the generated SQL from a left outer join to some sort of inner join, but this is not the case.

Can anybody out there share some wisdom on something that I have missed? Alternatively, can someone with experience with associating with fairly large legacy tables share some wisdom on a better strategy for implementing that sort of thing in Hibernate?

emmanuel · **Posted:** Thu Jul 26, 2007 1:38 pm

A collection can be empty, hence the outer join

RepublicanJew · **Joined:** Mon Feb 20, 2006 8:05 pm **Posts:** 8

Thanks for the reply, Emmanual.

Can you help me understand how a lazy-loaded @ManyToMany could require a left outer join in order to return an empty set? Would the ability for an empty set also put this same restriction on an @OneToMany? Would it apply equally to a @OneToMany where the many-side owns the accociation?

If there's no way around a left outer join with a @ManyToMany, do you have any suggestions on how I could avoid this problem by doing some other modelling technique?

Again, many thanks for your help.

emmanuel · **Posted:** Fri Jul 27, 2007 12:37 pm

Sorry I read too fast. This is strange indeed. Can you provide a minimal runnable test case? There might have been a regression in core or annotations.

RepublicanJew · **Joined:** Mon Feb 20, 2006 8:05 pm **Posts:** 8

Here are some test entities I created to try to isolate the issue. This first one is the easy side of my Many-to-many--it doesn't have any references to anything, hence its name:

Code:

@Entity
public class Referencee implements Dumpable
{
   private Log      log      = LogFactory.getLog( getClass() );

   private Long   id      = null;
   private String   name   = null;


   public Referencee()
   {
   }


   public Referencee( String name )
   {
      setName( name );
   }


   @Override
   public void dump()
   {
      log.info( "Referencee:" );
      log.info( "\tname:\t" + getName() );
      log.info( "" );
   }


   @Id
   public Long getId()
   {
      return id;
   }


   private void setId( Long id )
   {
      this.id = id;
   }


   public String getName()
   {
      return name;
   }


   public void setName( String name )
   {
      this.name = name;
   }
}

This next is the side that contains the mapped collection:

Code:

@Entity
public class Referencer implements Dumpable
{
   private Log            log      = LogFactory.getLog( getClass() );

   private Long         id      = null;
   private String         name   = null;
   private Set<Referencee>   set      = new HashSet<Referencee>();


   public Referencer()
   {
   }


   public Referencer( String name )
   {
      setName( name );
   }


   @Override
   public void dump()
   {
      log.info( "Referencer:" );
      log.info( "\tname:\t" + getName() );
      log.info( "" );
   }


   @Id
   @GeneratedValue( strategy = GenerationType.AUTO )
   public Long getId()
   {
      return id;
   }


   private void setId( Long id )
   {
      this.id = id;
   }


   public String getName()
   {
      return name;
   }


   public void setName( String name )
   {
      this.name = name;
   }


   @ManyToMany
   @JoinTable( joinColumns = { @JoinColumn( nullable = false ) }, inverseJoinColumns = { @JoinColumn( nullable = false ) } )
   public Set<Referencee> getSet()
   {
      return set;
   }


   public void setSet( Set<Referencee> set )
   {
      this.set = set;
   }

}

Here's a relevant TestNG method that I used to see the generated SQL:

Code:

public class ManyToManyTestInitializer extends ExampleHbnAwareTester
{
   private GenericDao<Referencee, Long> eeDao = null;
   private GenericDao<Referencer, Long> erDao = null;
   

   public ManyToManyTestInitializer()
   {
      super( false, false );
   }


   @Test
   public void initModel() throws Exception
   {
      HrProfile barry = new HrProfileDao().findById( "179453" );

      CurrentUserRegistry.set( barry );
      
      eeDao = new GenericDao<Referencee, Long>( Referencee.class );
      erDao = new GenericDao<Referencer, Long>( Referencer.class );
      
      if (eeDao.findAll().size() == 0)
         addEntries();
      
      for (Referencer er : erDao.findAll())
         for (Referencee e : er.getSet())
            e.dump();
   }
   
   
   private void addEntries()
   {
      Referencee eeOne = new Referencee( "Referencee One" );
      Referencee eeTwo = new Referencee( "Referencee Two" );
      Referencee eeThree = new Referencee( "Referencee Three" );
      
      eeDao.makePersistent( eeOne );
      eeDao.makePersistent( eeTwo );
      eeDao.makePersistent( eeThree );
      
      Referencer erOne = new Referencer( "Referencer 1" );
      Referencer erTwo = new Referencer( "Referencer 2" );
      Referencer erThree = new Referencer( "Referencer 3" );
      
      erOne.getSet().add( eeOne );
      erOne.getSet().add( eeTwo );
      erTwo.getSet().add( eeTwo );
      erTwo.getSet().add( eeThree );
      erThree.getSet().add( eeOne );
      erThree.getSet().add( eeThree );
      
      erDao.makePersistent( erOne );
      erDao.makePersistent( erTwo );
      erDao.makePersistent( erThree );
      
      HbnSessionUtil.commitAndBeginNewTransaction();
   }
}

I haven't included all my Hibernate plumbing classes, but I would think I wouldn't need to give that sort of thing to you!

If I add a class in between these 2 classes called Reference, alter Referencer to have a One-to-Many with Reference (rather than the above Many-to-Many directly with Referencee), and give Reference a Many-to-One with Referencee, then an inner join is created. You'd think that the above relationships and mapping annotations would have generated the same thing. I wouldn't be shocked if I were wrong, but if I am, then there's a connection under the surface that I'm probably naively missing.

Emmanuel, many thanks for taking the time to look at this.

emmanuel · **Posted:** Mon Jul 30, 2007 4:02 pm

Can you attach those classes to a JIRA issue, so that it's not lost.

RepublicanJew · **Joined:** Mon Feb 20, 2006 8:05 pm **Posts:** 8

I have created a JIRA entry here.

Thanks, Emmanuel.