EclipseLink JPA "invalid table in this context" with @OneToMany Map
dictionary, eclipselink, java, jpa-2.0, one-to-many
Solution
This looks like EclipseLink bug 364922:
An entity with uni-directional OneToMany property and @MapKeyColumn annotation has the correct database tables created, with the mapping table containing a "key column, but persisting an entity only populates the id columns and not the key column. The persist operation does not throw an error, but subsequent queries soon fail with the following error: Exception Description: The field [ORGANIZATION.MAILINGADDRESSES_KEY] in this expression has an invalid table in this context.
For what it's worth, I tested your code using Hibernate/H2 and it works fine.
UPDATE
I just tested this with EclipseLink. If you explicitly set the table for the `MapKeyColumn`, the key is populated correctly. i.e.:
`@MapKeyColumn(name="PHONE_TYPE", table="EMP_PHONE")`
Problem
I'm hoping I'm just doing something silly here... I'm trying to set up JPA annotations for a `Map<String, Phone>` and getting the following stack trace. ``` Exception [EclipseLink-6069] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.QueryException Exception Description: The field [EMPLOYEE.PHONE_TYPE] in this expression has an invalid table in this context. Query: ReadAllQuery(name="phones" referenceClass=Phone ) at org.eclipse.persistence.exceptions.QueryException.invalidTableForFieldInExpression(QueryException.java:739) at org.eclipse.persistence.internal.expressions.FieldExpression.validateNode(FieldExpression.java:281) at org.eclipse.persistence.expressions.Expression.normalize(Expression.java:3259) at org.eclipse.persistence.internal.expressions.DataExpression.normalize(DataExpression.java:369) at org.eclipse.persistence.internal.expressions.FieldExpression.normalize(FieldExpression.java:208) at org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1377) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildNormalSelectStatement(ExpressionQueryMechanism.java:546) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareSelectAllRows(ExpressionQueryMechanism.java:1700) at org.eclipse.persistence.queries.ReadAllQuery.prepareSelectAllRows(ReadAllQuery.java:721) at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:657) at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:614) at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:883) at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:575) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:820) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1109) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:393) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1197) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2875) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1602) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1584) at org.eclipse.persistence.internal.indirection.QueryBasedValueHolder.instantiate(QueryBasedValueHolder.java:112) at org.eclipse.persistence.internal.indirection.QueryBasedValueHolder.instantiate(QueryBasedValueHolder.java:99) at org.eclipse.persistence.internal.indirection.DatabaseValueHolder.getValue(DatabaseValueHolder.java:88) at org.eclipse.persistence.internal.indirection.UnitOfWorkValueHolder.instantiateImpl(UnitOfWorkValueHolder.java:161) at org.eclipse.persistence.internal.indirection.UnitOfWorkValueHolder.instantiate(UnitOfWorkValueHolder.java:222) at org.eclipse.persistence.internal.indirection.DatabaseValueHolder.getValue(DatabaseValueHolder.java:88) at org.eclipse.persistence.indirection.IndirectMap.buildDelegate(IndirectMap.java:110) at org.eclipse.persistence.indirection.IndirectMap.getDelegate(IndirectMap.java:330) at org.eclipse.persistence.indirection.IndirectMap.size(IndirectMap.java:637) at org.eclipse.persistence.internal.queries.MapContainerPolicy.sizeFor(MapContainerPolicy.java:830) at org.eclipse.persistence.internal.indirection.TransparentIndirectionPolicy.instantiateObject(TransparentIndirectionPolicy.java:386) at org.eclipse.persistence.mappings.ForeignReferenceMapping.buildCloneFromRow(ForeignReferenceMapping.java:285) at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildAttributesIntoWorkingCopyClone(ObjectBuilder.java:1594) at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildWorkingCopyCloneFromRow(ObjectBuilder.java:1741) at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObjectInUnitOfWork(ObjectBuilder.java:668) at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:605) at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:564) at org.eclipse.persistence.queries.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:777) at org.eclipse.persistence.queries.ReadAllQuery.registerResultInUnitOfWork(ReadAllQuery.java:783) at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:434) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1150) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:852) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1109) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:393) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1197) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2875) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1602) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1584) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1549) at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:231) at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:411) at aaa.Test.main(Test.java:20) ``` The created tables look like: ``` EMP_PHONE ================================= EMP_ID PHONES_ID PHONE_TYPE ------ --------- ---------- 42 1 NULL EMPLOYEE ================================= ID -- 42 PHONE ================================= ID NUM -- -------- 1 867-5309 ``` The `NULL` `PHONE_TYPE` seems to tell me something funky is going on, and the way that eclipselink is looking for `EMPLOYEE.PHONE_TYPE` makes me think it's thinking about the relationship from `PHONE`->`EMPLOYEE` rather than the other way around. the JPA entities are defined as follows. The phones map is unidirectional, and Phone objects should be shared by multiple Employees (not my actual types, but this simplified example demonstrates the problem) ``` @Entity public class Employee { @Id private long id; @OneToMany(cascade=CascadeType.ALL) @JoinTable(name="EMP_PHONE", joinColumns=@JoinColumn(name="EMP_ID")) @MapKeyColumn(name="PHONE_TYPE") private Map<String, Phone> phones = new HashMap<String, Phone>(); public Employee() {} public Employee(long id) { this.id = id; } public long getId() { return id; } public Map<String, Phone> getPhones() { return phones; } } @Entity public class Phone { @Id private long id; private String num; public String getNum() { return num; } public Phone() {} public Phone(String num) { this.num = num; } public Phone(long id, String num) { this.id = id; this.num = num; } public long getId() { return id; } } ``` using the following test code ``` EntityManagerFactory factory = Persistence.createEntityManagerFactory("test"); EntityManager em = factory.createEntityManager(); Employee employee = new Employee(42); // exception will occur with or without phones added to the map employee.getPhones().put("home", new Phone(1, "867-5309")); em.getTransaction().begin(); em.persist(employee); em.getTransaction().commit(); Query q = em.createQuery("select o from Employee o"); q.setHint("javax.persistence.cache.storeMode", "REFRESH"); q.getResultList(); // EXCEPTION THROWN HERE ``` and persistence.xml: ``` <?xml version='1.0' encoding='UTF-8' ?> <persistence xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd' version='2.0' xmlns='http://java.sun.com/xml/ns/persistence'> <persistence-unit name='test' transaction-type='RESOURCE_LOCAL'> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <class>aaa.Phone</class> <class>aaa.Employee</class> <shared-cache-mode>NONE</shared-cache-mode> <properties> <property name='javax.persistence.jdbc.user' value='uuuuuuuu' /> <property name='javax.persistence.jdbc.password' value='pppppppp' /> <property name='javax.persistence.jdbc.driver' value='oracle.jdbc.driver.OracleDriver' /> <property name='javax.persistence.jdbc.url' value='jdbc:oracle:thin:@localhost:1521:xe' /> <property name='eclipselink.ddl-generation' value='drop-and-create-tables' /> <property name='eclipselink.ddl-generation.output-mode' value='database' /> </properties> </persistence-unit> </persistence> ``` I'm running on Windows 7, using Oracle XE 11g as the database. Thanks in advance for any insights!