Spring Data JPA优化查询：获取Rating时仅加载Player ID而非完整实体

我在Spring Data JPA中配置了Player和Rating实体，需求是根据用户ID查询其所有Rating数据，但不想为每条Rating加载完整的Player实体，仅需获取Player ID（或无需Player相关额外数据），如同数据库存储的形式。目前我在查询后映射为DTO，但认为这种方式不够优化，请问能否通过自定义查询或投影等方式实现，避免不必要的实体加载以提升性能？

实体定义 #

Rating 实体 #

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Rating {

    @EmbeddedId
    private RatingId ratingId;

    @ManyToOne
    @MapsId("playerId")
    @NotNull
    private Player player;

    @ManyToOne
    @MapsId("gameId")
    @NotNull
    private Game game;

    private float points;

    @ManyToOne
    private Platform platform;

    private Long startDate;

    private Long endDate;

    private Status status;

    public Rating(Player player, Game game) {
        this.ratingId = new RatingId(player.getId(), game.getId());
        this.player = player;
        this.game = game;
    }
}

Player 实体 #

@Entity
@EqualsAndHashCode
@NoArgsConstructor
@Getter
@Setter
public class Player {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Setter(AccessLevel.NONE)
    @Column(updatable = false)
    private Long id;

    @NotBlank
    @Size(max = 20)
    @Column(unique = true)
    private String username;

    @NotBlank
    @Size(max = 50)
    @Email
    @Column(unique = true)
    private String email;

    @NotBlank
    @Size(max = 120)
    private String password;

    @EqualsAndHashCode.Exclude
    private Set<Role> roles = new HashSet<>();

    public Player(String username, String email, String password) {
        this.username = username;
        this.email = email;
        this.password = password;
    }
}

RatingId 嵌入式ID #

@Embeddable
@NoArgsConstructor
@AllArgsConstructor
public class RatingId implements Serializable {

    private Long playerId;
    private Long gameId;
}

现有Repository #

@Repository
public interface RatingRepository extends JpaRepository<Rating, RatingId> {
    List<Rating> findAllByPlayerId(Long playerId);
}

数据库直接查询结果 #

database=# select * from rating where rating.player_id = 1;

 end_date | points | start_date | status | player_id | game_id | platform_id
----------+--------+------------+--------+-----------+---------+------------- 
          |    1.5 |            |      0 |         1 |    9174 |           6

下面提供几种无需加载完整Player实体的优化方案，按性能和易用性排序：

1. 使用Spring Data Projections（投影） #

创建一个仅包含所需字段的投影接口，Spring Data会自动生成只查询指定字段的SQL，完全避免加载Player实体。

步骤1：定义投影接口 #

public interface RatingProjection {
    // Rating核心字段
    Float getPoints();
    Long getStartDate();
    Long getEndDate();
    Status getStatus();
    // 直接从嵌入式ID获取Player/Game ID，无需关联实体
    Long getPlayerId();
    Long getGameId();
    // 如需Platform ID
    Long getPlatformId();
}

步骤2：修改Repository方法 #

@Repository
public interface RatingRepository extends JpaRepository<Rating, RatingId> {
    List<RatingProjection> findAllByPlayerId(Long playerId);
}

优势：无需手写SQL，自动生成最优查询，仅选择需要的字段，性能最高。

2. 自定义JPQL查询（映射到DTO或Map） #

通过JPQL明确指定查询字段，完全控制SQL逻辑，避免关联Player表。

方式A：映射到DTO #

先定义DTO类：

public class RatingDto {
    private Long playerId;
    private Long gameId;
    private Float points;
    private Long startDate;
    private Long endDate;
    private Status status;
    private Long platformId;

    // 构造方法参数顺序需与JPQL字段顺序一致
    public RatingDto(Long playerId, Long gameId, Float points, Long startDate, Long endDate, Status status, Long platformId) {
        this.playerId = playerId;
        this.gameId = gameId;
        this.points = points;
        this.startDate = startDate;
        this.endDate = endDate;
        this.status = status;
        this.platformId = platformId;
    }

    // Getter方法
    public Long getPlayerId() { return playerId; }
    // 其他Getter省略
}

然后在Repository中添加查询方法：

@Query("SELECT new com.yourpackage.RatingDto(" +
       "r.ratingId.playerId, r.ratingId.gameId, r.points, r.startDate, r.endDate, r.status, r.platform.id) " +
       "FROM Rating r WHERE r.ratingId.playerId = :playerId")
List<RatingDto> findRatingsByPlayerId(@Param("playerId") Long playerId);

方式B：返回Map #

如果不需要DTO，也可以直接返回Map：

@Query("SELECT r.ratingId.playerId as playerId, r.ratingId.gameId as gameId, r.points, r.startDate, r.endDate, r.status, r.platform.id as platformId " +
       "FROM Rating r WHERE r.ratingId.playerId = :playerId")
List<Map<String, Object>> findRatingsByPlayerId(@Param("playerId") Long playerId);

3. 修改关联的FetchType为LAZY（保留Rating实体） #

默认@ManyToOne的FetchType是EAGER，改为LAZY后，Player会被初始化为代理对象，不会触发实际查询。你可以通过嵌入式ID直接获取Player ID，无需访问关联的Player实体。

修改Rating实体的Player关联 #

@ManyToOne(fetch = FetchType.LAZY)
@MapsId("playerId")
@NotNull
private Player player;

使用原有Repository方法 #

List<Rating> findAllByPlayerId(Long playerId);

注意：避免在事务上下文之外访问rating.getPlayer()的其他属性，否则会抛出懒加载异常。直接通过rating.getRatingId().getPlayerId()获取ID即可。

4. 原生SQL查询 #

如果需要完全匹配数据库原生查询逻辑，可以使用原生SQL：

@Query(value = "SELECT player_id, game_id, points, start_date, end_date, status, platform_id " +
               "FROM rating WHERE player_id = :playerId", nativeQuery = true)
List<Object[]> findRatingsByPlayerIdNative(@Param("playerId") Long playerId);

返回的Object[]中元素顺序与SQL字段顺序一致，可手动映射到DTO或直接使用。

• 若无需完整Rating实体：优先选择投影（方案1）或JPQL构造DTO（方案2），性能最优，仅查询所需字段。
• 若需保留Rating实体：选择方案3（懒加载+嵌入式ID），但需注意避免触发懒加载。

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