title: JPA2 - Getting child entities of joined entities recursively using Criteria API as Dynamic Query
body: <p>I want to understand how to apply certain conditions using Criteria API instead of JPQL, particularly if Criteria can be used to recursively get child entities from Joins in the same way that JPQL can, through Join hierarchies. </p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>The comments from Tiny and Chris prompted me to first be clear what i'm trying to achieve: </p>
  
  <p>My example has 4 entities as per the below diagram. Enitemshas a ManyToOne relationship with Ensources. Entopics has OneToMany relationship with Enitems. Entopics has a OneToMany relationship with SegmentsNew. </p>
  
  <p><a href="https://i.stack.imgur.com/kZAig.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kZAig.jpg" alt="enter image description here"></a></p>
  
  <p>I am building a search page where the user can find a chosen item by entering as many or as little in the search criteria. In the below example a search for "Corporate Law" should return all the items in the Corporate Law segment (even if nothing else is entered). </p>
  
  <p><a href="https://i.stack.imgur.com/W1yGD.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/W1yGD.jpg" alt="enter image description here"></a></p>
  
  <p>My entities:</p>
  
  <p>Enitems:</p>
  
  <pre><code>@Entity
  @Table(name = "enitem")
  
  private static final long serialVersionUID = 1L;
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Basic(optional = false)
  @Column(name = "itemid")
  private Integer itemid;
  @Size(max = 500)
  @Column(name = "itemname")
  private String itemname;
  @Column(name = "daterec")
  @Temporal(TemporalType.DATE)
  private Date daterec;
  @Lob
  @Size(max = 65535)
  @Column(name = "itemdetails")
  private String itemdetails;
  private String enteredby;
  @OneToMany(mappedBy = "items")
  private Collection&lt;Endatamaster&gt; endatamasterCollection;
  @JoinColumn(name = "topicid", referencedColumnName = "topicid")
  @ManyToOne
  private Entopic topics;
  @JoinColumn(name = "sourceid", referencedColumnName = "sourceid")
  @ManyToOne
  private Ensource source;
  </code></pre>
  
  <p>Entopics:</p>
  
  <pre><code>public class Entopic implements Serializable {
  
  private static final long serialVersionUID = 1L;
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Basic(optional = false)
  @Column(name = "topicid")
  private Integer topicid;
  @Size(max = 500)
  @Column(name = "topicname")
  private String topicname;
  @Size(max = 500)
  @Column(name = "description")
  private String description;
  @Column(name = "locksec")
  private boolean locksec;
  @JoinColumn(name = "segmentid", referencedColumnName = "SEGMENTID")
  @ManyToOne
  private Segmentnew segments;
  @JoinColumn(name = "marketid", referencedColumnName = "marketid")
  @ManyToOne
  private Enmarkets markets;
  @OneToMany(mappedBy = "topics")
  private Collection&lt;Enitem&gt; enitemCollection;
  </code></pre>
  
  <p>Ensource:</p>
  
  <pre><code>@Entity
  @Table(name = "ensource")
  @NamedQueries({
  @NamedQuery(name = "Ensource.findAll", query = "SELECT e FROM Ensource e")})
  public class Ensource implements Serializable {
  private static final long serialVersionUID = 1L;
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Basic(optional = false)
  @Column(name = "sourceid")
  private Integer sourceid;
  @Size(max = 500)
  @Column(name = "sourcename")
  private String sourcename;
  @Size(max = 500)
  @Column(name = "description")
  private String description;
  
  @JoinColumn(name = "typeid", referencedColumnName = "typeid")
  @ManyToOne
  private Enitemtype entype;
  </code></pre>
  
  <p>Segmentsnew:</p>
  
  <pre><code>public class Segmentnew implements Serializable {
  private static final long serialVersionUID = 1L;
  @Id
  @Basic(optional = false)
  @NotNull
  @Column(name = "SEGMENTID")
  private Integer segmentid;
  @Size(max = 255)
  @Column(name = "SEGMENTNAME")
  private String segmentname;
  @OneToMany(mappedBy = "segments")
  private Collection&lt;Entopic&gt; entopicCollection;
  @JoinColumn(name = "sectorId", referencedColumnName = "SECTORID")
  @ManyToOne
  private Sectorsnew sectors;
  </code></pre>
  
  <p>A desired JPQL string representation of this would be:</p>
  
  <pre><code>Select e FROM Enitems e WHERE e.topics.topicid = :topicid 
  AND e.source.sourceid = :sourceid; AND e.topics.segments.segmentid = :segmentid  
  </code></pre>
  
  <p>Search page uses JSf Primefaces:</p>
  
  <pre><code>&lt;p:panelGrid columns="2"&gt;
  
                      &lt;p:outputLabel value="Sectors: "/&gt;
                      &lt;p:selectOneMenu value="#{sectorBean.sectorid}"
                                       filter="true"&gt;
                          &lt;f:selectItem itemValue="0" itemLabel="NULL"/&gt;
                          &lt;f:selectItems value="#{sectorBean.secList}"
                                         var="sect"
                                         itemLabel="#{sect.sectorname}"
                                         itemValue="#{sect.sectorid}"/&gt;
                          &lt;f:ajax listener="#{segmentsBean.segFromSec}" render="segs"/&gt;
                      &lt;/p:selectOneMenu&gt;
  
                      &lt;p:outputLabel value="Segments: "/&gt;
                      &lt;p:panel id="segs"&gt;
  
                          &lt;p:selectOneMenu value="#{queryBean.segmentid}"
                                           rendered="#{not empty segmentsBean.menuNormList}"&gt;
  
                              &lt;f:selectItems value="#{segmentsBean.menuNormList}"
                                             var="segs"
                                             itemLabel="#{segs.segmentname}"
                                             itemValue="#{segs.segmentid}"/&gt;
                          &lt;/p:selectOneMenu&gt;
  
                      &lt;/p:panel&gt;
  
                      &lt;p:outputLabel value="Topics: "/&gt;
                      &lt;p:selectOneMenu value="#{queryBean.topicid}" filter="true"&gt;
                          &lt;f:selectItem itemValue="" itemLabel="NULL"/&gt;
                          &lt;f:selectItems value="#{clienTopicBean.publicTopMenu}"
                                         var="pubs"
                                         itemLabel="#{pubs.topicname}"
                                         itemValue="#{pubs.topicid}"/&gt;
  
  
                      &lt;/p:selectOneMenu&gt;
  
                      &lt;p:outputLabel value="Type: "/&gt;
                      &lt;p:selectOneMenu value="#{typeBean.typeid}" filter="true"&gt;
                          &lt;f:selectItem itemValue="" itemLabel="NULL"/&gt;
                          &lt;f:selectItems value="#{typeBean.menuList}"
                                         var="type"
                                         itemLabel="#{type.typename}"
                                         itemValue="#{type.typeid}"/&gt;
  
                          &lt;f:ajax listener="#{sourceBean.sourceFromType}" render="src"/&gt;
                      &lt;/p:selectOneMenu&gt;
  
                      &lt;p:outputLabel value="Sources: "/&gt;
                      &lt;p:panel id="src"&gt;
  
                          &lt;p:selectOneMenu value="#{queryBean.sourceid}"
                                           rendered="#{not empty sourceBean.sourceListNorm}"&gt;
  
                              &lt;f:selectItems value="#{sourceBean.sourceListNorm}"
                                             var="srcs"
                                             itemLabel="#{srcs.sourcename}"
                                             itemValue="#{srcs.sourceid}"/&gt;
                          &lt;/p:selectOneMenu&gt;
  
                      &lt;/p:panel&gt;
                  &lt;/p:panelGrid&gt;
  </code></pre>
  
  <p>This is what I'm trying using CAPI:</p>
  
  <pre><code>public List&lt;Enitem&gt; superQ(Integer topicid, Integer sourceid,
          Integer segmentid) {
      em.clear();
  
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery cq = cb.createQuery(Enitem.class);
      Root&lt;Enitem&gt; rt = cq.from(Enitem.class);
  
      cq.select(rt);
      cq.distinct(true);
  
      List&lt;Predicate&gt; criteria = new ArrayList&lt;Predicate&gt;();
      Predicate whereClause = cb.conjunction();
      // if () {
      Path&lt;Entopic&gt; topJoin = rt.get("topics");
  
  
      if (topicid != 0) {
  
          ParameterExpression&lt;Integer&gt; p
                  = cb.parameter(Integer.class, "topicid");
          whereClause = cb.and(whereClause, cb.equal(topJoin.get("topicid"), p));
      }
      if (segmentid != 0) {
  
          ParameterExpression&lt;Integer&gt; p
                  = cb.parameter(Integer.class, "segmentid");
          whereClause = cb.and(whereClause, cb.equal(topJoin.get("segments").get("segmentid"), p));
      }
      //}
  
      if (sourceid != 0) {
          ParameterExpression&lt;Integer&gt; p
                  = cb.parameter(Integer.class, "sourceid");
          whereClause = cb.and(whereClause, cb.equal(rt.get("source").get("sourceid"), p));
      }
  
  //    if(whereClause.getExpressions().isEmpty()) {
  //        throw new RuntimeException("no criteria");
  //    }
      cq.where(whereClause);
  
      TypedQuery q = em.createQuery(cq);
      if (topicid != 0) {
          q.setParameter("topicid", topicid);
      }
      if (segmentid != 0) {
          q.setParameter("segmentid", segmentid);
      }
  
      if (sourceid != 0) {
          q.setParameter("sourceid", sourceid);
      }
  
      return q.getResultList();
  
  }
  </code></pre>
  
  <p><strong>It is important to state if(entityName != 0) rather than if(entityName != null), which I was previously doing and this caused the application to require all parameters to be populated by the user. This is probably because integer value of null is actually the number zero?</strong> </p>
  
  <p>The generated SQL:</p>
  
  <pre><code>SELECT DISTINCT t1.itemid, t1.daterec, t1.ENTEREDBY, t1.itemdetails, t1.itemname, 
  t1.sourceid, t1.topicid FROM enitem t1 LEFT OUTER JOIN entopic t0 ON (t0.topicid 
  = t1.topicid) LEFT OUTER JOIN ensource t2 ON (t2.sourceid = t1.sourceid) WHERE 
  (((t0.topicid = ?) AND (t2.sourceid = ?)) AND (t0.segmentid = ?))
  </code></pre>
  
  <p>The application is behaving dynamically in that the user only needs to enter any single value in the search page and a list is returned corresponding to that value. <strong>The problem I'm now having is that the same results are being returned if I do a second query even though I already cleared the EntityManager at the start of the method. SO the application only works if it's restarted. Do I need to refersh the entities?</strong> </p>