Spring JPA下检测用户是否已点赞/点踩评论的最优方案

实体类定义 #

Post 实体:

public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @OneToMany(mappedBy = "post")
    private List<PostComment> postComments;

    ...
}

PostComment 实体:

public class PostComment {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @ManyToOne
    @JoinColumn(name = "post_id")
    private Post post;
    
    @OneToMany(mappedBy = "postComment")
    private Set<PostCommentUpvote> postCommentUpvotes;

    @OneToMany(mappedBy = "postComment")
    private Set<PostCommentDownvote> postCommentDownvotes;

    ...
}

PostCommentUpvote 实体（PostCommentUpvote与PostCommentDownvote字段完全一致，用作计数器）:

public class PostCommentUpvote {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @ManyToOne
    @JoinColumn(name = "post_comment_id")
    private PostComment postComment;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    ...
}

所有关联均为双向关联，可通过注解确认。

需求目标 #

已认证用户对PostComment执行点赞/点踩操作时，需先检查该用户是否已对该评论进行过点赞/点踩操作。已知Post ID（非必需）和PostComment ID，且两者均已建立索引。

用户投票存在三种状态：

• 用户尚未对该评论点赞/点踩：执行新增点赞或点踩操作
• 用户已点赞，再次点赞则取消点赞；已点踩再次点踩则取消点踩
• 用户已点赞，点踩则取消点赞并新增点踩；反之亦然

最优实现方案 #

方案选择：优先用针对性SQL查询，而非遍历集合 #

通过PostComment ID查询实体后遍历postCommentUpvotes/postCommentDownvotes集合的方式性能极差，尤其是当评论的点赞/点踩数量较多时，会加载大量不必要的数据到内存，还需在Java层面循环校验，既耗内存又浪费CPU。直接执行针对性SQL查询仅返回当前用户对该评论的投票状态，性能优势明显。

SQL查询写法 #

1. 查询用户当前投票状态

用一条SQL即可获取用户对目标评论的投票情况：

SELECT 
    CASE WHEN up.id IS NOT NULL THEN 'UPVOTE'
         WHEN down.id IS NOT NULL THEN 'DOWNVOTE'
         ELSE 'NONE' END AS vote_status
FROM post_comment pc
LEFT JOIN post_comment_upvote up 
    ON pc.id = up.post_comment_id AND up.user_id = ?
LEFT JOIN post_comment_downvote down 
    ON pc.id = down.post_comment_id AND down.user_id = ?
WHERE pc.id = ?

参数依次为：当前用户ID、当前用户ID、目标评论ID。

2. 执行投票操作的SQL逻辑

通过事务包裹操作，确保数据一致性，针对三种状态分别处理：

• 无投票记录：直接插入对应表

-- 点赞
INSERT INTO post_comment_upvote (post_comment_id, user_id) VALUES (?, ?);
-- 点踩
INSERT INTO post_comment_downvote (post_comment_id, user_id) VALUES (?, ?);

• 重复投票：删除对应投票记录

-- 取消点赞
DELETE FROM post_comment_upvote WHERE post_comment_id = ? AND user_id = ?;
-- 取消点踩
DELETE FROM post_comment_downvote WHERE post_comment_id = ? AND user_id = ?;

• 切换投票类型：先删除原有记录，再插入新记录

-- 点赞转点踩
DELETE FROM post_comment_upvote WHERE post_comment_id = ? AND user_id = ?;
INSERT INTO post_comment_downvote (post_comment_id, user_id) VALUES (?, ?);

-- 点踩转点赞
DELETE FROM post_comment_downvote WHERE post_comment_id = ? AND user_id = ?;
INSERT INTO post_comment_upvote (post_comment_id, user_id) VALUES (?, ?);

JPA层面的优化实现 #

如果使用JPA，可通过自定义查询方法获取投票状态，避免加载整个实体集合：

@Repository
public interface PostCommentUpvoteRepository extends JpaRepository<PostCommentUpvote, Long> {
    boolean existsByPostCommentIdAndUserId(Long postCommentId, Long userId);
}

@Repository
public interface PostCommentDownvoteRepository extends JpaRepository<PostCommentDownvote, Long> {
    boolean existsByPostCommentIdAndUserId(Long postCommentId, Long userId);
}

业务逻辑中调用这两个方法判断投票状态，再执行对应增删操作即可，本质仍是生成高效的针对性SQL。

高性能优化建议 #

• 给post_comment_upvote和post_comment_downvote表建立复合索引：(post_comment_id, user_id)，大幅提升查询与删除操作的性能。
• 用数据库事务包裹所有投票相关操作，确保数据一致性（比如切换投票类型时的删、插操作需同时成功或失败）。
• 避免加载不必要的实体数据，始终只查询所需字段或状态，不要加载整个PostComment实体及其关联集合。

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