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 ?
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));
}
}
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 :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With