CODEWITHSUNDEEP
jpa
gammay
gammay

Reputation: 6215

JPA: query for all items in a list where target is also a list

I have two entities:

  1. Engineer - has a set of tags (say skillsets - Java. .NET, etc.)
  2. Tag

An engineer may have any number of tags. And a tag is associated with any number of engineers.

@Entity
public class Engineer implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(nullable = false, length = 12)
    private String id;

    @Column(length = 100)
    private String name;

    @OneToMany(fetch = FetchType.EAGER)
    List<Tag> tags;

    /* Getters and Setters */
}

@Entity
public class Tag implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    private int id;

    @Column(length = 50)
    private String name;

    /* Getters and Setters */
}

Now, I want to query for engineers who have a list of tags associated with them. For example - "Query for engineers with tags 'Java', 'Opensource', and 'Banking'". The query should return engineers who have all the tags associated with them.

I am able to query for one tag:

public List<Engineer> searchMindsByTags(String tag) {
    Query q = em.createQuery("SELECT e FROM Engineer e WHERE '" + tag
            + "' = ANY(SELECT tag.name FROM e.tags tag)");
    return (List<Engineer>) q.getResultList();
}

What I need is:

public List<Engineer> searchMindsByTags(List<String> tags) {
    /* Which query goes here? */
}

Is there a single query which can do this job or do we need iterate over the results progressively to filter?

Upvotes: 0

Views: 984

Answers (3)

James
James

Reputation: 18379

You can either use multiple sub selects, or use a count.

See, https://en.wikibooks.org/wiki/Java_Persistence/Querying#Subselect.2C_querying_a_to_many_relationship_where_all_of_the_relations_are_in_a_list

Upvotes: 0

Ondrej Bozek
Ondrej Bozek

Reputation: 11501

First thing that comes to my mind is that you can construct your query with something like this:

public List<String> searchMindsByTags(List<String> tags)
{

    String queryString = "SELECT e FROM Engineer e ";
    List<Tag> tagobjects=null;
    if (tags != null && !tags.isEmpty()) {
        Query q = entityManager.createQuery("Select t from tag t where t.name IN :tags");
        q.setParameter("tags", tags);
        tagobjects = q.getResultList();
        queryString += " WHERE ";
        for (int i = 0; i < tagobjects.size(); i++) {
            if (i != 0) {
                queryString += "AND ";
            }
            queryString += "?" + (i + 1) + " member of e.tags";
        }
    }
    Query q = entityManager.createQuery(queryString);
    if (tagobjects != null && !tagobjects.isEmpty()) {
        for (int i = 0; i < tagobjects.size(); i++) {
            q.setParameter((i + 1), tagobjects.get(i));
        }
    }
    return (List<Engineer>) q.getResultList();
}

Its quick example, needs some testing.

Upvotes: 0

gerrytan
gerrytan

Reputation: 41143

An engineer may have any number of tags. And a tag is associated with any number of engineers.

You entity design contradicts above requirement.

You modelled Engineer having one to many relationship to Tag. Hence on a tag table you would see a foreign key column engineer_id. Ie: a tag is associated to one (and only 1) engineer.

You need to model the relationship as many to many with join tables. Engineer entity class should have Tag collection decorated with @ManyToMany, Tag entity class should have an Engineer collection decorated with @ManyToMany.

Using the Engineer collection on Tag class you can implement your searchMindsByTags functions

Upvotes: 1

Related Questions