Spring /HIbernate:重复键值违反唯一约束

Question

在现有答案中搜索没有有效的结果。这就是我找不到原因的错误。我有一个实体类MeteoRecord和DayDegree，定义如下：

@Getter
@Setter
@Entity
public class MeteoRecord extends AbstractEntity {

    @Temporal(TemporalType.DATE)
    Date date;
...
//other attributes
}

@Entity
@Getter
@Setter
public class DayDegree extends AbstractEntity {

    @ManyToOne
    @JoinColumn(name = "meteo_record_id")
    private MeteoRecord meteoRecord;
...
//other attributes
}

所有实体都扩展了AbstractEntity类：

@MappedSuperclass
@Getter
@Setter
public abstract class AbstractEntity implements Serializable {

    @Access(AccessType.PROPERTY)
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "DATCRE")
    public Date datcre;

    @UpdateTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "DATMOD")
    public Date datmod;
}

保存新MeteoRecord时，如下所示：

Optional<MeteoRecord> meteoByDate = meteoRecordRepository.findByDate(dateAsDate);
  MeteoRecord meteoRecord;
  if (meteoByDate.isPresent()) {
      meteoRecord = meteoByDate.get();
  } else {
      meteoRecord = new MeteoRecord();
      meteoRecord.setDate(dateAsDate);
  }
MeteoRecord savedMeteoRecord = meteoRecordRepository.save(meteoRecord);
...

它会引发错误：

rg.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "meteo_record_pkey"
  Detail: Key (id)=(1680) already exists.

“`MeteoRepositiry”只是扩展了 JPARepository：

@Repository
public interface MeteoRecordRepository extends JpaRepository<MeteoRecord, Long> {
...
Optional<MeteoRecord> findByDate(Date date);
}

我激活了更多日志来查看SQL查询，但没有看到创建新MeteoRecord的其他查询，也没有在其他地方插入DayDegree记录：

11:33:55.664 [http-nio-8080-exec-1] DEBUG org.hibernate.SQL - 
    select
        meteorecor0_.id as id1_33_,
        meteorecor0_.datcre as datcre2_33_,
        meteorecor0_.datmod as datmod3_33_,
        meteorecor0_.date as date4_33_,
        meteorecor0_.degree_day_15 as degree_d5_33_,
        meteorecor0_.degree_day_15_eq as degree_d6_33_,
        meteorecor0_.station as station7_33_,
        meteorecor0_.temp_avg as temp_avg8_33_,
        meteorecor0_.temp_avg_eq as temp_avg9_33_ 
    from
        meteo_record meteorecor0_ 
    where
        meteorecor0_.date=?
Hibernate: 
    select
        meteorecor0_.id as id1_33_,
        meteorecor0_.datcre as datcre2_33_,
        meteorecor0_.datmod as datmod3_33_,
        meteorecor0_.date as date4_33_,
        meteorecor0_.degree_day_15 as degree_d5_33_,
        meteorecor0_.degree_day_15_eq as degree_d6_33_,
        meteorecor0_.station as station7_33_,
        meteorecor0_.temp_avg as temp_avg8_33_,
        meteorecor0_.temp_avg_eq as temp_avg9_33_ 
    from
        meteo_record meteorecor0_ 
    where
        meteorecor0_.date=?
11:33:55.677 [http-nio-8080-exec-1] DEBUG o.s.d.r.c.s.TransactionalRepositoryProxyPostProcessor$CustomAnnotationTransactionAttributeSource - Adding transactional method 'save' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
11:33:55.679 [http-nio-8080-exec-1] DEBUG o.s.orm.jpa.JpaTransactionManager - Found thread-bound EntityManager [SessionImpl(1454087429<open>)] for JPA transaction
11:33:55.680 [http-nio-8080-exec-1] DEBUG o.s.orm.jpa.JpaTransactionManager - Creating new transaction with name [org.springframework.data.jpa.repository.support.SimpleJpaRepository.save]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
11:33:55.680 [http-nio-8080-exec-1] DEBUG o.h.e.t.internal.TransactionImpl - On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
11:33:55.680 [http-nio-8080-exec-1] DEBUG o.h.e.t.internal.TransactionImpl - begin
11:33:55.681 [http-nio-8080-exec-1] DEBUG o.s.orm.jpa.JpaTransactionManager - Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@27864a10]
11:33:55.696 [http-nio-8080-exec-1] DEBUG org.hibernate.engine.spi.ActionQueue - Executing identity-insert immediately
11:33:55.703 [http-nio-8080-exec-1] DEBUG org.hibernate.SQL - 
    insert 
    into
        meteo_record
        (datcre, datmod, date, degree_day_15, degree_day_15_eq, station, temp_avg, temp_avg_eq) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        meteo_record
        (datcre, datmod, date, degree_day_15, degree_day_15_eq, station, temp_avg, temp_avg_eq) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?)
11:33:55.732 [http-nio-8080-exec-1] DEBUG o.h.e.jdbc.spi.SqlExceptionHelper - could not execute statement [n/a]
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "meteo_record_pkey"
  Detail: Key (id)=(1681) already exists.

我遗漏了什么？我的印象是，这是因为在DayDegree实体中定义了一个关系，但没有看到查询，也没有使用DayDegree实体。

谢谢。

Answer 1

根据这一点：

当我检查表meteo_record时，当然，总是有一个在错误中显示id的记录: select * from meteo_record mr mr.id = 1678；

我可以假设这个问题在DB中。Hibernate不通过id创建新记录来查找记录，但它只需要数据库中的下一个标识。因为它是IDENTITY，它将得到策略的下一个值(+1..或者别的什么)。现在让我们想象一下，您已经将增量策略定义为INCREMENT BY 1，当前的值为12，因此很明显，在实体持久性期间下一个值将为13。但是，以某种方式(例如，通过直接direct和id列)，用id=13插入了记录。在这种情况下，Hibernate对此一无所知，因为身份没有增加。我们到了。现在，任何使用identity插入的尝试都将导致我们到给定的异常。

我觉得你应该这样做：

select max(id) + 1 from meteo_record;

并在以下方面使用结果值(x)：

ALTER TABLE meteo_record ALTER COLUMN id RESTART WITH x;

UPD

下面是用于meteo_record的DDL

CREATE TABLE public.meteo_record (     
    id bigint NOT NULL DEFAULT nextval('meteo_record_id_seq'::regclass),     
    datcre timestamp without time zone,     
    datmod timestamp without time zone,     
    date date,     
    degree_day_15 double precision,     
    degree_day_15_eq double precision,     
    station character varying(255) COLLATE pg_catalog."default",     
    temp_avg double precision,     
    temp_avg_eq double precision,     
    CONSTRAINT meteo_record_pkey PRIMARY KEY (id) 
)

因为这里有一个序列，所以正确的重新设置它的命令应该是

ALTER SEQUENCE meteo_record_id_seq RESTART WITH 1973;