PostgreSQL GROUP BY异常排查：单向关联实体映射报错

问题背景 #

我有两个JPA实体Album和AlbumCard，对应数据库表albums、album_cards和中间关联表album_album_cards，配置了单向@OneToMany关联。在将实体映射为Model时触发org.postgresql.util.PSQLException，提示列"cards0_.album_id"必须出现在GROUP BY子句或用于聚合函数中。调用List<AlbumCard>的任意方法都会触发该异常，添加@Transactional注解后问题仍未解决。

实体代码 #

@Entity
@Table(name = "albums")
public class Album {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String ownerId;
    private String name;
    private Boolean isPublic;

    @OneToMany(orphanRemoval = true)
    @JoinTable(
            name = "album_album_cards",
            joinColumns = @JoinColumn(name = "album_id"),
            inverseJoinColumns = @JoinColumn(name = "album_card_id"))
    private List<AlbumCard> cards;
}

@Entity
@Table(name = "album_cards")
public class AlbumCard {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Integer price;
    private String condition;
    private String design;
    private Integer count;
    private Long cardId;
    @UpdateTimestamp
    private LocalDate updated;

}

错误栈信息 #

2022-11-14 21:37:57.725 ERROR 18696 --- [nio-9999-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

org.postgresql.util.PSQLException: ERROR: column "cards0_.album_id" must appear in the GROUP BY clause or be used in an aggregate function

at ru.berserkdeck.albums.impl.mapper.AlbumMapper.albumCardListToAlbumPositionModelList(AlbumMapper.java:57) ~[classes/:na]
    at ru.berserkdeck.albums.impl.mapper.AlbumMapper.toModel(AlbumMapper.java:31) ~[classes/:na]
    at java.base/java.util.Optional.map(Optional.java:260) ~[na:na]
    at ru.berserkdeck.albums.impl.service.AlbumServiceImpl.getAlbum(AlbumServiceImpl.java:50) ~[classes/:na]

SQL执行日志 #

Hibernate: select album0_.id as id1_1_, album0_.is_public as is_publi2_1_, album0_.name as name3_1_, album0_.owner_id as owner_id4_1_ from albums album0_ where album0_.owner_id=? and album0_.id=?
Hibernate: select cards0_.album_id as album_id8_0_0_, cards0_.id as id1_0_0_, cards0_.id as id1_0_1_, cards0_.card_id as card_id2_0_1_, cards0_.condition as conditio3_0_1_, cards0_.count as count4_0_1_, cards0_.design as design5_0_1_, cards0_.price as price6_0_1_, cards0_.updated as updated7_0_1_ from album_cards cards0_ where cards0_.album_id=?

Mapper代码 #

51    protected List<AlbumPositionModel> albumCardListToAlbumPositionModelList(List<AlbumCard> list) {
52        if (list == null) {
53            return new ArrayList<>();
54        }
55
56        List<AlbumPositionModel> list1 = new ArrayList<>();
57        list.forEach(e -> list1.add(albumCardToAlbumPositionModel(e))); <---- 异常触发位置
58        return list1;

Service代码 #

@Override
    public Optional<AlbumModel> getAlbum(String ownerId, Long albumId) {
        if (ownerId != null) {
            return albumRepo
                    .findByOwnerIdAndId(ownerId, albumId)
                    .map(mapper::toModel);
        } else {
            return albumRepo
                    .findByIdAndIsPublic(albumId, true)
                    .map(mapper::toModel);
        }
    }

原因分析 #

从Hibernate生成的SQL日志可以看出核心问题：

• 加载AlbumCard集合时，Hibernate错误生成了直接查询album_cards表并过滤album_id的SQL，但album_cards表本身并不存在album_id字段（该字段属于中间关联表album_album_cards）。
• PostgreSQL默认开启ONLY_FULL_GROUP_BY严格模式，当SQL中出现未在GROUP BY中声明的非聚合列时（此处为错误引入的cards0_.album_id），就会抛出该异常。
• 添加@Transactional仅能延长持久化上下文生命周期，无法修正SQL生成逻辑的错误。

解决办法 #

1. 使用Fetch Join预加载集合（推荐） #

修改Repository中的查询方法，通过JOIN FETCH显式加载关联的cards集合，让Hibernate生成正确的关联查询，避免后续懒加载时的错误SQL：

// AlbumRepository.java
@Query("SELECT a FROM Album a JOIN FETCH a.cards WHERE a.ownerId = :ownerId AND a.id = :albumId")
Optional<Album> findByOwnerIdAndId(@Param("ownerId") String ownerId, @Param("albumId") Long albumId);

@Query("SELECT a FROM Album a JOIN FETCH a.cards WHERE a.id = :albumId AND a.isPublic = true")
Optional<Album> findByIdAndIsPublic(@Param("albumId") Long albumId);

2. 显式修正实体映射配置 #

在@JoinTable中明确指定关联的主键列，避免Hibernate自动推断错误：

@OneToMany(orphanRemoval = true)
@JoinTable(
        name = "album_album_cards",
        joinColumns = @JoinColumn(name = "album_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "album_card_id", referencedColumnName = "id")
)
private List<AlbumCard> cards;

3. 升级Hibernate版本 #

若使用较旧版本的Hibernate，可能存在单向@OneToMany+@JoinTable的SQL生成bug，升级到最新稳定版本（如5.6.x或6.x系列）可修复此类问题。

内容的提问来源于stack exchange，提问作者Артём Власов