Hibernate n+1 select with multiple table hierarchy

Question

I have next class structure

@Entity
@Table(name = "Company")
public class Company {
   @Id
   @GeneratedValue
   private Long id;

   @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
   @JoinColumn(name = "CompanyId")
   @Fetch(FetchMode.SUBSELECT)
   private Set<Departement> departements;
}

@Entity
@Table(name = "Departement")
public class Departement {
   @Id
   @GeneratedValue
   private Long id;

   @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
   @JoinColumn(name = "DepartementId")
   @Fetch(FetchMode.SUBSELECT)
   private Set<Employee> employees;
}

@Entity
@Table(name = "Employee")
public class Employee {
   @Id
   @GeneratedValue
   private Long id;

  // other fields and methods
}

On the application startup I need to fetch all the companies with the initialized internal collections. My database is big enough (1,5 M rows in the Company table). I need to solve n+1 selection issue to speed up data retrieval. The solution with fetch joins does not work in my case because the generated sql query returns enormous data set and even if I use scroll like this

Query query = session.createQuery(query);
query.setReadOnly(true);
// MIN_VALUE gives hint to JDBC driver to stream results
query.setFetchSize(Integer.MIN_VALUE);
ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);

it still consumes all my RAM because I cannot flush session or evict retrieved entities.

Another approach is to use subselects but when I do

@SuppressWarnings("unchecked")
List<Company> companies = session.createQuery("from Company").list();

for (Company c : companies) {
    for (Departement d : c.getDepartements()) {
        d.getEmployees();
    }
}

hibernate generates only 2 queries: one for Company table

select ... from Company company

and another for Department table

select ... from Departement departemen0_ 
where departemen0_.CompanyId in (select company0_.id from Company company0_)

and I still have to initialize employees collection from the Departement class separately.

Is there any way to retrieve all 3 tables with subselects? Or may be there is another way to retrieve big amount of data with the given structure?

Usually you add `@NamedEntityGraph(name = "Company.withDepartment", attributeNodes = @NamedAttributeNode("department"))` on the top of the class `Company` and `@NamedEntityGraph(name = "Department.withEmployee", attributeNodes = @NamedAttributeNode("employee"))` under on the top of the class `Department`. — LowLevel, Jun 23 '16 at 17:29
then you add to your named query (better you work with named queries) following code: `{yourEntityManager}.{yourNamedQuery}.setHint("javax.persistence.loadgraph", getEntityManager().createEntityGraph("Company.withDepartment")) .getResultList();` and `{yourEntityManager}.{yourNamedQuery}.setHint("javax.persistence.loadgraph", getEntityManager().createEntityGraph("Department.withEmployee")) .getResultList();` — LowLevel, Jun 23 '16 at 17:29
- For the `departments` I would write: `@OneToMany @JoinColumn(name = "companyId") private Set departements;` — LowLevel, Jun 23 '16 at 17:30
- For the `employees` I would write `@OneToMany @JoinColumn(name = "departementId") private Set employees;` — LowLevel, Jun 23 '16 at 17:31
- I would use a Filter to define entityManagers (`ThreadLocal — LowLevel, Jun 23 '16 at 17:32

score 0 · Answer 1 · answered Dec 04 '18 at 14:23

Your code are generating only two queries (for Company and Department) because your Employee query code (the third and desired one) are not being lazy fetched. So, just change:

d.getEmployees();

To:

d.getEmployees().size();

If you are dealing with this amount of data, SUBSELECT seems the best strategy for you:

JOIN: avoids the major issue of N+1 queries but it may retrieve data duplicated.

SUBSELECT: avoids N+1 too and doesn't duplicate data but it loads all the entities of the associated type into memory.

Hibernate n+1 select with multiple table hierarchy

1 Answers1