CODEWITHSUNDEEP
javajpapredicate
Aliaksei
Aliaksei

Reputation: 1447

CriteriaBuilder select ManyToMany

I have such code

@OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
public Registration getRegistration() {
    return registration;
}

@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(name = "COMPANY_USER", joinColumns = @JoinColumn(name = "USER_ID", referencedColumnName = "ID"), inverseJoinColumns = @JoinColumn(name = "COMPANY_ID", referencedColumnName = "ID"))

public List<CompanyInfo> getCompanyInfoList() {
    return companyInfoList;
}

For exmaple in Registration (@OneToOne) I search so

private Predicate[] buildPredicates(UserFilter example, CriteriaBuilder qb, Root root) {
    ArrayList<Predicate> predicates = new ArrayList<>();
    if (example != null) {
    predicates.add(qb.like(qb.upper(root.get(UserInfo_.registration).get(Registration_.messageCode)), "%TEST%"));
    }
    return predicates.toArray(new Predicate[predicates.size()]);
}

And how to correctly search in the List CompanyInfo (@ManyToMany)

predicates.add(qb.like(qb.upper(root.get(UserInfo_.companyInfoList).... ??

For example I want get such sql

SELECT u.id, u.login_name, c.name
  FROM user_info  u
       INNER JOIN company_user cu ON cu.user_id = u.id
       INNER JOIN company_info c ON cu.company_id = c.id
 WHERE UPPER (c.name) LIKE 'ОДО%'

company_user is Link table.

With CriteriaBuilder

    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery<Long> query = qb.createQuery(Long.class);
    Root<UserInfo> root = query.from(UserInfo.class);
    query.select(qb.count(root.get(UserInfo_.id)));

    Subquery<String> subquery = query.subquery(String.class);
    Root<CompanyInfo> subRoot = subquery.from(CompanyInfo.class);
    subquery.select(subRoot.get(CompanyInfo_.id));

    List<Predicate> restrictions = buildPredicates(example, qb, root);
    List<Predicate> restrictionsSub = buildPredicatesSub(example, qb, subRoot);
    Predicate[] predicates = joinList(restrictions, restrictionsSub);
    query.where(predicates);

    return em.createQuery(query.distinct(true)).getSingleResult();

Upvotes: 1

Views: 3636

Answers (1)

Dave Pateral
Dave Pateral

Reputation: 1475

Try this, Entity_A and Entity_B are the manyToMany associated entities:

Join<Entity_A, Entity_B> join = root.join("associationList", JoinType.INNER);
predicates.add(cb.like(join.get("name").as(String.class), "%test%"));

Upvotes: 3

Related Questions