CODEWITHSUNDEEP
javasqljpacriteriacriteria-api
Dmitrii
Dmitrii

Reputation: 43

Building a CriteriaBuilder query to compare lists

How to compare the list from the database table and the list from the program code?
The database has user profiles with skills.

    @ManyToMany(cascade = {CascadeType.MERGE}, fetch = FetchType.EAGER)
    @JoinTable(name = "profile_skills",
            joinColumns = @JoinColumn(name = "profile_id", referencedColumnName="id"),
            inverseJoinColumns = @JoinColumn(name = "skill_id", referencedColumnName="id"))
    private Map<Integer, Skill> skills = new TreeMap<>();

Need to find user profiles that have the right skills.
I made a request, but he selects profiles that have at least one match for any of the skills in the list. However, I need to select only those that have all the skills indicated in the list (a match with all the elements in the list).

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Profile> cq = cb.createQuery(Profile.class);
    Root<Profile> root  = cq.from(Profile.class);
    MapJoin<Profile, Integer, Skill> skillMapJoin = root.join(Profile_.skills);
    Expression<String> nameSkill = skillMapJoin.get(Skill_.name);

    List<String> skills = Arrays.asList("Java", "JavaScript");

    cq.where(nameSkill.in(skills)).groupBy(root.get(Profile_.id));

    TypedQuery<Profile> q = em.createQuery(cq);
    List<Profile> profiles = q.getResultList();

For instance:
Profile1.Skills : "Java", "Scala", "Groovy", "JavaScript"
Profile2.Skills : "Java", "Scala", "Groovy"
Profile3.Skills : "Groovy", "JavaScript"

My query returns the result: Profile1, Profile2, Profile3
There must be a result: Profile1

Please tell me how to implement the request I need?
How to compare the list from the database table and the list from the program code?
It is possible not only using CriteriaBuilder, but also other query options like sql or jpql.

Upvotes: 1

Views: 1530

Answers (1)

Dmitrii
Dmitrii

Reputation: 43

I found a solution, although I think it can be done differently.

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Profile> cq = cb.createQuery(Profile.class);
    Root<Profile> root  = cq.from(Profile.class);
    MapJoin<Profile, Integer, Skill> skillMapJoin = root.join(Profile_.skills);
    Expression<String> nameSkill = skillMapJoin.get(Skill_.name);

    List<String> skills = Arrays.asList("Java", "JavaScript");
    Integer sizeListValue = skills.size();
    Expression<Long> countSkills = cb.count(exp);

    cq.where(nameSkill.in(skills))
      .groupBy(root.get(Profile_.id))
      .having(cb.equal(countSkills, sizeListValue));

    TypedQuery<Profile> q = em.createQuery(cq);
    List<Profile> profiles = q.getResultList();

Upvotes: 1

Related Questions