JPA one to many relationship query

Question

Implemented one to many relationship and it is working fine.

My issue is when i run the below query, if the table has 100 employee rows, and each employee has 2 departments. The database query is called 101 times, because for each employee it is calling department query, it is taking very long to complete calling all hundred rows, can any one suggest any alternative solution?

Please see the details below

Queries it is calling:

    First query is :    SELECT * FROM Employee e

    Next 100 queries : SELECT * FROM DEPARTMENT d WHERE d.EmployeeId=?

JPA Database call :

    javax.persistence.Query query = em.createNamedQuery("SELECT * FROM Employee e", Employee.class);

    return query.getResultList();




    import javax.persistence.CascadeType;
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.Id;
    import javax.persistence.NamedNativeQueries;
    import javax.persistence.NamedNativeQuery;
    import javax.persistence.OneToMany;
    import javax.persistence.Table;

    @Entity
    @Table(name = "EMPLOYEE")
    public class Employee implements Serializable
    {
        @Id
        @Column(name = "EmployeeId")
        String employeeId;

        @OneToMany(mappedBy = "employee", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
        private List<Department> departments;

        public List<Department> getDepartments() {
            return departments;
        }

        public void setDepartments(List<Department> departments) {
            this.departments = departments;
        }

        public String getEmployeeId() {
            return employeeId;
        }

        public void setEmployeeId(String employeeId) {
            this.employeeId = employeeId;
        }
    }

    @Entity
    @Table(name = "DEPARTMENT")
    public class Department implements Serializable
    {
        private static final long serialVersionUID = 1L;

        @Id
        @Column(name = "DepartmentID")
        String departmentId;

        @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name = "EmployeeId", insertable = false, updatable = false)
        private Employee employee;
    }

The output xml:

        <Employees>
            <Employee>
                <name>Rob</name>
                <Departments>
                    <Departmnet><id>1</id></Departmnet>
                    <Departmnet><id>2</id></Departmnet>
                </Departments>  
            </Employee>
            <Employee>
                <name>Sam</name>
                <Departments>
                    <Departmnet><id>1</id></Departmnet>
                    <Departmnet><id>2</id></Departmnet>
                </Departments>  
            </Employee>
        </Employees>

@PhilippSander: are you perhaps referring to a fetch join? This is JPA after all, the persistence provider does the joining for you. — Gimby, Mar 06 '14 at 10:12

score 4 · Accepted Answer · edited May 23 '17 at 11:58

4

This is a typical N+1 selects issue. I usually solve this with JOIN FETCH queries as described here and here

edited May 23 '17 at 11:58

Community

1
1

answered Mar 06 '14 at 10:24

Tasos P.

3,994
2
21
41

score 2 · Answer 2 · answered Mar 06 '14 at 10:09

2

You could switch the fetchtype to lazy, which will cause the departments only to be queried when necessary.

@OneToMany(mappedBy = "employee", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Department> departments;

answered Mar 06 '14 at 10:09

Kevin Bowersox

93,289
19
159
189

Actually that's the default fetch type for a oneToMany mapping, so its actually an act of not attempting to override what the JPA API designers deemed a good idea :) – Gimby Mar 06 '14 at 10:11
Hi thank you for your the answer, i have used the lazy also, but still loads 100 queries. I want mainly alternative to what i have implemented. – user3157090 Mar 06 '14 at 10:19

score 2 · Answer 3 · answered Mar 06 '14 at 10:15

2

Change FetchType.EAGER to FetchType.LAZY. Load the departments only when you need them which is looping the employee.getDepartmentList() for example

for(Department dept:employeeGetDepartmentList()){
 dept.getId();
}

before using departments

answered Mar 06 '14 at 10:15

zawhtut

8,335
5
52
76

1

Hi thank you for your the answer, i have used the lazy also, but still loads 100 queries. I want mainly alternative to what i have implemented. – user3157090 Mar 06 '14 at 10:21

score 2 · Answer 4 · answered Jan 14 '15 at 12:07

Classic N+1 problem. You can reduce number of queries with Batch Fetching which just combines many lazy sql clauses to single one.

For Example:

@OneToMany(mappedBy = "employee", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@BatchSize(size=10)
private List<Department> departments;

JPA one to many relationship query

4 Answers4