无法解决货币新增与更新问题：软删除后重复创建报主键冲突

问题根源 #

系统采用软删除机制（通过isDeleted字段标记删除，而非物理删除数据），但现有insert方法未考虑已被软删除的同名货币记录：

• 删除货币后，数据库中仍保留isoCode（主键）相同、isDeleted = true的记录；
• 执行新的insert操作时，直接插入同主键的记录，触发数据库主键唯一约束报错；
• 现有update方法仅对isDeleted = false的记录生效，无法处理已删除记录的恢复场景。

修复方案 #

方案1：插入前检查并恢复软删除记录 #

修改insert方法，先查询是否存在同isoCode的软删除记录，若存在则恢复该记录并同步最新字段，不存在再执行正常插入：

public String insert(CurrencyRequestDTO dto, String translationKey) {
    // 检查是否存在已软删除的同名货币
    Currency existingDeleted = queryFactory.selectFrom(currency)
            .where(currency.isoCode.eq(dto.isoCode).and(currency.isDeleted.eq(TRUE)))
            .fetchOne();

    if (existingDeleted != null) {
        // 恢复软删除记录并更新字段
        Long result = queryFactory.update(currency)
                .set(currency.digitalCode, dto.digitalCode)
                .set(currency.name, dto.name)
                .set(currency.symbol, dto.symbol)
                .set(currency.fullNameKey, translationKey)
                .set(currency.isDeleted, FALSE)
                .where(currency.isoCode.eq(dto.isoCode))
                .execute();
        CrudRepository.exceptionCheck(result, UPDATE);
        return dto.isoCode;
    }

    // 无软删除记录，执行正常插入
    Long affectedRows = queryFactory.insert(currency)
            .set(currency.isoCode, dto.isoCode)
            .set(currency.digitalCode, dto.digitalCode)
            .set(currency.name, dto.name)
            .set(currency.symbol, dto.symbol)
            .set(currency.fullNameKey, translationKey)
            .set(currency.isDeleted, FALSE) // 显式设置未删除状态
            .execute();
    CrudRepository.exceptionCheck(affectedRows, INSERT);

    return dto.isoCode;
}

方案2：捕获主键冲突异常并处理恢复 #

若担心并发场景下的查询-插入竞态问题，可尝试直接插入，捕获主键冲突的SQL异常后再检查并恢复软删除记录：

public String insert(CurrencyRequestDTO dto, String translationKey) {
    try {
        Long affectedRows = queryFactory.insert(currency)
                .set(currency.isoCode, dto.isoCode)
                .set(currency.digitalCode, dto.digitalCode)
                .set(currency.name, dto.name)
                .set(currency.symbol, dto.symbol)
                .set(currency.fullNameKey, translationKey)
                .set(currency.isDeleted, FALSE)
                .execute();
        CrudRepository.exceptionCheck(affectedRows, INSERT);
        return dto.isoCode;
    } catch (SQLIntegrityConstraintViolationException e) {
        // 检查是否是主键冲突且存在软删除记录
        Currency existingDeleted = queryFactory.selectFrom(currency)
                .where(currency.isoCode.eq(dto.isoCode).and(currency.isDeleted.eq(TRUE)))
                .fetchOne();
        if (existingDeleted != null) {
            // 恢复并更新记录
            Long result = queryFactory.update(currency)
                    .set(currency.digitalCode, dto.digitalCode)
                    .set(currency.name, dto.name)
                    .set(currency.symbol, dto.symbol)
                    .set(currency.fullNameKey, translationKey)
                    .set(currency.isDeleted, FALSE)
                    .where(currency.isoCode.eq(dto.isoCode))
                    .execute();
            CrudRepository.exceptionCheck(result, UPDATE);
            return dto.isoCode;
        }
        // 非软删除导致的主键冲突，重新抛出异常
        throw e;
    }
}

额外注意事项 #

• 确保删除操作正确设置isDeleted = true，而非物理删除，示例删除方法：

  public void delete(String isoCode) {
      Long result = queryFactory.update(currency)
              .set(currency.isDeleted, TRUE)
              .where(currency.isoCode.eq(isoCode).and(currency.isDeleted.eq(FALSE)))
              .execute();
      CrudRepository.exceptionCheck(result, DELETE);
  }
  

• 若业务允许，也可将删除逻辑改为物理删除数据库记录，但软删除更利于数据追溯，需根据业务需求选择。

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