query with criteria on associated objects

I've been struggling with this Hibernate query problem and I'm hopeful I can get a nudge in the right direction.

I have an object named FlashCard which has associated objects named Tags. A FlashCard can have one or more Tags (kind of like how a StackOverflow Question can be assigned multiple Tags).

I'm trying to use Hibernate to return all FlashCards that are assigned specific Tags. For example, I would like to query for all FlashCards that have BOTH "tag2" and "tag4".

I've tried to approach this with Criteria Queries, Example Queries and HQL all without luck so far.

Here's what the database looks like: mysql> select * from flashcard;

+--------------+------------+----------+
| FLASHCARD_ID | QUESTION   | ANSWER   |
+--------------+------------+----------+
|            1 | Question 1 | Answer 1 |
|            2 | Question 2 | Answer 2 |
|            3 | Question 3 | Answer 3 |
|            4 | Question 4 | Answer 4 |
+--------------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from tag;
+--------+------+
| TAG_ID | NAME |
+--------+------+
|      1 | tag1 |
|      3 | tag2 |
|      2 | tag3 |
|      4 | tag4 |
|      5 | tag5 |
+--------+------+
5 rows in set (0.00 sec)

mysql> select * from flashcard_tags;
+--------+--------------+
| TAG_ID | FLASHCARD_ID |
+--------+--------------+
|      2 |            1 |
|      3 |            1 |
|      4 |            1 |
|      3 |            2 |
|      4 |            2 |
|      5 |            2 |
|      3 |            3 |
+--------+--------------+
7 rows in set (0.00 sec)

Using the data above, you can see that only FlashCard's 1 and 2 have BOTH "tag2" and "tag4".

I created an earlier post just to confirm what SQL (actual SQL) would return the results and here's two working examples.

Example1 of a working SQL query:

SELECT  f.*
FROM开发者_如何学C    (
        SELECT  flashcard_id
        FROM    tags t
        JOIN    flashcard_tags ft
        ON      ft.tag_id = t.tag_id
        WHERE   t.name IN ('tag2', 'tag4')
        GROUP BY
                flashcard_id
        HAVING  COUNT(*) = 2
        ) ft
JOIN    flashcard f
ON      f.flashcard_id = ft.flashcard_id

Example 2 of a working SQL query:

SELECT f.*
FROM flashcard f
  INNER JOIN flashcard_tags ft1 ON f.FLASHCARD_ID = ft1.FLASHCARD_ID
  INNER JOIN tag t1 ON ft1.TAG_ID = t1.TAG_ID AND t1.NAME = 'tag2'
  INNER JOIN flashcard_tags ft2 ON f.FLASHCARD_ID = ft2.FLASHCARD_ID
  INNER JOIN tag t2 ON ft2.TAG_ID = t2.TAG_ID AND t2.NAME = 'tag4'

Here's what the Hibernate mappings look like:

<hibernate-mapping>
   <class name="org.robbins.flashcards.model.FlashCard" table="FLASHCARD">
      <id name="flashCardId" type="int" column="FLASHCARD_ID">
         <meta attribute="scope-set">public</meta>
         <generator class="native" />
      </id>
      <property name="question" type="string">
         <meta attribute="use-in-tostring">true</meta>
         <column name="QUESTION" not-null="true" unique="true" />
      </property>
      <property name="answer" type="text">
         <meta attribute="use-in-tostring">true</meta>
         <column name="ANSWER" not-null="true" />
      </property>
      <set name="tags" table="FLASHCARD_TAGS">
         <meta attribute="field-description">Tags for this FlashCard</meta>
         <key column="FLASHCARD_ID" />
         <many-to-many class="org.robbins.flashcards.model.Tag"
            column="TAG_ID" />
      </set>
   </class>
</hibernate-mapping>

<hibernate-mapping>
   <class name="org.robbins.flashcards.model.Tag" table="TAG">
      <id name="tagId" type="int" column="TAG_ID">
         <meta attribute="scope-set">public</meta>
         <generator class="native" />
      </id>
      <property name="name" type="string">
         <meta attribute="use-in-tostring">true</meta>
         <column name="NAME" not-null="true" unique="true" />
      </property>
      <set name="flashcards" table="FLASHCARD_TAGS" inverse="true">
         <meta attribute="field-description">FlashCards for this Tag</meta>
         <key column="TAG_ID" />
         <many-to-many class="org.robbins.flashcards.model.FlashCard"
            column="FLASHCARD_ID" />
      </set>
   </class>
</hibernate-mapping>

Lastly, here's an excerpt from the generated FlashCard class where you can see the associated Tags:

public class FlashCard  implements java.io.Serializable {
     private int flashCardId;
     private String question;
     private String answer;
     private Set<Tag> tags = new HashSet<Tag>(0);
}

I've run into problems creating Hibernate code that will retrieve the same result. I tried a Criteria query but learned they do not support the SQL "Having" clause or using a "subquery" in the From clause as SQL example #1 does.

I tried Querying by Example (passing in a FlashCard example that had two Tag objects attached) and received no results at all.

I also tried looking into HQL but read "that HQL subqueries can occur only in the select or where clauses."

Rather than make this post any longer, I'll refrain for now from posting examples of my failed Hibernate code. I'll gladly post them if it would be helpful.

Any assistance is most greatly appreciated. Thanks!

Look at this article, It explains how to use HQL with many to many relationship.

http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate/