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

Question

在现有答案中搜索没有给出有效结果。这就是我找不到原因的错误。我有一个实体类< code>MeteoRecord和< code>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
}

所有实体都扩展< code>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;
}

保存新的气象记录时，如下所示：

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.

“气象仓库”只是扩展了“”JPA存储库：

@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上。< code>Hibernate不通过id查找记录来创建新的记录，但它只需要数据库中的下一个标识。因为它< code>IDENTITY它将获得策略的下一个值(1..或者别的什么)。现在让我们假设您已经将增量策略定义为< code >增量1，此时当前值为12。所以很明显，在实体持久化期间，下一个值将是13。但是由于某种原因(例如，通过具有< code>id列的直接DB sql插入)插入了具有< code>id=13的记录。在这种情况下，< code>Hibernate不知道任何事情，因为身份没有增加。我们到了。现在，任何使用identity插入的尝试都将导致给定的异常。

我认为你应该这样做：

select max(id) + 1 from meteo_record;

并在此中使用结果值 （x）：

ALTER TABLE meteo_record ALTER COLUMN id RESTART WITH x;

更新

以下是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;