Avoiding outer joins across tables when using join

问题:

Consider the following classes in a Spring Data JPA (+ Hibernate) application:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@Table(name = "person")
public class Person { }

@Entity
@Table(name = "customer")
public class Customer extends Person { }

@Entity
@Table(name = "employee")
public class Employee extends Person { }

@Entity
@Table(name = "manager")
public class Manager extends Employee { }

public interface IPersonRepository extends JpaRepository<Person, Long> { }
public interface ICustomerRepository extends JpaRepository<Customer, Long> { }
public interface IEmployeeRepository extends JpaRepository<Employee, Long> { }

My most common use case involves calling the following method (inherited from JpaRepository):

IPersonRepository.findAll();

Whenever this method is invoked, the following SQL query is issued by Hibernate:

select
    person0_.id as id1_3_,
    person0_.version as version2_3_,
    person0_.first_name as first3_3_,
    person0_.last_name as last4_3_,
    person0_1_.customer_code as customer1_0_,
    person0_2_.employee_code as employee1_1_,
    person0_2_.manager_id as manager3_1_,
    case
        when person0_3_.id is not null then 3
        when person0_1_.id is not null then 1
        when person0_2_.id is not null then 2
        when person0_.id is not null then 0
    end as clazz_
from
    person person0_
left outer join
    customer person0_1_
on person0_.id=person0_1_.id
left outer join
    employee person0_2_
on person0_.id=person0_2_.id
left outer join
    manager person0_3_
on person0_.id=person0_3_.id;

Whenever this query is executed, I am interested only in the common fields in the Person class, so I find the left outer joins useless.

The problem is that in our actual application, there are 8 child classes like Employee and Customer and millions of records in each child table, which are causing the query on the parent table to run very slow.

Is there a way to avoid the outer joins across the tables in this case? Please note that I have tried using the DiscriminatorColumn approach and the joins are still performed in that case (when using Hibernate). I have also tried the Hibernate-specific Polymorphism annotation on the entity classes in all possible combinations and still the outer joins are performed.

Spring Data JPA version: 1.2.0
Hibernate version: 4.2.1

回答1:

After many days of trying to solve this problem, I have come to the following conclusion:

1. There is no way to force Hibernate 4.x (and 3.x) to not perform the outer joins in this case.
2. There is no way to force the latest available versions of TopLink Essentials (v2.1-60) and OpenJPA (v2.2.2) to not perform the outer joins either.
3. It is possible to avoid the outer joins with the latest available version of EclipseLink (v2.5.0). However, EclipseLink requires a discriminator column for the class hierarchy shown above, even though Hibernate and OpenJPA do not. So far I have been unable to find a way to avoid using the discriminator column with EclipseLink.

I guess I will have to wait for either the JPA specification to change or a JPA implementation to become available that satisfies my current requirement.

回答2:

Not a direct answer to your question but:

You could replace this inheritance with @OneToOne composition.

The table structure would remain pretty much the same. You could query concrete types without the JOINs that you want avoid.

@Entity
@Table(name = "personDetails")
public class PersonDetails { }

@Entity
@Table(name = "customer")
public class Customer {

   @OneToOne
   PersonDetails personDetails;
 }

@Entity
@Table(name = "employee")
public class Employee {

   @OneToOne
   PersonDetails personDetails;

 }

@Entity
@Table(name = "manager")
public class Manager {

   @OneToOne
   PersonDetails personDetails;

 }

Just changed the name of Person to PersonDetails to have a better name in this context.