Getting a query intersection in JPA?

问题:

I have: "image" 1:Many "imageToTag" Many:1 "tag"

I want to issue a query that will return all images that have at least tags [a, b, c]. It's not clear to me how one can model this in JPQL. I could build the query string dynamically but that's bad for performance and security reasons. Any ideas?

回答1:

In PSEUDO CODE:

SELECT * FROM IMAGES 
  INNER JOIN IMAGETAGS A
    ON IMAGE.ID = A.IMAGEID AND A.TAGID = 'A'
  INNER JOIN IMAGETAGS B
    ON IMAGE.ID = B.IMAGEID AND B.TAGID = 'B'
  INNER JOIN IMAGETAGS C
    ON IMAGE.ID = C.IMAGEID AND C.TAGID = 'C'

Just guessing, but the equivalent in JPQL would be

Select img from Image img 
 JOIN img.tag ta 
 JOIN img.tag tb 
 JOIN img.tag tc 
  WHERE ta.description = 'A' and tb.description = 'B' and tc.description = 'C'

回答2:

How about this is jpa query language

Select img from Image img where img.tag.description in ('A','B','C');