Dropwizard新增实体无法提交致重复插入ConstraintViolationException问题

云上老周

2026-6-16

问题描述

我们有一个线程用于查询外部系统客户信息，该线程接收包含客户customId（外部系统ID）、姓名、电话等信息的JSON数组，循环处理客户数据的新增/更新逻辑：通过customId和accountId查询数据库，若客户已存在则检查字段变更并更新，不存在则创建新客户。

目前出现异常：线程创建新客户后，后续循环中再次通过customId和accountId查询该客户时无法找到，进而尝试再次插入，触发ConstraintViolationException。即便JSON数组中存在重复客户，线程也应能找到已插入的客户并执行更新操作，无法理解该问题成因。

版本信息

Dropwizard版本：1.3.29
MySQL版本：8.0.31

栈追踪信息

java.lang.RuntimeException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at br.com.system.db.ClientDao.transaction(ClientDao.java:95)
        at br.com.system.db.ClientDao.insert(ClientDao.java:46)
        at br.com.system.remotesystems.agent.Integration.loadTransactions(Integration.java:1355)
        at br.com.system.core.system.integration.IntegrationJob.run(IntegrationJob.java:44)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:750)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
        at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2933)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3524)
        at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
        at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:637)
        at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
        at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
        at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
        at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:318)
        at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:275)
        at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:182)
        at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:113)
        at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:192)
        at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:38)
        at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:177)
        at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:32)
        at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:73)
        at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:692)
        at org.hibernate.internal.SessionImpl.save(SessionImpl.java:684)
        at org.hibernate.internal.SessionImpl.save(SessionImpl.java:679)
        at br.com.system.db.AbstractDao.lambda$insert$7(AbstractDao.java:242)
        at br.com.system.db.AbstractDao.executeCall(AbstractDao.java:126)
        ... 13 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1399856-1699' for key 'Client.customId'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
        at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy124.executeUpdate(Unknown Source)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
        ... 36 more

Client实体类

public class Client {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int clientId;

    private String customId; // external system id

    private int accountId;

    private String name;

    // ... other fields like phone, birthdate, etc
}

加载线程代码

Client client = clientDao.findByAccountIdAndCustomId(accountId, customId);

if (client == null) {
    client = new Client(accountId, customId, name, ...other fields);
    client = clientDao.insert(client);
} else /* client already exists */ {
    boolean update = false;
    if (!name.equals(client.getName())) {
        update = true;
        client.setName(name);
    }
    // ... checks and updates other fields
    if (update)
      clientDao.update(client);
}

ClientDao代码（继承Dropwizard AbstractDAO）

public class ClientDao extends AbstractDao<Client> {
    
    public ClientDao(SessionFactory session) {
        super(session);
    }

    public Client insert(Client client) {
        return transaction(() -> {
            currentSession().save(client);
            return client;
        }, true);
    }

    public Client update(Client client) {
        return transaction(() -> {
            currentSession().update(client);
            return client;
        }, true);
    }

    protected <E> E transaction(Callable<E> call, boolean commit) {
        if (ManagedSessionContext.hasBind(sessionFactory)) {
            Session session = currentSession();
            if (session.isOpen()) {
                return call.call();
            }
        }
        try (Session session = sessionFactory.openSession()) {
            ManagedSessionContext.bind(session);
            Transaction transaction = session.beginTransaction();
            E result = call.call();
            if (commit)
                transaction.commit();
            return result;
        } finally {
            ManagedSessionContext.unbind(sessionFactory);
        }
    }

    public Client findByAccountIdAndCustomId(int accountId, String customId) {
        return transaction(() -> {
            
        }, false);
    }

}

Client表建表SQL

CREATE TABLE `Client` (
  `clientId` int unsigned NOT NULL AUTO_INCREMENT,
  `customId` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(115) COLLATE utf8mb4_unicode_ci NOT NULL,
  `accountId` int unsigned DEFAULT NULL,
  -- ... other fields
  PRIMARY KEY (`clientId`),
  UNIQUE KEY `customId` (`customId`,`accountId`),
)

已尝试的解决方案

使用Dropwizard AbstractDAO的persist方法替代currentSession().save和currentSession().update，无效果；
设置Dropwizard数据库的commitOnReturn和autoCommitByDefault参数，无效果。

解决方案

问题核心在于事务隔离级别与Session生命周期不匹配，以及查询方法的事务处理逻辑错误，具体修复步骤如下：

1. 补充实体类联合唯一约束注解

数据库中customId和accountId是联合唯一键，但实体类未添加对应注解，导致Hibernate无法在内存层面校验重复，需添加注解让Hibernate提前拦截：

@Entity
@Table(
    name = "Client",
    uniqueConstraints = {
        @UniqueConstraint(columnNames = {"customId", "accountId"})
    }
)
public class Client {
    // ... 原有字段
}

2. 修正事务处理逻辑

原transaction方法复用Session上下文绑定的逻辑会导致查询使用过期缓存，需移除复用逻辑，确保每次操作使用独立Session，并在提交后刷新缓存：

protected <E> E transaction(Callable<E> call, boolean commit) {
    try (Session session = sessionFactory.openSession()) {
        ManagedSessionContext.bind(session);
        Transaction transaction = session.beginTransaction();
        try {
            E result = call.call();
            if (commit) {
                transaction.commit();
                session.flush(); // 提交后强制刷新，同步数据库状态
            }
            return result;
        } catch (Exception e) {
            transaction.rollback();
            throw new RuntimeException(e);
        }
    } finally {
        ManagedSessionContext.unbind(sessionFactory);
    }
}

3. 修复查询方法逻辑

原查询方法为空，需补充正确的HQL查询，同时在查询前刷新Session，确保读取到数据库最新数据：

public Client findByAccountIdAndCustomId(int accountId, String customId) {
    return transaction(() -> {
        currentSession().flush(); // 查询前刷新缓存
        return currentSession()
            .createQuery("SELECT c FROM Client c WHERE c.accountId = :accountId AND c.customId = :customId", Client.class)
            .setParameter("accountId", accountId)
            .setParameter("customId", customId)
            .uniqueResult();
    }, true); // 查询操作也提交事务，保证Session状态同步
}

4. 改用merge方法简化增删改逻辑

Hibernate的merge方法可自动判断实体是新增还是更新，无需手动查询，避免Session缓存问题：

public Client saveOrUpdate(Client client) {
    return transaction(() -> {
        currentSession().merge(client);
        return client;
    }, true);
}

修改加载线程代码：

Client existing = clientDao.findByAccountIdAndCustomId(accountId, customId);
if (existing != null) {
    // 复制字段变更到已存在的实体
    existing.setName(name);
    // ... 其他字段更新
    clientDao.saveOrUpdate(existing);
} else {
    Client client = new Client(accountId, customId, name, ...other fields);
    clientDao.saveOrUpdate(client);
}

5. 调整数据库事务隔离级别

MySQL默认隔离级别为REPEATABLE READ，会导致查询无法读取到刚提交的新数据，需改为READ COMMITTED：
在Dropwizard配置文件中添加：

database:
  driverClass: com.mysql.cj.jdbc.Driver
  url: jdbc:mysql://localhost:3306/your_db?useSSL=false&serverTimezone=UTC
  user: root
  password: password
  properties:
    hibernate.dialect: org.hibernate.dialect.MySQL8Dialect
    hibernate.connection.isolation: 2 # 对应READ COMMITTED

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

Dropwizard新增实体无法提交致重复插入ConstraintViolationException问题

版本信息 #

栈追踪信息 #

Client实体类 #

加载线程代码 #

ClientDao代码（继承Dropwizard AbstractDAO） #

Client表建表SQL #

已尝试的解决方案 #

1. 补充实体类联合唯一约束注解 #

2. 修正事务处理逻辑 #

3. 修复查询方法逻辑 #

4. 改用merge方法简化增删改逻辑 #

5. 调整数据库事务隔离级别 #