基于JPA/Hibernate通过关联表值进行数据筛选的教程

import javax.persistence.*;

@Entity
@Table(name = "queue")
public class Queue {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "queue_id")
    private Integer queueId;

    @ManyToOne
    @JoinColumn(name = "location_id", nullable = false)
    private Location location; // 关联Location实体

    @ManyToOne
    @JoinColumn(name = "queue_room_id", nullable = true)
    public QueueRoom queueRoom; // 关联QueueRoom实体

    // Getters and Setters...
    public Integer getQueueId() { return queueId; }
    public void setQueueId(Integer queueId) { this.queueId = queueId; }
    public Location getLocation() { return location; }
    public void setLocation(Location location) { this.location = location; }
    public QueueRoom getQueueRoom() { return queueRoom; }
    public void setQueueRoom(QueueRoom queueRoom) { this.queueRoom = queueRoom; }
}

// Location 和 QueueRoom 实体结构类似，包含一个 String uuid 字段
@Entity
@Table(name = "location")
class Location {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String uuid;
    // Getters and Setters...
    public String getUuid() { return uuid; }
    public void setUuid(String uuid) { this.uuid = uuid; }
}

@Entity
@Table(name = "queue_room")
class QueueRoom {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String uuid;
    // Getters and Setters...
    public String getUuid() { return uuid; }
    public void setUuid(String uuid) { this.uuid = uuid; }
}

SELECT q FROM Queue q
WHERE q.location.uuid = :locationUuid
AND q.queueRoom.uuid = :queueRoomUuid

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import java.util.List;

public class QueueRepository {

    private EntityManager entityManager; // 注入或获取EntityManager实例

    public QueueRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<Queue> getAllQueuesByLocationAndQueueRoom(String locationUuid, String queueRoomUuid) {
        String jpql = "SELECT q FROM Queue q " +
                      "WHERE q.location.uuid = :locationUuid " +
                      "AND q.queueRoom.uuid = :queueRoomUuid";

        TypedQuery<Queue> query = entityManager.createQuery(jpql, Queue.class);
        query.setParameter("locationUuid", locationUuid);
        query.setParameter("queueRoomUuid", queueRoomUuid);

        return query.getResultList();
    }
}

import javax.persistence.EntityManager;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Predicate;
import java.util.List;

public class QueueRepository {

    private EntityManager entityManager;

    public QueueRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<Queue> getAllQueuesByLocationAndQueueRoomCriteria(String locationUuid, String queueRoomUuid) {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Queue> criteria = builder.createQuery(Queue.class);
        Root<Queue> queueRoot = criteria.from(Queue.class); // 定义查询的根实体

        // 构建第一个条件：q.location.uuid = :locationUuid
        Predicate locationPredicate = builder.equal(
            queueRoot.get("location").get("uuid"), // 导航到 location 实体，再获取 uuid 属性
            locationUuid
        );

        // 构建第二个条件：q.queueRoom.uuid = :queueRoomUuid
        Predicate queueRoomPredicate = builder.equal(
            queueRoot.get("queueRoom").get("uuid"), // 导航到 queueRoom 实体，再获取 uuid 属性
            queueRoomUuid
        );

        // 使用 builder.and() 将两个条件组合起来
        criteria.where(builder.and(locationPredicate, queueRoomPredicate));

        return entityManager.createQuery(criteria).getResultList();
    }
}

import org.hibernate.Session;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.sql.JoinType; // 用于指定连接类型
import java.util.List;

public class QueueRepository {

    private Session currentSession; // 注入或获取Hibernate Session实例

    public QueueRepository(Session currentSession) {
        this.currentSession = currentSession;
    }

    @SuppressWarnings("unchecked")
    public List<Queue> getAllQueuesByLocationAndQueueRoomHibernateCriteria(String locationUuid, String queueRoomUuid) {
        Criteria criteria = currentSession.createCriteria(Queue.class, "q");

        // 创建别名并添加限制到根Criteria
        // 使用 createAlias 而不是 createCriteria 来避免创建子 Criteria 并确保所有限制都在根级别
        criteria.createAlias("q.location", "ql", JoinType.INNER_JOIN);
        criteria.createAlias("q.queueRoom", "qr", JoinType.INNER_JOIN);

        // 添加条件到根Criteria
        criteria.add(Restrictions.eq("ql.uuid", locationUuid));
        criteria.add(Restrictions.eq("qr.uuid", queueRoomUuid));

        // 假设有一个 includeVoidedObjects 方法，这里也加入
        // includeVoidedObjects(criteria, false); // 根据实际情况决定是否需要

        return criteria.list();
    }
}