如何使用JPA Repository高效获取各ID对应的最新日期行？

首先对应你的event表定义JPA实体类：

import jakarta.persistence.*;
import java.util.Date;

@Entity
@Table(name = "event")
public class Event {
    @Column(name = "otherTableId", nullable = false)
    private Long otherTableId;

    @Column(name = "updateDateTime", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date updateDateTime;

    // 空参构造、全参构造、getter/setter
    public Event() {}

    public Event(Long otherTableId, Date updateDateTime) {
        this.otherTableId = otherTableId;
        this.updateDateTime = updateDateTime;
    }

    public Long getOtherTableId() { return otherTableId; }
    public void setOtherTableId(Long otherTableId) { this.otherTableId = otherTableId; }
    public Date getUpdateDateTime() { return updateDateTime; }
    public void setUpdateDateTime(Date updateDateTime) { this.updateDateTime = updateDateTime; }
}

以下是几种最优实现方案，按需选择：

方案一：原生SQL直接复用原查询逻辑 #

完全贴合你提供的SQL，性能和原查询一致，适合复杂场景：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

public interface EventRepository extends JpaRepository<Event, Long> {

    @Query(value = """
            SELECT A.otherTableId, A.updateDateTime
            FROM event as A
            INNER JOIN (
                SELECT otherTableId, max(updateDateTime) as updateDateTime
                FROM event
                WHERE otherTableId IN :ids
                GROUP BY otherTableId) as B
            ON A.otherTableId = B.otherTableId AND A.updateDateTime = B.updateDateTime
            """, nativeQuery = true)
    List<Object[]> findLatestEventsByOtherTableIds(@Param("ids") List<Long> ids);
}

如果想返回结构化对象，可以定义一个DTO：

public class EventLatestDTO {
    private Long otherTableId;
    private Date updateDateTime;

    public EventLatestDTO(Long otherTableId, Date updateDateTime) {
        this.otherTableId = otherTableId;
        this.updateDateTime = updateDateTime;
    }

    // getter方法
    public Long getOtherTableId() { return otherTableId; }
    public Date getUpdateDateTime() { return updateDateTime; }
}

修改Repository的查询语句为：

@Query(value = """
        SELECT new com.yourpackage.EventLatestDTO(A.otherTableId, A.updateDateTime)
        FROM Event A
        INNER JOIN (
            SELECT e.otherTableId, max(e.updateDateTime) as updateDateTime
            FROM Event e
            WHERE e.otherTableId IN :ids
            GROUP BY e.otherTableId) B
        ON A.otherTableId = B.otherTableId AND A.updateDateTime = B.updateDateTime
        """)
List<EventLatestDTO> findLatestEventsByOtherTableIds(@Param("ids") List<Long> ids);

方案二：JPQL简洁写法（避免原生SQL） #

用JPQL的子查询实现相同逻辑，更贴合ORM风格：

@Query("""
        SELECT e FROM Event e
        WHERE e.updateDateTime = (
            SELECT max(e2.updateDateTime) FROM Event e2
            WHERE e2.otherTableId = e.otherTableId)
        AND e.otherTableId IN :ids
        """)
List<Event> findLatestEventsByOtherTableIds(@Param("ids") List<Long> ids);

这个写法逻辑和原SQL一致，依赖otherTableId的索引，性能表现优异。

方案三：窗口函数优化（MySQL 8.0+） #

如果你的MySQL版本在8.0及以上，用窗口函数的写法性能可能更优，尤其是单otherTableId下数据量较大的场景：

@Query(value = """
        SELECT otherTableId, updateDateTime
        FROM (
            SELECT otherTableId, updateDateTime,
                   ROW_NUMBER() OVER (PARTITION BY otherTableId ORDER BY updateDateTime DESC) rn
            FROM event
            WHERE otherTableId IN :ids) t
        WHERE rn = 1
        """, nativeQuery = true)
List<Object[]> findLatestEventsWithRowNumber(@Param("ids") List<Long> ids);

选择建议 #

• 要完全复现原SQL逻辑：选方案一
• 偏好ORM风格、避免原生SQL：选方案二
• MySQL版本支持且追求性能优化：选方案三

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