Spring Data JPA按多列分组查询同实体嵌套集合及分页过滤问题

需求：在Spring Data JPA中，从同一张表按artist和pickup两列分组，获取每个分组对应的吉他集合，同时支持分页和过滤功能。

示例数据表：

尝试的代码：

@Entity
public class Guitar {

   @Id
   private Long id;

   @Column
   private String name;

   @Column
   private String pickup;
}

@Subselect("SELECT DISTINCT artist, name, pickup FROM rocker")
public class ArtistGuitar {
   @Id
   private String artist;

   private String pickup;

   @JoinColumns({
      @JoinColumn(name = "artist"),
      @JoinColumn(name= "pickup")
   })
   @OneToMany
   private List<Guitar> guitars;

}

遇到的错误：

A Foreign key refering...has the wrong number of columns

期望返回的JSON格式：

[
    {
         "artist": "Eric Clapton",
         "pickup": "humbucker",
         "guitars": [
            {"id": 1, "name": "Les Paul"}
         ]
         
    },
    {
         "artist": "Eric Clapton",
         "pickup": "single coil",
         "guitars": [
            {"id": 2, "name": "Stratocaster"},
            {"id": 3, "name": "Telecaster"}
         ]

    },
    {
         "artist": "Paul McCartney",
         "pickup": "single coil",
         "guitars": [
            {"id": 4, "name": "Gretsch"}
         ]

    }
]

问题分析 #

原代码存在三个核心问题：

1. Guitar实体缺少artist字段，导致关联查询时找不到对应列；
2. ArtistGuitar的主键仅设置了artist，但分组键是artist+pickup的复合键，不符合JPA主键规范；
3. @OneToMany关联方式错误：Guitar的主键是id，无法直接用artist和pickup作为外键建立关联，外键必须对应主键列。

解决方案 #

1. 修正Guitar实体 #

补全Guitar实体的artist字段，确保和数据表结构一致：

@Entity
@Table(name = "rocker")
public class Guitar {
    @Id
    private Long id;
    private String name;
    private String artist;
    private String pickup;

    // 构造器、Getter、Setter
}

2. 用DTO投影+手动分组实现需求 #

JPA直接返回嵌套集合的分组结果较为繁琐，推荐先查询分组键，再批量获取每个分组对应的吉他列表，这种方式天然支持分页和过滤：

步骤1：定义分组键DTO

public class GroupKey {
    private String artist;
    private String pickup;

    public GroupKey(String artist, String pickup) {
        this.artist = artist;
        this.pickup = pickup;
    }

    // Getter方法
    public String getArtist() { return artist; }
    public String getPickup() { return pickup; }
}

步骤2：定义最终返回结果DTO

public class ArtistPickupGuitarResult {
    private String artist;
    private String pickup;
    private List<Guitar> guitars;

    public ArtistPickupGuitarResult(String artist, String pickup, List<Guitar> guitars) {
        this.artist = artist;
        this.pickup = pickup;
        this.guitars = guitars;
    }

    // Getter方法
}

步骤3：实现Repository查询

public interface GuitarRepository extends JpaRepository<Guitar, Long> {

    // 查询所有分组键，支持分页
    @Query("SELECT new com.example.dto.GroupKey(g.artist, g.pickup) FROM Guitar g GROUP BY g.artist, g.pickup")
    Page<GroupKey> findGroupKeys(Pageable pageable);

    // 带过滤条件的分组键查询（比如按artist模糊匹配）
    @Query("SELECT new com.example.dto.GroupKey(g.artist, g.pickup) FROM Guitar g WHERE g.artist LIKE %:artist% GROUP BY g.artist, g.pickup")
    Page<GroupKey> findGroupKeysWithFilter(@Param("artist") String artist, Pageable pageable);

    // 根据分组键批量查询吉他
    @Query("SELECT g FROM Guitar g WHERE (g.artist, g.pickup) IN :groupKeys")
    List<Guitar> findByGroupKeys(@Param("groupKeys") List<GroupKey> groupKeys);
}

步骤4：业务层组装结果（优化N+1查询）

@Service
public class GuitarService {
    @Autowired
    private GuitarRepository guitarRepository;

    public Page<ArtistPickupGuitarResult> getGroupedGuitars(Pageable pageable) {
        Page<GroupKey> groupKeysPage = guitarRepository.findGroupKeys(pageable);
        List<GroupKey> groupKeys = groupKeysPage.getContent();
        
        // 批量查询所有分组对应的吉他，避免N+1查询
        List<Guitar> allGuitars = guitarRepository.findByGroupKeys(groupKeys);
        
        // 按分组键分组吉他
        Map<GroupKey, List<Guitar>> guitarMap = allGuitars.stream()
                .collect(Collectors.groupingBy(g -> new GroupKey(g.getArtist(), g.getPickup())));
        
        // 组装最终结果
        List<ArtistPickupGuitarResult> results = groupKeys.stream()
                .map(key -> new ArtistPickupGuitarResult(
                        key.getArtist(),
                        key.getPickup(),
                        guitarMap.getOrDefault(key, Collections.emptyList())
                )).collect(Collectors.toList());
        
        return new PageImpl<>(results, pageable, groupKeysPage.getTotalElements());
    }

    // 带过滤的分组查询
    public Page<ArtistPickupGuitarResult> getGroupedGuitarsWithFilter(String artist, Pageable pageable) {
        Page<GroupKey> groupKeysPage = guitarRepository.findGroupKeysWithFilter(artist, pageable);
        List<GroupKey> groupKeys = groupKeysPage.getContent();
        
        List<Guitar> allGuitars = guitarRepository.findByGroupKeys(groupKeys);
        Map<GroupKey, List<Guitar>> guitarMap = allGuitars.stream()
                .collect(Collectors.groupingBy(g -> new GroupKey(g.getArtist(), g.getPickup())));
        
        List<ArtistPickupGuitarResult> results = groupKeys.stream()
                .map(key -> new ArtistPickupGuitarResult(
                        key.getArtist(),
                        key.getPickup(),
                        guitarMap.getOrDefault(key, Collections.emptyList())
                )).collect(Collectors.toList());
        
        return new PageImpl<>(results, pageable, groupKeysPage.getTotalElements());
    }
}

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