语句被中止，因为它会导致键值重复

hfyxw5xn  于 2021-06-27  发布在  Java
关注(0)|答案(1)|浏览(314)

我的javaee应用程序的一部分是用户管理。我的开发环境是windows10上的netbeans12.0、jdk 14、glassfish服务器5.1、apachederby db和eclipse持久性jpa。目前，在开发阶段，我已经将jpa配置为删除和创建表，每当我重新启动服务器时。

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.2" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
  <persistence-unit name="listserviceDB">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>java:comp/DefaultDataSource</jta-data-source>
    <properties>
      <property name="eclipselink.ddl-generation.output-mode" value="both"/>
      <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
    </properties>
  </persistence-unit>
</persistence>

我有一个实体bean“user”，它被配置为对实体使用自动生成的主键：

public class User implements Serializable {

    private static final long serialVersionUID = 1005L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @NotNull
    private String userId;
    @NotNull
    private String password;
    ............................
    @NotNull
    private String userRole;
}

每当服务器启动时，我使用jaxb机制用xml文件中的测试数据填充所有表。下面是“user”实体bean的myuserlist.xml示例。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<users>
    <user>
        <id>2</id>
        <userId>AMackinzy</userId>
        <password>password</password>
        <userRole>update</userRole>
        <firstName>Adam</firstName>
        <lastName>Mc Keinzy</lastName>
        <officePhone>(718)-815-8110</officePhone>
        <cellPhone>(845)-340-5410</cellPhone>
        <company>MMC</company>
    </user>
    <user>
        <id>3</id>
        <userId>AOzguer23</userId>
        <password>password</password>
        <userRole>consumer</userRole>
        <firstName>Albert</firstName>
        <lastName>Ozgur</lastName>
        <officePhone>(213)-567-2390</officePhone>
        <cellPhone>(917)-301-3491</cellPhone>
        <company>Bernstern</company>
    </user>
    <user>
        <id>1</id>
        <userId>admin</userId>
        <password>password</password>
        <userRole>superadmin</userRole>
        <firstName>Joseph</firstName>
        <lastName>Ottaviano</lastName>
        <officePhone>718-815-8111</officePhone>
        <cellPhone>917-971-6854</cellPhone>
        <company>Lemmen Inc.</company>
    </user>
</users>

当应用程序启动并运行时，我可以通过前端jsf（javaserverfaces）页面直观地验证数据，一切看起来都很好，这表明jaxb代码已经成功地将初始用户数据加载到数据库中。当我尝试从前端jsf页面添加新用户时，可怕的问题就开始了，该页面以以下异常终止：

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.0.v20170811-d680af5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL201230210444600' defined on 'PERSISTENCE_USER'.
Error Code: 20000
Call: INSERT INTO PERSISTENCE_USER (ID, CELLPHONE, COMPANY, DATEOFBIRTH, FIRSTNAME, LASTNAME, OFFICEPHONE, PASSWORD, USERID, USERROLE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    bind => [10 parameters bound]
Query: InsertObjectQuery(org.me.mavenlistservicedb.entity.User@79460d95)
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:905)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:967)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:637)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2093)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:309)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:270)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:256)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:405)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:165)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:502)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
    at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:314)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911)

当“user”实体利用自动键生成时，初始的3个数据已经加载到表中，并且自动键生成器已经增加到4（假设jpa从index=1开始生成键），我不确定为什么会出现这个错误？因此，我通过jsf前端添加的第一个用户（与jaxb共享相同的持久性代码，将xml文件中的初始数据添加到表中）应该被分配id=5？
起初，我想，也许xml文件（即1）中的id字段与此错误有关，但是对于自动密钥生成方案，xml中的id应该被jpa自动生成的值覆盖？
我已经在这个论坛和其他地方读了很多关于这个错误的帖子，但是除了这个错误之外，我没有发现任何和我的情况有共同点的地方。感谢您对这个错误的根本原因以及我如何在没有应用程序生成主键的情况下修复它的清晰解释。

更新1：表中的剩余数据

经过彻底的调查，我发现在应用程序启动时，用户表中总是有剩余的数据，这违反了我在persistence.xml文件中选择的drop and create策略（可能是jpa问题？），在本例中，当容器调用我的singleton class@postconstruct annotated method loaddata（）时，要从xml文件加载初始数据，自动密钥生成从1开始，这会导致与具有相同id的剩余行之一发生冲突。我修改了代码，在服务器启动时从表中删除所有行，但是问题消失了，我在日志文件中看到以下异常，它不会导致应用程序出现任何问题或中断：

Caused by: Exception [EclipseLink-7251] (Eclipse Persistence Services - 2.7.0.v20170811-d680af5): org.eclipse.persistence.exceptions.ValidationException
Exception Description: The attribute [id] of class [org.me.mavenlistservicedb.entity.User] is mapped to a primary key column in the database. Updates are not allowed.
    at org.eclipse.persistence.exceptions.ValidationException.primaryKeyUpdateDisallowed(ValidationException.java:2551)
    at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.writeFromObjectIntoRowWithChangeRecord(AbstractDirectMapping.java:1265)
    at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildRowForUpdateWithChangeSet(ObjectBuilder.java:1773)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.updateObjectForWriteWithChangeSet(DatabaseQueryMechanism.java:1043)
    at org.eclipse.persistence.queries.UpdateObjectQuery.executeCommitWithChangeSet(UpdateObjectQuery.java:84)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:314)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:810)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2979)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1824)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitChangedObjectsForClassWithChangeSet(CommitManager.java:273)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:131)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4384)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1491)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1581)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3256)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:355)
    at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:158)
    ... 29 more

我肯定没有更新或篡改任何主键，我把任务留给了自动生成功能，所以我不知道为什么我得到这个例外(如果需要源代码，我很乐意提供）。

Javajpa-2.2

1条答案

按热度按时间
kyvafyod

kyvafyod1#

在这种情况下，EclipseJPA根据一个序列（在数据库中创建的seqgen序列；它叫 next value 在该序列上），它不是自动生成的列。
因此，在这种情况下，约束冲突异常是可以理解的—首先，在启动时，应用程序会创建3个ID为1、2、3的用户，而不触及序列，然后尝试创建第4个用户，但序列中的第一个值是1。
您可以启用 show-sql jpa属性来跟踪发送到数据库的sql。

赞(0）分享  回复(0）举报  2021-06-27
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com