解决JPA JOINED继承下多表关联查询子查询返回多行问题

错误原因 #

你原本的子查询没有和外层当前的blind记录绑定关联，当账号有多条屏蔽帖子时，子查询会返回所有post_blind关联的post.is_blind值，结果是多行数据，但AND条件需要单个布尔值做判断，所以触发了Subquery will return more than one row错误。

修正后的SQL写法 #

方案1：直接关联表查询 #

通过JOIN直接关联所有相关表，把筛选条件直接写在WHERE中，避免子查询的多行问题：

SELECT b.*
FROM blind b
LEFT JOIN post_blind pb ON b.id = pb.id
LEFT JOIN post p ON pb.post_id = p.id
LEFT JOIN comment_blind cb ON b.id = cb.id
LEFT JOIN comment c ON cb.comment_id = c.id
WHERE b.account_id = 2
AND (
    (b.type = 'POST' AND p.is_blind = 0)
    OR (b.type = 'COMMENT' AND c.is_blind = 0)
)

方案2：关联式子查询 #

如果偏好子查询写法，要确保子查询只针对当前blind记录返回单个判断结果：

SELECT *
FROM blind b
WHERE b.account_id = 2
AND (
    (b.type = 'POST' AND EXISTS (
        SELECT 1
        FROM post_blind pb
        JOIN post p ON pb.post_id = p.id
        WHERE pb.id = b.id AND p.is_blind = 0
    ))
    OR (b.type = 'COMMENT' AND EXISTS (
        SELECT 1
        FROM comment_blind cb
        JOIN comment c ON cb.comment_id = c.id
        WHERE cb.id = b.id AND c.is_blind = 0
    ))
)

JPA InheritanceType.JOINED 实现思路 #

1. 实体类定义 #

• 父类Blind（共用字段）：

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING)
public abstract class Blind {
    @Id
    private Long id;
    @Column(name = "account_id")
    private Long accountId;
    // 其他通用字段及getter/setter
}

• 子类PostBlind（关联帖子）：

@Entity
@DiscriminatorValue("POST")
@PrimaryKeyJoinColumn(name = "id")
public class PostBlind extends Blind {
    @OneToOne
    @JoinColumn(name = "post_id")
    private Post post;
    // getter/setter
}

• 子类CommentBlind（关联评论）：

@Entity
@DiscriminatorValue("COMMENT")
@PrimaryKeyJoinColumn(name = "id")
public class CommentBlind extends Blind {
    @OneToOne
    @JoinColumn(name = "comment_id")
    private Comment comment;
    // getter/setter
}

2. JPQL查询示例 #

利用JPA的类型判断和关联查询实现需求：

String jpql = "SELECT b FROM Blind b " +
              "LEFT JOIN PostBlind pb ON b.id = pb.id " +
              "LEFT JOIN pb.post p " +
              "LEFT JOIN CommentBlind cb ON b.id = cb.id " +
              "LEFT JOIN cb.comment c " +
              "WHERE b.accountId = :accountId " +
              "AND ( " +
              "    (TYPE(b) = PostBlind AND p.isBlind = false) " +
              "    OR (TYPE(b) = CommentBlind AND c.isBlind = false) " +
              ")";

TypedQuery<Blind> query = entityManager.createQuery(jpql, Blind.class);
query.setParameter("accountId", 2L);
List<Blind> result = query.getResultList();

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