CODEWITHSUNDEEP
javahibernatecriteria-api
Sergey
Sergey

Reputation: 963

Add where clause for joined table with JPA Criteria API

I have an one to many association between vocab and vocabml,

what need to be done with simple query (join two tables and filter based on description conditions):

select  * from vocab v
left join vocabml vm on vm.vocabid = v.id and vm.locale in ('uk','en'....)
where v.description like '%syst%'
or vm.description like '%syst%'

My attempt with criteria API raises exception: Caused by: java.lang.IllegalStateException: Illegal attempt to dereference path source [null.vocabmls] of basic type

My code:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Vocab> criteriaQuery = builder.createQuery(Vocab.class);
criteriaQuery.distinct(true);

Root<Vocab> root = criteriaQuery.from(Vocab.class);
root.fetch("vocabmls", JoinType.LEFT);

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


Predicate predicate = builder.or(builder.like(root.get("description"), "%" + description + "%"),
                      builder.like(root.get("vocabmls").get("description"), "%" + description + "%"));
predicates.add(predicate)

if(locales != null){
    List<String> ids = locales.stream().map(Locale::getId).collect(Collectors.toList());
    Join<Vocab, Vocabml> vocabmlJoin = root.join("vocabmls", JoinType.LEFT);
    vocabmlJoin.on(builder.and(vocabmlJoin.<Vocabml> get("localeId").in(ids)));
}

criteriaQuery.where(predicates.toArray(new Predicate[] {})).orderBy(builder.asc(root.get("description")));
return entityManager.createQuery(criteriaQuery).getResultList();

Upvotes: 1

Views: 3212

Answers (1)

Sergey
Sergey

Reputation: 963

I solved it like this, need to add condition using join

Predicate predicateJoin = builder.equal(vocabmlJoin.get("description"), description);
predicates.add(predicateJoin);

It will simple add where vm.description =

Also I changed this one to be OR not AND 

 criteriaQuery.where(builder.or(predicates.toArray(new Predicate[] {})))

Upvotes: 3

Related Questions