JPA Hibernate n+1 issue (Lazy & Eager Diff)

Question

I am trying to understand n+1 problem and thus find the proper fix.

I have two Entities: Company

@Entity
@Table(name="company")
public class Company implements Serializable {

    private static final long serialVersionUID = 1L;


    @Id
    @GeneratedValue
    private int id;

    @Column(name="cmp_id")
    private int cmpId;

    @Column(name="company_name")
    private String companyName;

    @OneToMany(fetch=FetchType.LAZY)
    @JoinColumn(name="cmp_id",referencedColumnName="cmp_id")
    private Set<Employee> employee;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getCmpId() {
        return cmpId;
    }

    public void setCmpId(int cmpId) {
        this.cmpId = cmpId;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Set<Employee> getEmployee() {
        return employee;
    }

    public void setEmployee(Set<Employee> employee) {
        this.employee = employee;
    }



}

Employee

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

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private int id;

    @Column(name="emp_id")
    private int empId;

    @Column(name="emp_name")
    private String empName;

    /*@ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="cmp_id", referencedColumnName="cmp_id")
    @JsonIgnore
    private Company company;*/

    @Column(name="cmp_id")
    private int cmpId;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getEmpId() {
        return empId;
    }

    public void setEmpId(int empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

}

Each company have many employee. So simple UNI-DIRECTIONAL One to Many Relationship. Now when i am running query ("Select a from Company a"), i am facing n+1 selects(when i am trying to get employee)

But to understand the concepts more clearly, when i am changing it to EAGER, all the same n+1 queries are running initially(even when I did not fetch the employee). Is it the right behaviour? I mean shouldn't it fire a join query instead. Also how do I change code with EAGER to result in only 1 query.?

Answer 1

The "issue" is not really an issue, it's about how ORMs work. Hibernate creates so called nested queries if you access an association like that.

You are right, in both cases the same queries are executed, switching the FetchType of your mapping from LAZY to EAGER is only scheduling the execution of the additional (n+1) queries.

Assume you have many companies, all of them have employees, in both cases queries like that are executed (at least once):

select ... from company company0_

select ... from employee employee0_ where employee0_.cmp_id=?

The first one is executed to get all companies, the second one for every company once.

E.g.: 3 companies (N) with many employees will execute the first select once and three nested selects = 3+1 = 4 queries in sum.

The difference between the EAGER and LAZY is just about the point in time, you can't avoid database access in general because you nevertheless need the data. With LAZY the additional query is just postponed until you iterate the employee collection(s). But keep in mind that it's just a hint, not every database driver is supporting lazy loading.

If you really know that you always need the data you can write a FETCH JOIN query and receive all needed data in one shot:

Select c from Company c JOIN FETCH c.employee e

That will execute a query like:

select ... from company company0_ inner join employee employee1_ on company0_.cmp_id=employee1_.cmp_id

That will avoid the second database access. To verify that in your tests, maybe the datasource proxy project is something for you.