Part of my Java EE application is user management. My development environment is Netbeans 12.0 on Windows 10, JDK 14, Glassfish server 5.1, Apache Derby DB, and the eclipse persistence JPA. Currently, in the development phase, I have configured the JPA to drop and create tables, whenever, I restart the server.
<?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>
I have an Entity bean “User” that is configured to use Auto-generated primary keys for the entity:
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;
}
Whenever the server starts, I populated all tables with test data from XML file using JAXB mechanism. Here is a sample of my UserList.xml for the “User” entity bean.
<?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>
When the application is up and running, I can visually validate the data through the front end JSF (Java Server Faces) pages and everything looks fine, which indicates the JAXB code has successfully loaded the initial user data into the database. The horrid problem starts when I try to add a new User from the front end JSF page that terminates with the following exception:
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)
I am not sure why I am getting this error when the “User” entity leverages the Auto-key generation, the initial 3 pieces of data have been already loaded into the table, and that the auto-key generator has already been incremented to 4 (assuming the JPA starts generating keys from index=1)? So the first user I add through the JSF front end (which shares the same persistence code as JAXB did to add the initial data from the XML file to the table) should be assigned id=5?
Initially, I thought, maybe the Id fields in the XML file (i.e. 1) had something to do with this error but again with the Auto-key generation scheme, the Id in XML should be overridden by the JPA auto-generated value?
I have already read many posts on this forum and elsewhere regarding the error but didn’t find any common denominator with my situation other than the error. Thanks for any lucid explanation of the root cause of this error and how I can fix it without having the application to generate the primary keys.
Update 1: Leftover data in the table
After a thorough investigation, I found out that there are always leftover data in the User table at the application startup, which violates the drop-and-create policy I have selected in the persistence.xml file (perhaps a JPA issue?) In this case, when the container calls my Singleton class @postConstruct annotated method loadData(), to load initial data from an XML file, the auto key generation starts at 1, and that causes conflict with one of the leftover rows having the same Id. I modified the code to delete all rows from the table at the server startup and the problem disappeared, however, I am seeing the following exception in the log file that doesn’t cause any problems or interruptions for the application: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
I am definitely not updating or tampering with any primary keys and I left that task to the auto-generate feature so I am not sure why I am getting this exception? (if source code needed, I would be happy to provide it).
Advertisement
Answer
In this case, Eclipse JPA chooses the strategy based on a sequence (the SEQ_GEN sequence created in a database; it calls next value on that sequence), it’s not an auto-generated column.
So, in this case the constraint violation exception is understandable – first, on start-up the application creates 3 users with ids: 1, 2, 3 without touching the sequence, and then you try to create the 4th user, but the first value in the sequence is 1.
You can enable the show-sql jpa property to trace SQLs sent to a database.