SqlExceptionHelper - 唯一键的重复项使用Spring Data JPA [英] SqlExceptionHelper - Duplicate entry for Unique key Using Spring Data JPA

问题描述

这是情况：

1. 我有3个表格 - 虚拟，A和B - 其中A和B有一对一许多
   关系，Dummy是独立的


2. 这些表在数据层中具有相应的JPA实体。我是使用存储库设计模式的
   ，因此通过它们相应的
   服务实现来访问这些实体。


3. 我正在对这些实体进行精确的调用序列：
>

4. 获取ID = xxx的实体
   
5. 显示实体ID和名称（或其他）
   
6. 更新字段使用entity.setField（YYY）
   
7. 使用entityService.updateEntity（entity）将它推回到DB
   
   
   

   从＃4到＃7的上述顺序就像虚拟桌子的魅力一样。但未能执行A和B.例外情况是：
   
   $ p $ ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - 重复输入<这里的名字>关键'name_UNIQUE'
线程main中的异常org.springframework.orm.jpa.JpaSystemException：org.hibernate.exception.ConstraintViolationException：无法执行语句;嵌套的例外是javax.persistence.PersistenceException：org.hibernate.exception.ConstraintViolationException：在org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible（EntityManagerFactoryUtils.java:321）
。在组织无法执行的语句
.bringframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible（AbstractEntityManagerFactoryBean.java:403）
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible（ChainedPersistenceExceptionTranslator.java:58）
at org.springframework.dao .support.DataAccessUtils.translateIfNecessary（DataAccessUtils.java:213）


   
   
   

   我正在更新的字段不是唯一的。这些实体具有完全相同的结构，并在这里和那里附加了一些列。

   
   
   

   以下是Dummy Entity的代码：
   
   

     DummyEntity dummyEntity = dummyService.findDummyEntity（16L）; 
    System.out.println（>>>名称是：+ dummyEntity.getName（）+，ID：+ dummyEntity.getId（））; 
    dummyEntity.setName（New Name）; 
    dummyEntity.setRank（3333333）; 
    dummyService.updateDummyEntity（dummyEntity）; 
     

   为剩余实体A和B重复完全相同的步骤。

   
   
   

   那么我做错了什么？任何指针将不胜感激。
   
   

   更新：

   
   
   

   @erencan - 是的，我仔细检查过。这里是我在这里提出问题后观察到的。表A和B（麻烦的）有这个问题：
   
   

   
   
   • 当我让知识库服务返回一个给定ID的实例对于表A和B，当使用setXXX（）对该实例进行更改时，会返回它们，并调用updateEntity（）或saveEntity（）（如使用演示实体代码），保存/更新会在表中插入一个新的实体，其中的属性值与旧的实体相同，但新的更改并入（通过删除表A和B中的唯一键约束来观察）。后来，当我在JPA / Java代码中使用它们的ID查询这些新创建的实体并执行完全相同的步骤（更改存储库中的某个属性并调用save / update）时， 这些新创建的实体（db表中的行）将按照预期的方式进行更新。
   因此，似乎原始实体（行）以某种方式锁定并且更新被阻止。因此，JPA保存/更新调用只是尝试创建一个新的调用;并且由于新实体仍然具有相同的一组属性值，所以任何UNIQUE键约束都会开始抱怨（当然）。
   
   • 我对现有表进行了一些测试（ETL'd into the DB），并且发现这种行为是一致的：如果实体不是由JPA创建的，那么JPA可以读取数据，但不能更新它们;如果实体由JPA创建，那么JPA可以读取并更新它们。
   
   
   
   
   

   不知道这是怎么发生的（但）。下面是表A和B的模式：
   
   

     CREATE TABLE IF NOT EXISTS`mydbschema`.`table-B`（
   `id` INT NOT NULL AUTO_INCREMENT COMMENT'这是PK'，
   `name` VARCHAR（100）NULL，
   `city` VARCHAR（50）NOT NULL，
   `state `VARCHAR（30）NOT NULL，
   `zip` VARCHAR（5）NOT NULL，
   `country` VARCHAR（50）NOT NULL，
   `overall_rank` INT NULL，
    `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP，
   `insert_src_ver_id` INT NULL，
   `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP，
   `update_src_ver_id` INT NULL，
   `version` INT NULL，
    PRIMARY KEY（`id`），
    UNIQUE INDEX`name_UNIQUE`（`name` ASC））
    ENGINE = InnoDB; 
     

   另一个：
   
   

     CREATE TABLE IF NOT EXISTS`mydbschema`.`table-A`（
   `id` INT NOT NULL AUTO_INCREMENT COMMENT'这是PK'，
   `full_name` VARCHAR（200） NULL，
   `gender` VARCHAR（1）NULL，
   `year_of_birth` VARCHAR（4）NULL，
   `title_code` VARCHAR（6）NULL，
   `business_role` VARCHAR 30）NULL，
   `graduation_year` VARCHAR（4）NULL，
   `residency` VARCHAR（500）NULL，
   `table-B_id` INT NULL，
   `npi_num` VARCHAR （10）NULL，
   `upin` VARCHAR（20）NULL，
   `dea_num` VARCHAR（20）NULL，
   `dea_expire_date` VARCHAR（10）NULL，
   `year_started_practicing VARCHAR（4）NULL，
   `high_prescriber` VARCHAR（1）NULL，
   `board_action` VARCHAR（1）NULL，
   `mdi_qscore` INT NOT NULL DEFAULT 0，
    `mdi_cscore` INT NOT NULL DEFAULT 0，
   `aco_id` INT NULL，
   `npp` INT NULL，
   `medicaid_id` VARCHAR（50）NULL，
   `medicaid_state` VARCHAR （2）NULL，
   `医疗保险_id` VARCHAR（50）NULL，
   `medicare_state` VARCHAR（2）NULL，
   `medicare_provider_flag` VARCHAR（1）NULL，
   `inserted'TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP，
   `insert_src_ver_id` INT NULL，
   `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP，
   `update_src_ver_id` INT NULL，
   `version` INT NULL，
    PRIMARY KEY（`id` ），
    UNIQUE INDEX`hdsphy_id_UNIQUE`（`id` ASC），
    UNIQUE INDEX`npi_num_UNIQUE`（`npi_num` ASC），
    UNIQUE INDEX`dea_num_UNIQUE`（`dea_num` ASC）， 
    CONSTRAINT`fk_table-A_table-B` 
    FOREIGN KEY（`table-B_id`）
    REFERENCES`mydbschema`.`table-B`（`id`）
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION，
    ENGINE = InnoDB; 
     

   以下是完整堆栈追踪：
   
   

     2013-09-30 10：20：49,705 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper  - 关键'npi_num_UNIQUE'重复条目'1568673648'
   线程main中的异常org.springframework.orm.jpa.JpaSystemException：org.hibernate.exception.ConstraintViolationException：无法执行语句;嵌套的例外是javax.persistence.PersistenceException：org.hibernate.exception.ConstraintViolationException：在org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible（EntityManagerFactoryUtils.java:321）
   。在组织无法执行的语句
    .bringframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible（AbstractEntityManagerFactoryBean.java:403）
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible（ChainedPersistenceExceptionTranslator.java:58）
    at org.springframework.dao .support.DataAccessUtils.translateIfNecessary（DataAccessUtils.java:213）
   在org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke（PersistenceExceptionTranslationInterceptor.java:163）
   在org.springframework.aop.framework.ReflectiveMethodInvocation .proceed（ReflectiveMethodInvocation.java:172）
   在org.springframework.data.jpa.repository.support.LockModeRepos itoryPostProcessor $ LockModePopulatingMethodIntercceptor.invoke（LockModeRepositoryPostProcessor.java:84）
   位于org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:172）
   位于org.springframework.aop.interceptor.ExposeInvocationInterceptor。调用在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:172）
   （ExposeInvocationInterceptor.java:91）
   在org.springframework.aop.framework.JdkDynamicAopProxy.invoke（JdkDynamicAopProxy。 java：204）
    at com.sun.proxy $ Proxy60.save（Unknown Source）
    at com.mdinsider.platform.domain.PhysicianServiceImpl_Roo_Service.ajc $ interMethod $ com_mdinsider_platform_domain_PhysicianServiceImpl_Roo_Service $ com_mdinsider_platform_domain_PhysicianServiceImpl $ updatePhysician（PhysicianServiceImpl_Roo_Service。 aj：48）
    at com.mdinsider.platform.domain.PhysicianServiceImpl.updatePhysician（PhysicianServiceImpl.java:1）
    at com.mdinsider.platform.domain.Phys icianService_Roo_Service.ajc $ interMethodDispatch1 $ com_mdinsider_platform_domain_PhysicianService_Roo_Service $ com_mdinsider_platform_domain_PhysicianService $ updatePhysician（PhysicianService_Roo_Service.aj）
   处com.mdinsider com.mdinsider.platform.mediblip.engine.TestDBSave.saveMDIQualityScore（TestDBSave.java:94）
   。 platform.mediblip.engine.TestDBSave.main（TestDBSave.java:142）
   导致：javax.persistence.PersistenceException：org.hibernate.exception.ConstraintViolationException：无法在org.hibernate中执行语句
   。 ejb.AbstractEntityManagerImpl.convert（AbstractEntityManagerImpl.java:1387）
   在org.hibernate.ejb.AbstractEntityManagerImpl.convert（AbstractEntityManagerImpl.java:1310）
   在org.hibernate.ejb.AbstractEntityManagerImpl.convert（AbstractEntityManagerImpl。 Java的：在sun.reflect.NativeMethodAccessorImpl.invoke0在org.hibernate.ejb.AbstractEntityManagerImpl.merge 1316）
   （AbstractEntityManagerImpl.java:898）
   （本机方法）
   。在sun.reflect。娜tiveMethodAccessorImpl.invoke（NativeMethodAccessorImpl.java:57）
   在sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java:43）
   在java.lang.reflect.Method.invoke（Method.java:606）在org.springframework.orm.jpa.SharedEntityManagerCreator $ SharedEntityManagerInvocationHandler.invoke（SharedEntityManagerCreator.java:241）
   。在com.sun.proxy。$ Proxy31.merge（来源不明）
    
   。在组织.springframework.data.jpa.repository.support.SimpleJpaRepository.save（SimpleJpaRepository.java:345）
    at sun.reflect.NativeMethodAccessorImpl.invoke0（Native Method）
    at sun.reflect.NativeMethodAccessorImpl.invoke（ NativeMethodAccessorImpl.java:57）美元，sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java:43 b $ b）
   在java.lang.reflect.Method.invoke（Method.java:606）
   在org.springframework.data.repository.core.support.RepositoryFactorySupport $ QueryExecutorMethodInterceptor.executeMethodOn（RepositoryFactorySupport.java:334）
    at org.springframework.data.repository.core.support.RepositoryFactorySupport $ QueryExecutorMethodInterceptor.invoke（RepositoryFactorySupport.java:319）
   在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:172）
   。在org.springframework.transaction.interceptor.TransactionInterceptor $ 1.proceedWithInvocation（TransactionInterceptor.java:96）
   在org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction（TransactionAspectSupport.java:260）
   。在组织.springframework.transaction.interceptor.TransactionInterceptor.invoke（TransactionInterceptor.java:94）
   在org.springframework.aop.framework.ReflectiveMethodInvocation.proceed（ReflectiveMethodInvocation.java:172）
   在的org.springframework.dao .support.PersistenceExceptionTranslationInterceptor.invoke（PersistenceExceptionTranslationInterceptor.java:155）
    ... 12 more 
   引起：org.hibernate.exception.ConstraintViolationException：无法执行在org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert（SQLExceptionTypeDelegate.java:74）UTE声明
    
   在org.hibernate.exception.internal.StandardSQLExceptionConverter.convert（StandardSQLExceptionConverter.java:49）
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert（SqlExceptionHelper.java:125）
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert（SqlExceptionHelper.java:110）
   。在org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate（ResultSetReturnImpl.java:136）
   在org.hibernate.id.IdentityGenerator $ GetGeneratedKeysDelegate.executeAndExtract（IdentityGenerator.java:96）
   在org.hibernate.id.insert.AbstractReturningDelegate.performInsert（AbstractReturningDelegate.java:58）
   在org.hibernate.persister.entity.AbstractEntityPersister.insert（AbstractEntityPersister.java:2975）
   在org.hibernate作为.persister.entity.AbstractEntityPersister.insert（AbstractEntityPersister.java:3487）org.hibernate.action.intern 
    org.hibernate.engine.spi.ActionQueue.execute（ActionQueue.java:377）
    org.hibernate.engine.spi.ActionQueue 
   。 addResolvedEntityInsertAction（ActionQueue.java:214）
    at org.hibernate.engine.spi.ActionQueue.addInsertAction（ActionQueue.java:194）
    at org.hibernate.engine.spi.ActionQueue.addAction（ActionQueue。 Java的：178），美元，org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction（AbstractSaveEventListener.java:321 b $ b）
   在org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate（AbstractSaveEventListener.java:286）在org.hibernate.event.internal.AbstractSaveEventListener.performSave（AbstractSaveEventListener.java:192）
    
   在org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId（AbstractSaveEventListener.java:125）
    at org.hibernate.ejb.event.EJB3MergeEventListener.saveWithGeneratedId（EJB3MergeEventListener.java:71）
    at org.hibernat e.event.internal.DefaultMergeEventListener.saveTransientEntity（DefaultMergeEventListener.java:236）
   处org.hibernate.event org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient（DefaultMergeEventListener.java:216）
   。 internal.DefaultMergeEventListener.onMerge（DefaultMergeEventListener.java:154）
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge（DefaultMergeEventListener.java:76）
    at org.hibernate.internal.SessionImpl.fireMerge（ SessionImpl.java:914）
   在org.hibernate.internal.SessionImpl.merge（SessionImpl.java:898）
   在org.hibernate.internal.SessionImpl.merge（SessionImpl.java:902）
    at org.hibernate.ejb.AbstractEntityManagerImpl.merge（AbstractEntityManagerImpl.java:889）
    ... 31 more 
   引起：com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException：重复条目' '关键'npi_num_UNIQUE'
   在sun.reflect.NativeConstructorAccessorImpl.newInstance0（本地方法）
   在阳光下的'1568673648' .reflect.NativeConstructorAccessorImpl.newInstance（NativeConstructorAccessorImpl.java:57）
   在sun.reflect.DelegatingConstructorAccessorImpl.newInstance（DelegatingConstructorAccessorImpl.java:45）
   在java.lang.reflect.Constructor.newInstance（Constructor.java ：526）
    at com.mysql.jdbc.Util.handleNewInstance（Util.java:411）
    at com.mysql.jdbc.Util.getInstance（Util.java:386）
    at com.mysql.jdbc.SQLError.createSQLException（SQLError.java:1039）
    at com.mysql.jdbc.MysqlIO.checkErrorPacket（MysqlIO.java:3609）
    at com.mysql.jdbc.MysqlIO。 checkErrorPacket（MysqlIO.java:3541）
    at com.mysql.jdbc.MysqlIO.sendCommand（MysqlIO.java:2002）
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect（MysqlIO.java:2163） 
    at com.mysql.jdbc.ConnectionImpl.execSQL（ConnectionImpl.java:2624）
    at com.mysql.jdbc.PreparedStatement.executeInternal（PreparedStatement.java:2127）
    at com.mysql .jdbc.PreparedStatement.executeUpdate（PreparedStatement.java:2427）
    at com.mysql.jdbc.PreparedStatemen t.executeUpdate（PreparedStatement.java:2345）
   在com.mysql.jdbc.PreparedStatement.executeUpdate（PreparedStatement.java:2330）
   在org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate（DelegatingPreparedStatement。的java：在org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate（DelegatingPreparedStatement.java:105 105）
   ）
   。在org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate（ResultSetReturnImpl.java： 133）
     

   
   

   解决方案

   好的，问题解决了。在尝试了各种方法来隔离问题后，我发现手动添加或通过ETL添加的行将被选择性地处理以解决此异常。 Spring Data JPA / Java代码添加的任何行都可以正常工作。

   
   
   

   因此，问题出在手动插入行。然后我意识到VERSION字段在那里用那些手动插入行的NULL值。当我将这些值设置为0时，手动插入的行已被JPA接受。

   
   
   

   另一种选择是根本没有版本字段。

   
   
   

   希望这可以帮助遇到同样问题的人。

   
   

   Here is the situation:

   1. I have 3 tables - Dummy, A and B - where A and B have One-To-Many relationship, and Dummy is stand alone
   2. These tables have corresponding JPA entities in the data layer. I am using Repository design pattern, so accessing these entities via their corresponding service implementations.
   3. I am making exact sequence of calls to these entities:
   4. Get an entity for ID = xxx
   5. Display the entity ID and name (or whatever)
   6. Update a field using entity.setField(YYY)
   7. push it back to DB using: entityService.updateEntity(entity)

   The above sequence from #4 to #7 works like a charm for Dummy table. But fails to execute for the A and B. The exception is:

   ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Duplicate entry <The name here> for key 'name_UNIQUE'
   Exception in thread "main" org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
   at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:321)
   at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
   at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
   at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
   

   The field that I am updating is not unique. The entities have exactly same structure with some additional columns here and there.

   Here is the code for Dummy Entity:

       DummyEntity dummyEntity = dummyService.findDummyEntity(16L);
       System.out.println(">>> Name is: " + dummyEntity.getName() + " with ID: " + dummyEntity.getId());
       dummyEntity.setName("New Name");
       dummyEntity.setRank(3333333);
       dummyService.updateDummyEntity(dummyEntity);
   

   Repeating the exact same steps for the remaining entities A and B.

   So what am I doing wrong? Any pointers will be greatly appreciated.

   UPDATE:

   @erencan - yes, I double checked that. Here is what I observed after posing the question here. The Table A and B (the troublesome ones) has this issue:

   • When I ask repository service to return me an instance for a given ID for Table A and B, it returns them alright
   • when a change is made to that instance using the setXXX(), and updateEntity() or saveEntity() is called (as shown using the demo entity code above), the save/update inserts a new entity in the table with exact attribute values as the old one, but with the new changes incorporated (this was observed by removing the unique key constraint on the Table A and B).
   • Later, when I query these newly created entities using their ID in the JPA/Java code, and perform the exact same steps (change some attribute and call save/update on the repository), these newly created entities (rows in the db table) get updated in exactly the manner expected.
   • So it seems that the original entity (row) is somehow 'locked' and updates are prevented. Therefore, the JPA save/update calls simply tries to create a fresh one instead; and since the fresh entity still has the same set of attribute values, any UNIQUE key constraint will start complaining (of course)
   • I did some tests on the existing tables (ETL'd into the DB), and find this behavior consistent: If the entity is NOT created by JPA, then JPA can read the data alright, but cannot update them; if the entity IS created by JPA, then JPA can read AND update them alright.

   Not sure how this happens though (yet). Here is the schema of Table A and B:

   CREATE TABLE IF NOT EXISTS `mydbschema`.`table-B` (
     `id` INT NOT NULL AUTO_INCREMENT COMMENT 'This is PK',
     `name` VARCHAR(100) NULL,
     `city` VARCHAR(50) NOT NULL,
     `state` VARCHAR(30) NOT NULL,
     `zip` VARCHAR(5) NOT NULL,
     `country` VARCHAR(50) NOT NULL,
     `overall_rank` INT NULL,
     `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `insert_src_ver_id` INT NULL,
     `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
     `update_src_ver_id` INT NULL,
     `version` INT NULL,
     PRIMARY KEY (`id`),
     UNIQUE INDEX `name_UNIQUE` (`name` ASC))
   ENGINE = InnoDB;
   

   Another one:

   CREATE TABLE IF NOT EXISTS `mydbschema`.`table-A` (
     `id` INT NOT NULL AUTO_INCREMENT COMMENT 'This is PK',
     `full_name` VARCHAR(200) NULL,
     `gender` VARCHAR(1) NULL,
     `year_of_birth` VARCHAR(4) NULL,
     `title_code` VARCHAR(6) NULL,
     `business_role` VARCHAR(30) NULL,
     `graduation_year` VARCHAR(4) NULL,
     `residency` VARCHAR(500) NULL,
     `table-B_id` INT NULL,
     `npi_num` VARCHAR(10) NULL,
     `upin` VARCHAR(20) NULL,
     `dea_num` VARCHAR(20) NULL,
     `dea_expire_date` VARCHAR(10) NULL,
     `year_started_practicing` VARCHAR(4) NULL,
     `high_prescriber` VARCHAR(1) NULL,
     `board_action` VARCHAR(1) NULL,
     `mdi_qscore` INT NOT NULL DEFAULT 0,
     `mdi_cscore` INT NOT NULL DEFAULT 0,
     `aco_id` INT NULL,
     `npp` INT NULL,
     `medicaid_id` VARCHAR(50) NULL,
     `medicaid_state` VARCHAR(2) NULL,
     `medicare_id` VARCHAR(50) NULL,
     `medicare_state` VARCHAR(2) NULL,
     `medicare_provider_flag` VARCHAR(1) NULL,
     `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `insert_src_ver_id` INT NULL,
     `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
     `update_src_ver_id` INT NULL,
     `version` INT NULL,
     PRIMARY KEY (`id`),
     UNIQUE INDEX `hdsphy_id_UNIQUE` (`id` ASC),
     UNIQUE INDEX `npi_num_UNIQUE` (`npi_num` ASC),
     UNIQUE INDEX `dea_num_UNIQUE` (`dea_num` ASC),
     CONSTRAINT `fk_table-A_table-B`
       FOREIGN KEY (`table-B_id`)
       REFERENCES `mydbschema`.`table-B` (`id`)
       ON DELETE NO ACTION
       ON UPDATE NO ACTION,
   ENGINE = InnoDB;
   

   Here is the FULL Stack trace:

   2013-09-30 10:20:49,705 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Duplicate entry '1568673648' for key 'npi_num_UNIQUE'
   Exception in thread "main" org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
   at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:321)
   at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
   at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
   at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
   at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:84)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at com.sun.proxy.$Proxy60.save(Unknown Source)
   at com.mdinsider.platform.domain.PhysicianServiceImpl_Roo_Service.ajc$interMethod$com_mdinsider_platform_domain_PhysicianServiceImpl_Roo_Service$com_mdinsider_platform_domain_PhysicianServiceImpl$updatePhysician(PhysicianServiceImpl_Roo_Service.aj:48)
   at com.mdinsider.platform.domain.PhysicianServiceImpl.updatePhysician(PhysicianServiceImpl.java:1)
   at com.mdinsider.platform.domain.PhysicianService_Roo_Service.ajc$interMethodDispatch1$com_mdinsider_platform_domain_PhysicianService_Roo_Service$com_mdinsider_platform_domain_PhysicianService$updatePhysician(PhysicianService_Roo_Service.aj)
   at com.mdinsider.platform.mediblip.engine.TestDBSave.saveMDIQualityScore(TestDBSave.java:94)
   at com.mdinsider.platform.mediblip.engine.TestDBSave.main(TestDBSave.java:142)
   Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1316)
   at org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:898)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:241)
   at com.sun.proxy.$Proxy31.merge(Unknown Source)
   at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:345)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:606)
   at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:334)
   at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:319)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
   ... 12 more
   Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
   at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74)
   at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
   at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136)
   at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
   at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2975)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3487)
   at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
   at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:377)
   at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:214)
   at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:194)
   at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:178)
   at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:321)
   at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:286)
   at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:192)
   at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:125)
   at org.hibernate.ejb.event.EJB3MergeEventListener.saveWithGeneratedId(EJB3MergeEventListener.java:71)
   at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:236)
   at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:216)
   at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:154)
   at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:76)
   at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:914)
   at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:898)
   at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:902)
   at org.hibernate.ejb.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:889)
   ... 31 more
   Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1568673648' for key 'npi_num_UNIQUE'
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
   at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
   at com.mysql.jdbc.Util.getInstance(Util.java:386)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
   at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)
   

   解决方案

   Alright, problem solved. After trying out various ways to isolate the problem, I saw that any rows added manually or by the ETL will be selectively treated for this exception. Any rows added by the Spring Data JPA/Java code will work fine.

   Hence, the issue was with manually inserted rows. Then I realized that VERSION field was sitting there with NULL value for those manually inserted rows. When I set the values to 0, the manually inserted rows became acceptable to JPA.

   Another alternative is to not have version field at all in of your tables.

   Hope this helps folks who come across the same problem.

   这篇关于SqlExceptionHelper - 唯一键的重复项使用Spring Data JPA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！