Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4.2
    • Fix Version/s: None
    • Component/s: criteria, jpa, query, sql
    • Labels:
      None
    • Environment:
      Windows 10 Pro (v 1709)
      Tomee 7.0.4 (Tomcat 8.5.20)
      OpenJPA 2.4.2 (JPA 2.1)
      MySQL 5.7

      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.

       

       

       

        Attachments

        1. SampleQuery.PNG
          23 kB
          Robert F. Peake
        2. image-2018-05-19-12-34-48-475.png
          18 kB
          Robert F. Peake

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              OldSplice Robert F. Peake
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: