如何为JPA关联表comment_unreads添加email_sent字段实现邮件防重？

要给comment_unreads表添加email_sent字段并实现定时邮件通知，核心问题是原@ManyToMany自动生成的中间表无法直接添加自定义字段，必须将中间表转为独立实体类，再完成后续逻辑。以下是分步实现方案：

1. 重构中间表为独立实体 #

创建CommentUnread实体类映射comment_unreads表，包含原关联字段和新增的邮件发送标记：

1.1 复合主键类（用于关联Comment和UserProfile） #

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CommentUnreadId implements Serializable {
    private Long comment;   // 对应Comments的id字段
    private Long userProfile; // 对应UserProfile的id字段
}

1.2 CommentUnread实体类 #

@Entity
@Table(name = "comment_unreads")
@Data
@NoArgsConstructor
@AllArgsConstructor
@IdClass(CommentUnreadId.class)
public class CommentUnread {
    @Id
    @ManyToOne
    @JoinColumn(name = "comment_id")
    private Comments comment;

    @Id
    @ManyToOne
    @JoinColumn(name = "user_profile_id")
    private UserProfile userProfile;

    // 邮件发送标记，默认未发送
    private boolean emailSent = false;

    // 邮件发送时间（可选，用于追踪）
    @Column(name = "email_sent_time")
    private LocalDateTime emailSentTime;
}

2. 修改Comments实体类 #

将原@ManyToMany关联的readBy集合替换为CommentUnread的一对多关联，并调整相关业务方法：

@Entity
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Comments {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Long quotationId;

    @ManyToOne
    @JoinColumn(name = "uid", referencedColumnName = "uid", nullable = false)
    private UserProfile user;

    @Size(min = 5, max = 300)
    private String comment;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "comment_likes", joinColumns = @JoinColumn(name = "comment_id"), inverseJoinColumns = @JoinColumn(name = "user_id"))
    private Set<UserProfile> likedBy = new HashSet<>();

    // 替换原readBy集合，关联CommentUnread实体
    @OneToMany(mappedBy = "comment", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<CommentUnread> commentUnreads = new HashSet<>();

    @CreationTimestamp
    private LocalDateTime createdAt;

    // 更新已读状态：移除对应的未读记录
    public void markAsReadBy(UserProfile user) {
        commentUnreads.removeIf(unread -> unread.getUserProfile().equals(user));
    }

    // 填充需要通知的用户（排除评论创建者）
    public void populateReadBy(Set<UserProfile> allUsersForQuotation, Long uid) {
        allUsersForQuotation.forEach(user -> {
            if (!user.getUser().getUid().equals(uid)) {
                CommentUnread unread = new CommentUnread();
                unread.setComment(this);
                unread.setUserProfile(user);
                commentUnreads.add(unread);
            }
        });
    }
}

3. 实现定时邮件通知逻辑 #

使用Spring的定时任务功能，每小时查询未发送邮件的未读记录，发送邮件后更新标记：

3.1 数据访问层Repository #

public interface CommentUnreadRepository extends JpaRepository<CommentUnread, CommentUnreadId> {
    // 查询所有未发送邮件的记录
    List<CommentUnread> findByEmailSentFalse();
}

3.2 定时任务服务类 #

@Service
@Transactional
public class CommentNotificationService {
    private final CommentUnreadRepository commentUnreadRepository;
    private final JavaMailSender mailSender;

    // 构造注入依赖
    public CommentNotificationService(CommentUnreadRepository commentUnreadRepository, JavaMailSender mailSender) {
        this.commentUnreadRepository = commentUnreadRepository;
        this.mailSender = mailSender;
    }

    // 每小时整点执行（cron表达式：分 时 日 月 周 年）
    @Scheduled(cron = "0 0 * * * ?")
    public void sendUnreadCommentEmails() {
        List<CommentUnread> unreadRecords = commentUnreadRepository.findByEmailSentFalse();

        for (CommentUnread record : unreadRecords) {
            UserProfile user = record.getUserProfile();
            Comments comment = record.getComment();

            // 构建邮件内容
            SimpleMailMessage message = new SimpleMailMessage();
            message.setTo(user.getEmail());
            message.setSubject("您有一条未读评论");
            message.setText(String.format("您在报价%d下有一条未读评论：%n%s", comment.getQuotationId(), comment.getComment()));

            // 发送邮件
            mailSender.send(message);

            // 更新发送状态和时间
            record.setEmailSent(true);
            record.setEmailSentTime(LocalDateTime.now());
            commentUnreadRepository.save(record);
        }
    }
}

4. 后续配置与迁移 #

• 数据库迁移：手动执行SQL为原表添加字段，或用Flyway/Liquibase自动化迁移：

ALTER TABLE comment_unreads ADD COLUMN email_sent BOOLEAN DEFAULT FALSE;
ALTER TABLE comment_unreads ADD COLUMN email_sent_time TIMESTAMP;

• 启用定时任务：在Spring Boot启动类添加@EnableScheduling注解；
• 邮件配置：在application.properties中配置SMTP参数，确保邮件能正常发送。

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