JPA one to many relationship query

user3157090

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>
Tasos P.

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

hql select query on one to many relationship in spring jpa

From Dev

JPA criteria query in a many-to-many relationship

From Dev

Query One to Many Relationship SQLAlchemy

From Dev

Django query in One to Many relationship

From Dev

Django one to many Relationship Query

From Dev

Query One to Many Relationship SQLAlchemy

From Dev

CoreData: Query to one-to-many-to-many relationship

From Dev

ios parse one query many to many relationship

From Dev

many to one relationship in spring hibernate jpa

From Dev

JPA update list of one to many relationship

From Dev

How to describe one to many relationship (JPA/HIbernate)

From Dev

Spring JPA - Inheritance in One-to-Many relationship

From Dev

JPA many-to-one relationship CascadeType behavior

From Dev

JPA criteria query in a many-to-many relationship using IN operator

From Dev

How to query a one to many/many to many relationship in Flask SQL Alchemy?

From Dev

Is there a way to simplify a Linq query with a many to one relationship?

From Dev

Criteria query for unidirectional one-to-many relationship

From Dev

jooq single query with one to many relationship

From Dev

Data base One To Many Relationship Query

From Dev

Data base One To Many Relationship Query

From Dev

How to query one-to-many relationship in JPQL?

From Dev

Is there a way to simplify a Linq query with a many to one relationship?

From Dev

query regarding one to many relationship in hibernate

From Dev

Criteria query for unidirectional one-to-many relationship

From Dev

Query two tables with one to many relationship

From Dev

Ruby on Rails Query a one to many relationship

From Dev

Spring Data JPA Specification using CriteriaBuilder with a one to many relationship

From Dev

Postgres Managing more than one one to many relationship in query

From Dev

Django: Query one to many relationship for existing relationship with certain attributes in the many-model

Related Related

  1. 1

    hql select query on one to many relationship in spring jpa

  2. 2

    JPA criteria query in a many-to-many relationship

  3. 3

    Query One to Many Relationship SQLAlchemy

  4. 4

    Django query in One to Many relationship

  5. 5

    Django one to many Relationship Query

  6. 6

    Query One to Many Relationship SQLAlchemy

  7. 7

    CoreData: Query to one-to-many-to-many relationship

  8. 8

    ios parse one query many to many relationship

  9. 9

    many to one relationship in spring hibernate jpa

  10. 10

    JPA update list of one to many relationship

  11. 11

    How to describe one to many relationship (JPA/HIbernate)

  12. 12

    Spring JPA - Inheritance in One-to-Many relationship

  13. 13

    JPA many-to-one relationship CascadeType behavior

  14. 14

    JPA criteria query in a many-to-many relationship using IN operator

  15. 15

    How to query a one to many/many to many relationship in Flask SQL Alchemy?

  16. 16

    Is there a way to simplify a Linq query with a many to one relationship?

  17. 17

    Criteria query for unidirectional one-to-many relationship

  18. 18

    jooq single query with one to many relationship

  19. 19

    Data base One To Many Relationship Query

  20. 20

    Data base One To Many Relationship Query

  21. 21

    How to query one-to-many relationship in JPQL?

  22. 22

    Is there a way to simplify a Linq query with a many to one relationship?

  23. 23

    query regarding one to many relationship in hibernate

  24. 24

    Criteria query for unidirectional one-to-many relationship

  25. 25

    Query two tables with one to many relationship

  26. 26

    Ruby on Rails Query a one to many relationship

  27. 27

    Spring Data JPA Specification using CriteriaBuilder with a one to many relationship

  28. 28

    Postgres Managing more than one one to many relationship in query

  29. 29

    Django: Query one to many relationship for existing relationship with certain attributes in the many-model

HotTag

Archive