如何用JPA的HQL或CriteriaQuery查询指定用户与各对话者的最后消息？

我来帮你把MySQL的查询逻辑转换成HQL和JPA CriteriaQuery的写法，针对你要查询指定用户与每个对话者的最后一条消息这个需求，咱们一步步来：

先回顾典型的MySQL实现（对应你提到的join+max写法） #

首先先明确你可能用到的MySQL查询逻辑，方便后续对比转换：

SELECT m.*
FROM messages m
JOIN (
    SELECT 
        CASE WHEN sender_id = :userId THEN receiver_id ELSE sender_id END AS interlocutor_id,
        MAX(sent_time) AS last_sent_time
    FROM messages
    WHERE sender_id = :userId OR receiver_id = :userId
    GROUP BY interlocutor_id
) AS last_msgs ON 
    (m.sender_id = :userId AND m.receiver_id = last_msgs.interlocutor_id AND m.sent_time = last_msgs.last_sent_time)
    OR (m.receiver_id = :userId AND m.sender_id = last_msgs.interlocutor_id AND m.sent_time = last_msgs.last_sent_time)

1. HQL写法 #

假设你的JPA实体类名为Message，属性对应数据库字段：id、senderId（对应sender_id）、receiverId（对应receiver_id）、sentTime（对应sent_time）、message，那么对应的HQL查询如下：

SELECT m
FROM Message m
JOIN (
    SELECT 
        CASE WHEN m2.senderId = :userId THEN m2.receiverId ELSE m2.senderId END AS interlocutorId,
        MAX(m2.sentTime) AS lastSentTime
    FROM Message m2
    WHERE m2.senderId = :userId OR m2.receiverId = :userId
    GROUP BY interlocutorId
) AS lastMsgs ON 
    (m.senderId = :userId AND m.receiverId = lastMsgs.interlocutorId AND m.sentTime = lastMsgs.lastSentTime)
    OR (m.receiverId = :userId AND m.senderId = lastMsgs.interlocutorId AND m.sentTime = lastMsgs.lastSentTime)

注意事项： #

• HQL里使用的是实体类的属性名，不是数据库列名，要和你的实体类定义保持一致；
• 执行时通过setParameter("userId", 你的目标用户ID)绑定参数；
• 如果你的实体中senderId/receiverId是关联的User实体（而非单纯的ID字段），需要调整为m2.sender.id这类写法。

2. JPA CriteriaQuery写法 #

如果需要用动态查询的CriteriaQuery实现，步骤如下（假设使用EntityManager）：

// 获取CriteriaBuilder
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
// 创建主查询，返回Message类型
CriteriaQuery<Message> cq = cb.createQuery(Message.class);
Root<Message> root = cq.from(Message.class);

// 第一步：构建子查询，获取每个对话者的最后发送时间
Subquery<Tuple> subquery = cq.subquery(Tuple.class);
Root<Message> subRoot = subquery.from(Message.class);

// 定义对话者字段：当发送方是目标用户时取接收方ID，反之取发送方ID
Expression<Long> interlocutor = cb.selectCase()
    .when(cb.equal(subRoot.get("senderId"), cb.parameter(Long.class, "userId")), subRoot.get("receiverId"))
    .otherwise(subRoot.get("senderId"));

// 子查询选择对话者ID和对应最后发送时间
subquery.select(cb.tuple(
    interlocutor.alias("interlocutorId"),
    cb.max(subRoot.get("sentTime")).alias("lastSentTime")
))
// 子查询过滤条件：目标用户是发送方或接收方
.where(
    cb.or(
        cb.equal(subRoot.get("senderId"), cb.parameter(Long.class, "userId")),
        cb.equal(subRoot.get("receiverId"), cb.parameter(Long.class, "userId"))
    )
)
// 按对话者分组，得到每个对话者的最后发送时间
.groupBy(interlocutor);

// 第二步：主查询关联子查询的条件
Predicate joinCondition = cb.or(
    // 情况1：当前消息是目标用户发送给对话者的，且时间匹配最后发送时间
    cb.and(
        cb.equal(root.get("senderId"), cb.parameter(Long.class, "userId")),
        cb.equal(root.get("receiverId"), subquery.get("interlocutorId")),
        cb.equal(root.get("sentTime"), subquery.get("lastSentTime"))
    ),
    // 情况2：当前消息是对话者发送给目标用户的，且时间匹配最后发送时间
    cb.and(
        cb.equal(root.get("receiverId"), cb.parameter(Long.class, "userId")),
        cb.equal(root.get("senderId"), subquery.get("interlocutorId")),
        cb.equal(root.get("sentTime"), subquery.get("lastSentTime"))
    )
);

// 组装主查询
cq.select(root)
.where(joinCondition)
.distinct(true); // 防止同一对话者同一时间有多条消息时返回重复结果

// 执行查询
TypedQuery<Message> query = entityManager.createQuery(cq);
query.setParameter("userId", targetUserId);
List<Message> lastMessages = query.getResultList();

补充提示： #

• 如果你的Message实体中sender/receiver是关联的User实体，那么把subRoot.get("senderId")改成subRoot.get("sender").get("id")即可；
• 如果你用的是Spring Data JPA，也可以直接把上面的HQL放到@Query注解里，简化实现：

@Query(value = "SELECT m FROM Message m JOIN (SELECT CASE WHEN m2.senderId = :userId THEN m2.receiverId ELSE m2.senderId END AS interlocutorId, MAX(m2.sentTime) AS lastSentTime FROM Message m2 WHERE m2.senderId = :userId OR m2.receiverId = :userId GROUP BY interlocutorId) AS lastMsgs ON (m.senderId = :userId AND m.receiverId = lastMsgs.interlocutorId AND m.sentTime = lastMsgs.lastSentTime) OR (m.receiverId = :userId AND m.senderId = lastMsgs.interlocutorId AND m.sentTime = lastMsgs.lastSentTime)")
List<Message> findLastMessagesPerInterlocutor(@Param("userId") Long userId);

内容的提问来源于stack exchange，提问作者Vadym Borys