Query involving Collections - Help

_nadia_ · **Joined:** Wed Jul 13, 2005 9:04 am **Posts:** 3

Hibernate version: 3.0

Mapping documents:

Code:

<hibernate-mapping>
    <class name="dbpath.Book">
        <id name="id" column="book_id">
            <generator class="increment"/>
        </id>
        <set name="themes" table="ThemeBook" >
            <key column="book_id"/>
            <many-to-many column="theme_id" class="dbpath.Theme" />
        </set>
        <property name="description"/>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class name="dbpath.Theme">
        <id name="id" column="theme_id">
            <generator class="increment"/>
        </id>
        <set name="books" inverse="true" table="ThemeBook">
            <key column="theme_id"/>
            <many-to-many column="book_id" class="dbpath.Book"/>
        </set>
        <property name="key"/>
    </class>
</hibernate-mapping>

Name and version of the database: Oracle 9

Problem:

I have two objects, Book and Theme.

The Book object contains a set of Themes, the Theme object contains a set of Books.

What is the best/most efficient way to write a query to return all the Books which contain a particular Theme?

I have tried the following, however, it obviously does not work as it is not doing a join of any sort:

Code:

tx = hSession.beginTransaction();
    Query query = hSession.createQuery("select book from dbpath.Book as book where book.theme = :theme");
    query.setEntity("theme", theme);

    Iterator it = query.iterate();
    while (it.hasNext()) {
        Book book = (Book) it.next();
        bookList.add(book);
    }
    tx.commit();

shl · **Posted:** Fri Jul 22, 2005 8:22 am

I am not sure if it is the best/most effecient way, but it should work pertty fine:

Code:

select b from dbpath.Book b
  inner join b.themes t
  where t.id=?

And think about lazy="true" for

Code:

and

Code:

in the dbpath.Theme mapping