JPA Criteria Query - How to Avoiding Duplicate Joins

Question

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.

For instance i have the following structure of Tables and JPA entities :

ACCOUNT
      ACCOUNT_ID
      ACCOUNT_TYPE


PERSON
      NAME
      AGE
      ACCOUNT_ID ( FK TO ACCOUNT ) 
      ADDRESS_ID ( FK TO ADDRESS ) 

ADDRESS
      ADDRESS_ID
      LOCATION
      COUNTRY

So assuming that i m using static metamodel implementation for applying criteria queries.

This is example of a wrong code that can generate duplicate joins:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Account> 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<Account> query = entityManager.createQuery(cq);
     List<Account> result = query.getResultList();

The code above will generate a SQL with mutiples joins of the same table :

 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>18
        and address6_.country=?

A simple solution is to keep instances of the Joins to reuse in multiples predicates like it :

   Root<Account> accountRoot = cq.from(Account.class);
   Join<Account,Person> personJoin= accountRoot.join(Account_.person);
   Join<Person,Address> 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")
      )
     )

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 !

What is the better way to avoid it ?

Answer 1

A suggestion for avoid it is to use a builder class to encapsulate the joins , see below.

public class AccountCriteriaBuilder {

        CriteriaBuilder cb;
        CriteriaQuery<Account> cq;

        // JOINS INSTANCE
        Root<Account> accountRoot;
        Join<Account,Person> personJoin;
        Join<Person,Address> personAddressJoin;

        public AccountCriteriaBuilder(CriteriaBuilder criteriaBuilder) {
            this.cb =  criteriaBuilder;
            this.cq = cb.createQuery(Account.class);
            this.accountRoot = cq.from(Account.class);
        }

        public CriteriaQuery buildQuery() {
            Predicate[] predicates = getPredicates();
            cq.select(accountRoot).where(predicates);
            return cq;
        }

        public Predicate[] getPredicates() {

           List<Predicate> predicates = new ArrayList<Predicate>();

           predicates.add(cb.equal(getPersonJoin().get(Person_.name), "Roger"));
           predicates.add(cb.greaterThan(getPersonJoin().get(Person_.age), 18));
           predicates.add(cb.equal(getPersonAddressJoin().get(Address_.country),"United States"));

           return predicates.toArray(new Predicate[predicates.size()]);
        }

        public Root<Account> getAccountRoot() {
            return accountRoot;
        }

        public Join<Account, Person> getPersonJoin() {
            if(personJoin == null){
                personJoin = getAccountRoot().join(Account_.person);
            }
            return personJoin;
        }

        public Join<Person, Address> getPersonAddressJoin() {
            if(personAddressJoin == null){
                personAddressJoin = getPersonJoin().join(Person_.address);
            }
            return personAddressJoin;
        }


}

The “ace in the hole” is the lazy loads for each required join instance, it will avoid duplicate joins and also to simplify the navigation process.

Finally, just call the builder like below :

AccountCriteriaBuilder criteriaBuilder = new AccountCriteriaBuilder(em.getCriteriaBuilder());
TypedQuery<Account> query = em.createQuery(criteriaBuilder.buildQuery());
List<Account> result = query.getResultList();

Enjoy :)