[Solved] Query and many-to-many-relation

colbertz · **Joined:** Sat Oct 18, 2008 10:25 am **Posts:** 30

Hello,

in my application, there are the classes Person and Category with a many-to-many-relation between them. Now I want to select all persons with categoryId = 1.

I have been trying to create this query for hours, but it does not work. Can someone explain me how to write this query?

Here are the relevant parts of my mapping files.

Person.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="false" auto-import="true">
   <class name="de.waldhausweg7.model.Person" table="Person">
      <id name="personId" type="int">
         <generator class="increment" />
      </id>
      
      <!-- n:m-Beziehung zu Kategorie -->
      <set name="categories" table="person_category" lazy="false">
         <key column="personId" />
         <many-to-many class="de.waldhausweg7.model.Category" column="categoryId" />
      </set>      
   </class>
</hibernate-mapping>

Category.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="false" auto-import="true">
   <class name="de.waldhausweg7.model.Category" table="Category">
      <id name="categoryId" type="int">
         <generator class="increment" />
      </id>
      <property name="categoryName" column="categoryName" />
      <property name="comment" column="comment" />
   </class>   
</hibernate-mapping>

Here some of my attempts:

Code:

String queryString = "select * from person p, person_category pc where p.personId = pc.personId and pc.categoryId = 1";

String queryString = "select * from Person p where p.categories in (select * from Category c where c.categoryId = 1)";

String queryString = "select p from Person as p where :categoryId in elements (p.categories)";

String queryString = "select p from Person as p where :categoryId in elements (p.categories.categoryId)";

String queryString = "select p from Person p join p.categories c where c.categoryId = :categoryId";

Everytime I get null as result although there is a record which fits to the condition.

I would be thankful for every hint.

Christopher

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

One of the sides of the relationship has to be marked as "inverse", so that Hibernate doesn't get confused when generating the SQL. I don't know if this will solve your problem, but it is an error to correct anyways.

If it still doesn't work (I don't see anything blatantly wrong in your HQL), I would suggest logging the generated SQL (I explain how here
http://hibernar.org/articulos_en/logging.php), and observe if it makes sense to you, maybe copying it and executing it against the database independently.

ataveras · **Posted:** Sat Jan 03, 2009 4:51 pm

Hi, inverse won't work because it's not a bidirectional mapping.

The either query could work for you, because the mappings seens to be ok. You should check the data and set hibernate.show.sql=true could help a little.

Code:

select p from Person p join p.categories c where c.categoryId = :categoryId

or

Code:

select p from Person p fetch join p.categories c where c.categoryId = :categoryId

regards,

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Ooops, right!
It is not bidirectional.

colbertz · **Joined:** Sat Oct 18, 2008 10:25 am **Posts:** 30

Thank you very much. Now it works.