CODEWITHSUNDEEP
javasqlhibernatejpa
user3157090
user3157090

Reputation: 537

JPA one to many relationship query

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>

Upvotes: 5

Views: 22013

Answers (4)

ikettu
ikettu

Reputation: 1203

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;

Upvotes: 2

Tasos P.
Tasos P.

Reputation: 4124

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

Upvotes: 4

zawhtut
zawhtut

Reputation: 8561

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

Upvotes: 2

Kevin Bowersox
Kevin Bowersox

Reputation: 94489

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;

Upvotes: 2

Related Questions