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

We use the following utility method to avoid duplicate joins

public class CriteriaApiUtils {
  public static <X, Y> ListJoin<X, Y> join(Root<X> criteriaRoot,
                                             ListAttribute<? super X, Y> attribute,
                                             JoinType joinType
  ) {
    return (ListJoin<X, Y>) criteriaRoot.getJoins().stream()
        .filter(j -> j.getAttribute().getName().equals(attribute.getName()) && j.getJoinType().equals(joinType))
        .findFirst()
        .orElseGet(() -> criteriaRoot.join(attribute, joinType));
  }
}

Answer 2

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 :)

Answer 3

It's been a while, I know, but maybe someone can help, since it took me quite a while to find a solution to this problem.

Straight to the point. I took the solution proposed by user1096250 and simplified it a bit to fit my needs:

This method is the final method, expressed in generic form:

public Join<X, Y> getJoin(Root<X> root, String attribute, JoinType joinType) {
    return (Join<X, Y>) root.getJoins().stream()
            .filter(r -> r.getAttribute().getName().equals(attribute) && r.getJoinType().equals(joinType))
            .findFirst()
            .orElseGet(() -> root.join(attribute, joinType));
}

And this is the implementation. For example, this is a spec with between of my spec class for my entity CasoDeNegocioEntity

public Specification<CasoDeNegocioEntity> specifPc_fechaCumplimiento(LocalDateTime fechaInicio, LocalDateTime fechaTermino, String operador) {
    switch (operador) {
        case "between":
            return (root, query, criteriaBuilder) ->
                    criteriaBuilder.between(getJoin(root, "plazosCumplimientoEntity", JoinType.INNER).get("fechaCumplimiento"), fechaInicio, fechaTermino);
    }
    return null;
}

In short, what this does is that it evaluates existing joins. If it exists, it returns the first one it finds, if it doesn't exist, it creates it. This avoids duplicate "inner join" and therefore duplicate rows in the result. And of course, it is no longer necessary to use the DISTINCT clause.

Thanks to all for the help. With this post more this blog, I learned a lot about Specifications. Greetings!