一对多关系查询

Question

实现了一对多的关系，它运行良好。

我的问题是当我运行下面的查询时，如果表有100个员工行，每个员工有两个部门。数据库查询被调用了101次，因为对每个员工都是调用部门查询，要完成调用全部100行需要很长时间，有没有人可以提出替代的解决方案？

    First query is :    SELECT * FROM Employee e

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

    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;
    }

输出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>

Answer 1

这是一个典型的N+1选择问题。我通常使用join fetch查询来解决这个问题，如下所述