Details
Major Description
Summary: Programatically, whether using a criteria query, JPQL or native query, the child class data contained in the joined table cannot be accessed. However, a native query executed in MySQL Workbench does return fields in both the parent and child tables.
This problem was posted a month ago on both the OpenJPA Nabble forum:
[+http://openjpa.208410.n2.nabble.com/Criteria-query-ManyToOne-with-join-table-not-working-td7590630.html+]
and Stack Overflow:
[+https://stackoverflow.com/questions/49867134/criteria-query-on-entities-linked-by-a-join-table+]
No solution has been forthcoming.
Organization
The Ledger entity holds the details of a transaction including, optionally, the associated Person entity (denominated counterparty) . The Person entity holds a List of Ledger entries (denominated transactions) in which that person engaged. The entities are linked in a bi-directional relationship using a join table (trx_person) containing columns for the pertinent Person.id and Ledger.id. The database is MySQL 5.7 and the ORM is OpenJPA 2.4.2 (JPA 2.1) running under Tomee 7.0.4 (Tomcat 8.5.20) on Windows 10 Pro (v 1709). Saving a transaction writes data to the ledger and person tables and records the respective IDs in the trx_person join table. The problem arises when attempting to execute a query (whether criteria, JPQL or native) to retrieve data from the parent and child tables, resulting in either an error or the failure to retrieve data from the child table.
Ledger Entity (parent):
@ManyToOne(fetch = FetchType.EAGER,
cascade = {CascadeType.PERSIST, CascadeType.MERGE{color:#000000}})
@JoinTable(
name = "trx_person",
joinColumns =
@JoinColumn(name = "trx_ID", referencedColumnName = "id"),
inverseJoinColumns =
@JoinColumn(name = "person_ID", referencedColumnName = "id")
)
protected Person counterparty;
Person Entity
@OneToMany(fetch = FetchType.EAGER, mappedBy = "counterparty")
private List<Ledger> transactions = new ArrayList<>();
Test Class
public class LedgerEx extends BaseDAOImpl<Ledger, Integer>
implements Serializable {
private static final long serialVersionUID = 1L;
@SuppressWarnings("unchecked")
private List<Ledger> execute(Query theQuery) {
List<Ledger> results = null;
String qStr = theQuery.unwrap(org.apache.openjpa.persistence.QueryImpl.class).getQueryString();
LOG.info("UNWRAPPED QUERY: " + qStr);
try {
results = theQuery.getResultList();
} catch (Exception e) {
e.printStackTrace();
}
return results;
}
public List<Ledger> retrieveCriteria(String lastName) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Ledger> q = cb.createQuery(Ledger.class);
Root<Ledger> ledger = q.from(Ledger.class);
Join<Ledger, Person> person = ledger.join(Ledger_.counterparty, JoinType.LEFT);
List<Predicate> predicates = new ArrayList<Predicate>();
Path<String> lnPath = person.get(Person_.lastName);
predicates.add(cb.like(lnPath, lastName + "%"));
q.select(ledger).distinct(true);
q.where(predicates.toArray(new Predicate[predicates.size()]));
Query theQuery = em.createQuery(q);
LOG.info("CRITERIA QUERY");
return execute(theQuery);
}
public List<Ledger> retrieveJPQL(String lastName) {
String ln = lastName.trim() + "%";
StringBuilder selectBldr = new StringBuilder("SELECT l FROM Ledger l ");
selectBldr.append("LEFT JOIN l.counterparty cp WHERE ")
.append("cp.lastName LIKE :lastName");
Query theQuery = null;
String qStr = selectBldr.toString();
theQuery = em.createQuery(qStr, Ledger.class);
theQuery.setParameter("lastName", ln);
LOG.info("JPQL QUERY");
return execute(theQuery);
}
public List<Ledger> retrieveNative(String lastName) {
StringBuilder selectBldr = new StringBuilder("SELECT * FROM Ledger l ");
selectBldr.append("LEFT OUTER JOIN trx_person tp ON l.id = tp.trx_ID ")
.append("LEFT OUTER JOIN person p ON p.id = tp.person_ID");
String ln = lastName.trim() + "%";
selectBldr.append(" WHERE p.last_name LIKE '" + ln + "'");
Query theQuery = null;
String qStr = selectBldr.toString();
theQuery = em.createNativeQuery(qStr, Ledger.class);
LOG.info("NATIVE QUERY");
return execute(theQuery);
}
Criteria Query:
UNWRAPPED QUERY: SELECT DISTINCT l FROM Ledger l LEFT JOIN l.counterparty ? WHERE l.counterparty.lastName LIKE 'bel%'
JPQL:
UNWRAPPED QUERY: SELECT l FROM Ledger l LEFT JOIN l.counterparty cp WHERE cp.lastName LIKE :lastName
In either case, an NPE is thrown by the join method of the org.apache.openjpa.jdbc.sql SelectImpl class, which is caused by a null argument fk (ForeignKey):
<openjpa-2.4.2-r422266:1777108 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Failed to execute query "SELECT l FROM Ledger l LEFT JOIN l.counterparty cp WHERE cp.lastName LIKE :lastName". Check the query syntax for correctness. See nested exception for details.
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:878)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:800)
at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:541)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:274)
at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:290)
at rfpeake.scc.persistence.LedgerEx.retrieveJPQL(LedgerEx.java:78)
at rfpeake.scc.view.JournalView.test(JournalView.java:921)
at rfpeake.scc.view.JournalView$$OwbInterceptProxy0.test(rfpeake/scc/view/JournalView.java)
at rfpeake.scc.view.JournalView$$OwbNormalScopeProxy0.test(rfpeake/scc/view/JournalView.java)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.el.parser.AstValue.invoke(AstValue.java:247)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:267)
at org.apache.webbeans.el22.WrappedMethodExpression.invoke(WrappedMethodExpression.java:52)
at org.apache.webbeans.el22.WrappedMethodExpression.invoke(WrappedMethodExpression.java:52)
at org.apache.myfaces.view.facelets.el.ContextAwareTagMethodExpression.invoke(ContextAwareTagMethodExpression.java:96)
at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:74)
at javax.faces.component.UICommand.broadcast(UICommand.java:120)
at javax.faces.component.UIViewRoot._broadcastAll(UIViewRoot.java:1174)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:365)
at javax.faces.component.UIViewRoot._process(UIViewRoot.java:1660)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:864)
at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:42)
at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:196)
at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:143)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:198)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.openejb.server.httpd.EEFilter.doFilter(EEFilter.java:65)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.tomee.catalina.OpenEJBValve.invoke(OpenEJBValve.java:44)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
at org.apache.tomee.catalina.OpenEJBSecurityListener$RequestCapturer.invoke(OpenEJBSecurityListener.java:97)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1457)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NullPointerException
at org.apache.openjpa.jdbc.sql.SelectImpl$SelectJoins.join(SelectImpl.java:2981)
at org.apache.openjpa.jdbc.sql.SelectImpl$SelectJoins.outerJoinRelation(SelectImpl.java:2961)
at org.apache.openjpa.jdbc.meta.strats.RelationFieldStrategy.joinRelation(RelationFieldStrategy.java:939)
at org.apache.openjpa.jdbc.meta.FieldMapping.joinRelation(FieldMapping.java:985)
at org.apache.openjpa.jdbc.kernel.exps.PCPath.joinRelation(PCPath.java:826)
at org.apache.openjpa.jdbc.kernel.exps.PCPath.initialize(PCPath.java:644)
at org.apache.openjpa.jdbc.kernel.exps.Variable.initialize(Variable.java:119)
at org.apache.openjpa.jdbc.kernel.exps.BindVariableExpression.initialize(BindVariableExpression.java:58)
at org.apache.openjpa.jdbc.kernel.exps.BindVariableAndExpression.initialize(BindVariableAndExpression.java:47)
at org.apache.openjpa.jdbc.kernel.exps.SelectConstructor.initialize(SelectConstructor.java:231)
at org.apache.openjpa.jdbc.kernel.exps.SelectConstructor.newSelect(SelectConstructor.java:172)
at org.apache.openjpa.jdbc.kernel.exps.SelectConstructor.evaluate(SelectConstructor.java:86)
at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.createWhereSelects(JDBCStoreQuery.java:359)
at org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeQuery(JDBCStoreQuery.java:192)
at org.apache.openjpa.kernel.ExpressionStoreQuery$DataStoreExecutor.executeQuery(ExpressionStoreQuery.java:783)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1011)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:869)
... 54 more
Here is the subject method:
package org.apache.openjpa.jdbc.sql;
public class SelectImpl
private Joins join(String name, ForeignKey fk, ClassMapping target,
int subs, boolean inverse, boolean toMany, boolean outer) {
. . .
if (createJoin) {
boolean createIndex = true;
table1 = (inverse) ? fk.getPrimaryKeyTable() : fk.getTable(); // NPE
if (correlatedVar != null)
createIndex = false; // not to create here
alias1 = _sel.getTableIndex(table1, this, createIndex);
}
Native Query:
UNWRAPPED QUERY: SELECT * FROM Ledger l LEFT OUTER JOIN trx_person tp ON l.id = tp.trx_ID LEFT OUTER JOIN person p ON p.id = tp.person_ID WHERE p.last_name LIKE 'bel%'
26473 sccPU TRACE [http-nio-8080-exec-1] openjpa.jdbc.SQL - <t 2001566939, conn 1897759772> executing prepstmnt 92722447
SELECT *
FROM Ledger l LEFT OUTER JOIN trx_person tp ON l.id = tp.trx_ID LEFT
OUTER JOIN person p ON p.id = tp.person_ID
WHERE p.last_name LIKE 'bel%'
26478 sccPU TRACE [http-nio-8080-exec-1] openjpa.jdbc.SQL - <t 2001566939, conn 1897759772> [5 ms] spent
May 17, 2018 1:51:47 PM rfpeake.scc.view.JournalView processTrxList
INFO: resultCount = 5
May 17, 2018 1:51:47 PM rfpeake.scc.view.JournalView display
WARNING: ledger.counterparty is null
While no error is thrown, the Person entity (ledger.counterparty) is not returned. It seems that the join table annotations are detected upon writing data, but the join table information is not utilized when querying data. Executing the query in MySQL Workbench returns data from both tables and will return all columns using: SELECT * FROM ledger l.
See screenshot attached.
