spring-data-jpa - java.sql.SQLIntegrityConstraintViolationException：更新时键“日期”的重复条目“2020-05-29”

Question

无法更新数据库中的记录，它尝试插入新记录，但由于唯一约束而引发错误

我有如下实体，

@Setter
@Getter
@Audited
@Entity
@Accessors(chain = true)
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
@EntityListeners(AuditingEntityListener.class)
public class Sum {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    // Date
    @Column(nullable = false, unique = true)
    private Date date;

    // Sum of won
    @Column(nullable = false, scale = 2)
        private BigDecimal won;

    @CreatedDate
    private Date createdDateTime;
}

使用 flyway 脚本创建 db 表，如下所示，

    CREATE TABLE `sum` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `date` date NOT NULL UNIQUE,
      `won` numeric(28,2) NOT NULL,
      `created_date_time` datetime NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE `sum_aud` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `rev` int(11) NOT NULL UNIQUE,
      `revtype` tinyint(4) DEFAULT NULL,
      `date` date NOT NULL unique,
      `won` numeric(28,2) NOT NULL,
      `created_date_time` datetime NOT NULL,
      PRIMARY KEY (`id`,`rev`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `revinfo` (
  `rev` int(11) NOT NULL AUTO_INCREMENT,
  `revtstmp` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`rev`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1;

jpaRepository 如下，

public interface SumRepository extends PagingAndSortingRepository<Sum,Long> {

    @Query("select t from Sum t where t.date = ?1")
    Iterable<Sum> findByDate(Date previousDate);
}

我正在尝试按如下方式插入/更新记录，

  public void saveSum(Sum sumObj) {


    // update if exist already
    Iterable<Sum> Sums = getDBRecord(Sum.getDate());
    Iterator it = Sums.iterator();
    if (it.hasNext()) {
        Sum existingRecord = (Sum) it.next();

        existingRecord
                .setCreatedDateTime(sumObj.getCreatedDateTime())
                .setWon(sumObj.getWon());
        sumRepository.save(existingRecord);
    } else
        sumRepository.save(sumObj);
}

但是，如果我尝试为现有记录设置任何属性，则会引发以下错误，

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2020-05-29' for key 'date'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1094)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1042)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1345)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1027)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3238)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3763)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
    at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
    at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1360)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1347)
    at org.hibernate.envers.internal.synchronization.AuditProcess.doBeforeTransactionCompletion(AuditProcess.java:177)
    at org.hibernate.envers.internal.synchronization.AuditProcessManager$1.doBeforeTransactionCompletion(AuditProcessManager.java:47)
    at org.hibernate.engine.spi.ActionQueue$BeforeTransactionCompletionProcessQueue.beforeTransactionCompletion(ActionQueue.java:954)
    at org.hibernate.engine.spi.ActionQueue.beforeTransactionCompletion(ActionQueue.java:525)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2379)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)

我不明白为什么它不持久化一个对象来更新它，而是尝试创建一个新的对象来插入，我也可以在上面的日志中看到 ResultSetReturnImpl.executeUpdate 方法被调用然后为什么它给唯一键错误

有人知道吗？我试图搜索与此相关的许多问题，但找不到任何有用的信息。现在有点卡住了。

标签： spring-data-jpacrud