How to request an M: N relationship with JPA2?

Question

I have an object (BlogPost) that contains a collection of M: N elements (tags).

How to request an object (BlogPost), where at least one of its tags matches an element in a set of tags (user defined) with JPA2 (Hibernate).

findBlogPostWithAtLeastOneMatchingTag(Collection<Tag> tags){ ???? } 

My main problem is that I really need to compare two tag collections: - BlogPost tag collection. - the collection I'm looking for

I tried Select p from Post p where p.tags in(:tags) , but this did not work, since my message objects have more than one tag.

So what could I do instead?

My BlogPost object is as follows. It has several tags.

 @Entity public class BlogPost{ /** The tags. */ @ManyToMany() @NotNull private Set<Tag> tags; @NotBlank private String content; ... } 

---

The solution should not be JPQL, JPA-Criteria (not Hibernate-Criteria) would be fine too.

Answer 1

If you like JPA Criteria, this solution is for you:

 List<Integer> myTagsIds = new ArrayList<Integer> (); myTagsIds.add(1); myTagsIds.add(2); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<BlogPost> cq = cb.createQuery(BlogPost.class); Root<BlogPost> blogPost = cq.from(BlogPost.class); SetJoin<BlogPost, Tag> tags = blogPost.join(BlogPost_.tags); Predicate predicate = tags.get(Tag_.id).in(myTagsIds); cq.distinct(true); cq.where(predicate); TypedQuery<BlogPost> tq = em.createQuery(cq); return tq.getResultList(); 

This solution uses the canonical classes MetaModel BlogPost_ and Tag_ , which should be generated by your JPA implementation.

Answer 2

Approach 1:

In SQL, it could be something like:

 SELECT p FROM Post p WHERE (p.tags INTERSECT :tags IS NOT EMPTY); 

Then apply @SqlResultSetMapping .

Approach 2:

You can use the Criteria API and start how you did it, but loop through the Collection<Tag> tags as follows:

 * make a union of single query results from `Select p from Post p where p.tags in(:tags)`; * take distinct over result of union. 

The request will be server-side, and you will not have to do the dirty work in Java.

Answer 3

You can do something like

Select t from column t, where t.tag in (select p.tag from Post p, where p.id =: id)

id is the identifier of the current message. Basically you select a message with tags that are in the tags of the current message.