Hey Folks,
I'm having trouble constructing a Hibernate query and I'm hoping to get some assistance here. :)
I have an object named FlashCard which has associated objects named Tags. A FlashCard can have one or more Tags (kind of like a Delicious bookmark can be assigned multiple tags).
I'm trying to write a query that returns all FlashCards that are assigned specific multiple Tags. For example, I would like to query for all FlashCards that are assigned
both "tag2" and "tag4".
I'm not sure how best to explain it. I'll include my mappings and relevant code below to hopefully give you a better picture of what I'm trying and currently failing to do.
The code below is a simplified version of what I'm trying to do. This code works if I specify a single Tag.
Code:
@Test
public void getFlashCardsForTwoTags() {
// Ask for a session using the JDBC information we've configured
Session session = sessionFactory.openSession();
Criteria criteria = session.createCriteria(FlashCard.class);
Criteria tagCriteria = criteria.createCriteria("tags");
tagCriteria.add(Restrictions.eq("name", "tag2"));
tagCriteria.add(Restrictions.eq("name", "tag4"));
// populate FlashCard object with the query result
@SuppressWarnings("unchecked")
List<FlashCard> flashCardList = criteria.list();
assertTrue("Expected a Flash Card list", flashCardList.size() > 0);
logger.debug("FlashCard list size: " + flashCardList.size());
}
If I run the above code with multiple Tags, the following query is generated
Code:
select this_.FLASHCARD_ID as FLASHCARD1_2_1_, this_.QUESTION as QUESTION2_1_, this_.ANSWER as ANSWER2_1_, tags3_.FLASHCARD_ID as FLASHCARD2_2_, tag1_.TAG_ID as TAG1_, tag1_.TAG_ID as TAG1_0_0_, tag1_.NAME as NAME0_0_
from FLASHCARD this_ inner join FLASHCARD_TAGS tags3_ on this_.FLASHCARD_ID=tags3_.FLASHCARD_ID inner join TAG tag1_ on tags3_.TAG_ID=tag1_.TAG_ID
where tag1_.NAME=? and tag1_.NAME=?
Code:
<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>
Code:
<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>
As you can see below, FlashCard_ID '1' and '2' are both assigned Tags '2' and '4'
Code:
mysql> select * from flashcard;
+--------------+------------+----------+
| FLASHCARD_ID | QUESTION | ANSWER |
+--------------+------------+----------+
| 1 | Question 1 | Answer 1 |
| 2 | Question 2 | Answer 2 |
| 3 | Question 3 | Answer 3 |
+--------------+------------+----------+
3 rows in set (0.00 sec)
mysql> select * from tag;
+--------+------+
| TAG_ID | NAME |
+--------+------+
| 1 | tag1 |
| 2 | tag2 |
| 3 | 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 |
| 2 | 2 |
| 4 | 2 |
| 5 | 2 |
+--------+--------------+
6 rows in set (0.00 sec)
Login
FAQ
Search