如何在JPA中实现Student与Teacher基于双条件OR的左连接查询

1. JPQL 实现 #

直接在JPQL中复现原生SQL的连接逻辑，支持OR关联条件：

// 基础查询，返回Student和Teacher的数组
String jpql = "SELECT s, t FROM Student s LEFT JOIN Teacher t ON s.teacher = t.badgeCode OR s.teacher = t.name";
TypedQuery<Object[]> query = entityManager.createQuery(jpql, Object[].class);
List<Object[]> results = query.getResultList();

// 带FETCH的查询，一次性加载关联的Teacher，避免N+1查询
String fetchJpql = "SELECT s FROM Student s LEFT JOIN FETCH Teacher t ON s.teacher = t.badgeCode OR s.teacher = t.name";
TypedQuery<Student> fetchQuery = entityManager.createQuery(fetchJpql, Student.class);
List<Student> students = fetchQuery.getResultList();

2. Criteria Query 实现 #

适合动态构建查询的场景，通过API自定义连接条件：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> cq = cb.createQuery(Student.class);
Root<Student> studentRoot = cq.from(Student.class);

// 创建左连接，并指定自定义OR条件
Join<Student, Teacher> teacherJoin = studentRoot.join("teacher", JoinType.LEFT);
Predicate joinCondition = cb.or(
    cb.equal(studentRoot.get("teacher"), teacherJoin.get("badgeCode")),
    cb.equal(studentRoot.get("teacher"), teacherJoin.get("name"))
);
teacherJoin.on(joinCondition);

cq.select(studentRoot);
TypedQuery<Student> query = entityManager.createQuery(cq);
List<Student> students = query.getResultList();

3. 注解方式（Hibernate 专有） #

如果使用Hibernate作为JPA实现，可以通过@JoinCondition注解直接在实体类中定义关联规则：

@Entity
public class Student {
    @Id
    private Long id;
    private String name;
    private String teacher; // 存储教师姓名或工号

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinCondition(clause = "student.teacher = teacher.badge_code OR student.teacher = teacher.name")
    private Teacher teacherEntity;

    // getter、setter 方法
}

注意：该注解是Hibernate扩展，不兼容其他JPA实现，若需跨框架兼容，优先选择前两种方案。

内容的提问来源于stack exchange，提问作者Luciano Marti