How to map Map with a non-unique value set in JPA 2.1?

Question

I'm trying to map a Map<String, Entity> with a non-unique value set in JPA 2.1. The relationship can be uni-directional for now. There're a trillion explanations how to map a Map<String, Entity> with a unique value set, but this question is not about that.

I have a

@Entity
public class Entity2 implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private Long id;

    [constructors, getter and setter]

and

@Entity
public class Entity1 implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private Long id;
    @OneToMany
    @MapKey(name = "id")
    private Map<String, Entity2> entity2Map = new HashMap<>();

and my persistence.xml contains

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/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_1.xsd">
  <persistence-unit name="richtercloud_jpa-map-with-non-unique-value-set_war_1.0-SNAPSHOTPU" transaction-type="JTA">
    <jta-data-source>jdbc/sample</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="javax.persistence.schema-generation.database.action" value="create"/>
    </properties>
  </persistence-unit>
</persistence>

which causes

Entity2 entity2 = new Entity2(1l);
Entity1 entity1 = new Entity1(2l);
entity1.getEntity2Map().put("a", entity2);
entity1.getEntity2Map().put("b", entity2);
entityManager.persist(entity2);
entityManager.persist(entity1);

to fail due to (I desperately tried to switch the output to English, but don't seem to figure it out; the essence is that the two Entity2 references cause a primary key constraint violation):

Caused by: javax.transaction.RollbackException: Transaction marked for rollback.
    at com.sun.enterprise.transaction.JavaEETransactionImpl.commit(JavaEETransactionImpl.java:490)
    at com.sun.enterprise.transaction.JavaEETransactionManagerSimplified.commit(JavaEETransactionManagerSimplified.java:878)
    at com.sun.ejb.containers.EJBContainerTransactionManager.completeNewTx(EJBContainerTransactionManager.java:721)
    ... 64 more
Caused by: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.qualifier): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: Die Anweisung wurde abgebrochen, weil sie in einer für 'ENTITY1_ENTITY2' definierten Vorgabe für einen eindeutigen oder Primärschlüssel-Constraint bzw. für einen von 'SQL180327114054560' identifizierten und eindeutigen Index zu einem duplizierten Schlüsselwert geführt hätte.
Error Code: 20000
Call: INSERT INTO ENTITY1_ENTITY2 (entity2Map_ID, Entity1_ID) VALUES (?, ?)
    bind => [2 parameters bound]
Query: DataModifyQuery(name="entity2Map" sql="INSERT INTO ENTITY1_ENTITY2 (entity2Map_ID, Entity1_ID) VALUES (?, ?)")
    at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl$1.handleException(EntityManagerSetupImpl.java:745)
    at org.eclipse.persistence.transaction.AbstractSynchronizationListener.handleException(AbstractSynchronizationListener.java:275)
    at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:170)
    at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:68)
    at com.sun.enterprise.transaction.JavaEETransactionImpl.commit(JavaEETransactionImpl.java:452)
    ... 66 more
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.qualifier): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: Die Anweisung wurde abgebrochen, weil sie in einer für 'ENTITY1_ENTITY2' definierten Vorgabe für einen eindeutigen oder Primärschlüssel-Constraint bzw. für einen von 'SQL180327114054560' identifizierten und eindeutigen Index zu einem duplizierten Schlüsselwert geführt hätte.
Error Code: 20000
Call: INSERT INTO ENTITY1_ENTITY2 (entity2Map_ID, Entity1_ID) VALUES (?, ?)
    bind => [2 parameters bound]
Query: DataModifyQuery(name="entity2Map" sql="INSERT INTO ENTITY1_ENTITY2 (entity2Map_ID, Entity1_ID) VALUES (?, ?)")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:902)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:964)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:633)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2056)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:306)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:271)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:251)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.executeNoSelect(StatementQueryMechanism.java:118)
    at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
    at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:3271)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
    at org.eclipse.persistence.mappings.ManyToManyMapping.insertIntoRelationTable(ManyToManyMapping.java:672)
    at org.eclipse.persistence.mappings.ManyToManyMapping.performDataModificationEvent(ManyToManyMapping.java:831)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:160)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4264)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1441)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3168)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:355)
    at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:158)
    ... 68 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Die Anweisung wurde abgebrochen, weil sie in einer für 'ENTITY1_ENTITY2' definierten Vorgabe für einen eindeutigen oder Primärschlüssel-Constraint bzw. für einen von 'SQL180327114054560' identifizierten und eindeutigen Index zu einem duplizierten Schlüsselwert geführt hätte.
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.executeUpdate(Unknown Source)
    at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
    at sun.reflect.GeneratedMethodAccessor117.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.sun.gjc.spi.jdbc40.ProfiledConnectionWrapper40$1.invoke(ProfiledConnectionWrapper40.java:437)
    at com.sun.proxy.$Proxy341.executeUpdate(Unknown Source)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:892)
    ... 92 more
Caused by: org.apache.derby.client.am.SqlException: Die Anweisung wurde abgebrochen, weil sie in einer für 'ENTITY1_ENTITY2' definierten Vorgabe für einen eindeutigen oder Primärschlüssel-Constraint bzw. für einen von 'SQL180327114054560' identifizierten und eindeutigen Index zu einem duplizierten Schlüsselwert geführt hätte.
    at org.apache.derby.client.am.Statement.completeExecute(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readExecute(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readExecute(Unknown Source)
    at org.apache.derby.client.net.NetPreparedStatement.readExecute_(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.readExecute(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.executeUpdateX(Unknown Source)
    ... 100 more

in a JTA-based Java EE project deployed to Payara 4.1.2.181 with EclipseLink and Apache Derby.

@MapKeyColumn is mapping between basic types, e.g. Map<String, String>.

I didn't try @CollectionTable because it

Specifies the table that is used for the mapping of collections of basic or embeddable types.

In case I can't figure this out, I might transform the Map<String, Entity> into a Set<WrappingEntity> where WrappingEntity contains an id, the string value and the Entity2 reference which might even be more efficient, but I'd like to figure this out as well. The approach should generally work since a table with Entity1's id, the string map key and the map Entity2 value could use Entity1's id and the string map key as primary key.

An SSCCE can be found at https://gitlab.com/krichter/jpa-map-with-non-unique-value-set.

What schema do you have? did you let your JPA provider create it? The JPA provider I use creates a PK constraint on the Map table (ENTITY1_ENTITY2 in your case) with the FK column back to ENTITY1, and the KEY, hence value has no such constraint, and it works fine. — , Mar 27 '18 at 10:25
@BillyFrost I have EclipseLink 2.5 generate the schema (I added `persistence.xml` to the question to clarify). Now the big obvious question: which JPA provider did you use to get this working? — Kalle Richter, Mar 27 '18 at 10:28
I use DataNucleus JPA. I don't have the `@MapKey` since that is for where you have the map represented by a column in the value table to be the key of the map; I use a join table to represent the map, like this http://www.datanucleus.org:15080/products/accessplatform_5_1/jpa/mapping.html#one_many_map_join_simple_pc Is this what you're trying to achieve? because you clearly cant have the key stored in the value if using the value multiple times in a single map — , Mar 27 '18 at 10:32
@BillyFrost You're right. This works with DataNucleus 5.1.4, but why not with EclipseLink 2.5, Hibernate, 5.2? Thank for the investigation and the comment. Would you consider it a bug that it's not working in EclipseLink and Hibernate? Any other possibility to get this working without switching to DataNucleus? — Kalle Richter, Mar 27 '18 at 11:07
@crizzis That's a good explanation which I'd accept as an answer, thank you. `@ManyToMany` includes `Entity2.id` in the primary key as well (which complies to your explanation). — Kalle Richter, Mar 27 '18 at 12:51

score 1 · Accepted Answer · answered Apr 11 '18 at 08:58

I'm not sure the JPA 2.1 spec even mentions entities as non-unique values in a to-many association, so that's not really a bug, it's just that the behavior is unspecified (some providers might support such a feature, by chance or by design).

That being said, it makes sense for a unique constraint on Entity2.id to exist in a join table for a one-to-many association. I'd try switching to @ManyToMany (at your own risk, because... unspecified behaviour)

How to map Map with a non-unique value set in JPA 2.1?

1 Answers1