Converting SQL to HQL

thomers · **Joined:** Wed Oct 29, 2008 8:54 am **Posts:** 13

i need to translate an SQL query into a hibernate query, but i'm not sure how to deal with the associated set.

a character can have other characters as buddies (stored in the "buddylist" table).

i want to retrieve all highscores from buddies of a character. i can do this via SQL, but how do i do it using HQL?

the query:
select hs.* from highscores hs, buddylist bl
where bl.character_id = 32
and
hs.game_id = 2
and
(bl.buddy_id = hs.character_id
)
order by hs.coins desc

Code:

<hibernate-mapping>
    <class name="Character" table="characters">
        <id name="id" type="java.lang.Integer">
            <column name="id" />
            <generator class="native" />
        </id>
        <set name="buddies" table="buddylist">
            <key column="character_id" />
            <many-to-many column="buddy_id" class="Character" />
        </set>
    </class>
</hibernate-mapping>
<hibernate-mapping>
    <class name="Highscore" table="highscore">
        <id name="id" type="java.lang.Integer">
            <column name="id" />
            <generator class="identity" />
        </id>
        <many-to-one name="character" column="character_id"
            class="Character" />
        <many-to-one name="game" column="game_id"
            class="Game" />
        <property name="coins" type="int">
            <column name="coins" />
        </property>
    </class>
</hibernate-mapping>

Hibernate version:
3.3.1

Name and version of the database you are using:
mySQL 5.0.45

littypreethkr · **Posted:** Wed Feb 11, 2009 8:51 am

I think this will do it:

Code:

SELECT hs FROM Highscore hs WHERE hs.character IN (SELECT b FROM Character c join c.buddies b where c.id = 1) AND hs.game.id=2 order by hs.coins desc

thomers · **Joined:** Wed Oct 29, 2008 8:54 am **Posts:** 13

thanks!