mysql - 具有连接和分页功能的 eclipselink

Question

目前我一直在通过 eclipselink 为以下关系模式生成查询。

经理可以有零个或多个员工。(经理可以在没有任何员工的情况下存在)

员工将只属于一位经理。(员工始终映射到经理)

public class Manager {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private String id;    

    @OneToMany(mappedBy = "manager")
    private List<Employee> employee;

    private String department;
}

public class Employee {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private String id;

    @ManyToOne
    @JoinColumn(name="managerId")
    private Manager manager;

    private String status;
}

如何在 eclipselink 中形成包含经理或员工或两者的 where 条件的左外连接查询？

例如:

SELECT m.id FROM Manager m LEFT OUTER JOIN Employee e ON m.id = e.managerId
WHERE e.id is null or m.department like 'Dev%' AND e.status = 'ACTIVE'
GROUP BY m.id
ORDER BY m.id
LIMIT 10

Answer 1

JPQL查询几乎是一样的。您只是不使用表而是使用实体:

SELECT m FROM Manager m
  LEFT JOIN m.employee e

ON 条件已在映射注释中定义，因此将被跳过。您还可以使用“LEFT JOIN FETCH”通过惰性关系获取对象。另请检查https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#LEFT_JOIN

您可以在实体中将 JPQL 查询指定为命名查询:

@Entity
...
@NamedQueries({
@NamedQuery(
        name="findAllEmployeesOrderById",
        query="SELECT e FROM Employee e order by e.id"
),
@NamedQuery(
        name="findAllEmployeesJoinAddressPhones",
        query="SELECT e FROM Employee e",
        hints={
                @QueryHint(name=QueryHints.FETCH, value="e.address"),
                @QueryHint(name=QueryHints.FETCH,
                           value="e.phoneNumbers")
        }
)
})
...
public class MyEntity { ...