title: JPA Criteria Query - How to Avoiding Duplicate Joins
body: <p>I need to make a criteria query with a lot of conditional joins and where clauses, in such cases the code  tends become complex and could be produces duplicate joins.</p>
  
  <p>For instance i have the following structure of Tables and JPA entities :</p>
  
  <pre><code>ACCOUNT
        ACCOUNT_ID
        ACCOUNT_TYPE
  
  
  PERSON
        NAME
        AGE
        ACCOUNT_ID ( FK TO ACCOUNT ) 
        ADDRESS_ID ( FK TO ADDRESS ) 
  
  ADDRESS
        ADDRESS_ID
        LOCATION
        COUNTRY
  </code></pre>
  
  <p>So assuming that i m using static metamodel implementation for applying criteria queries.</p>
  
  <p>This is example of a wrong code that can generate duplicate joins:</p>
  
  <pre><code>CriteriaBuilder cb = entityManager.getCriteriaBuilder();
      CriteriaQuery&lt;Account&gt; cq = cb.createQuery(Account.class);
  
      cq.select(accountRoot).where(
       cb.and(
        cb.equal(accountRoot.join(Account_.person).get(Person_.name),"Roger"),
        cb.greaterThan(accountRoot.join(Account_.person).get(Person_.age),18),
        cb.equal(accountRoot.join(Account_.person)                                   
                .join(Person_.address).get(Address_.country),"United States")
        )
       )
  
       TypedQuery&lt;Account&gt; query = entityManager.createQuery(cq);
       List&lt;Account&gt; result = query.getResultList();
  </code></pre>
  
  <p>The code above will generate a SQL with mutiples joins of the same table :</p>
  
  <pre><code> Select
          account0_.account_id as account1_2_,
          account0_.account_type as account2_2_
      from
          account account0_
      inner join
          person person1_
              on account0_.account_id=person1_.account_id
      inner join
          address address2_
              on person1_.address_id=address2_.address_id
      inner join
          person person3_
              on account0_.account_id=person3_.account_id
      inner join
          person person4_
              on account0_.account_id=person4_.account_id
      inner join
          person person5_
              on account0_.account_id=person5_.account_id
      inner join
          address address6_
              on person5_.address_id=address6_.address_id
      where
          person3_.name=?
          and person4_.age&gt;18
          and address6_.country=?
  </code></pre>
  
  <p>A simple solution is to keep instances of the Joins to reuse in multiples predicates like it :</p>
  
  <pre><code>   Root&lt;Account&gt; accountRoot = cq.from(Account.class);
     Join&lt;Account,Person&gt; personJoin= accountRoot.join(Account_.person);
     Join&lt;Person,Address&gt; personAddressJoin = accountRoot.join(Person_.address);
  
     cq.select(accountRoot).where(
       cb.and(
        cb.equal(personJoin.get(Person_.name),"Roger"),
        cb.greaterThan(personJoin.get(Person_.age),18),
        cb.equal(personAddressJoin.get(Address_.country),"United States")
        )
       )
  </code></pre>
  
  <p>Ok , it works , But with a real complex code with several tables and conditional joins for the codes tends to turn a Spaghetti code ! Believe me ! </p>
  
  <p>What is the better way to avoid it ? </p>