newbie: simple hql query...

kenhans · **Joined:** Tue Dec 13, 2005 5:43 pm **Posts:** 5

hello,
I was wondering how to do a simple query with hql. I have the following two POJOs, with a one-to-many association:

Code:

public class Component  implements java.io.Serializable {
     private Set componentPowerLimits;
}

public class ComponentPowerLimit  implements java.io.Serializable {
     private Date terminated;
     private Component component;
}

in my database table, the COMPONENT_POWER_LIMITS table contains the id (cmp_irn_nr) of the COMPONENT as a foreign key. I would like to achieve an SQL looking like this:

Code:

select id from component_power_limits where cmp_irn_nr = 779511201 and terminated is not null

How can I achieve this with hql? I tried

Code:

session.createQuery("from ComponentPowerLimit cpl where cpl.cmpIrnNr = :cmpIrn1 and terminated is null");

which of course did not work as the cmpIrnNr is not a part of the ComponentPowerLimit class.

Hmmm.... seems to be something about Hibernate I do not quite grasp. Any help or pointers in the right direction would be highly appreciated!

cheers,
Kenny

nathanmoon · **Posted:** Wed Dec 14, 2005 3:36 pm

Always think of HQL in terms of the Objects, not the table/columns. You want to do:

Code:

from ComponentPowerLimit cpl where cpl.component = :component and terminated is null

Then you would bind the Component object to the HQL query, something like this:

Code:

Component comp = session.get(Component.class,new Long(779511201));
List cpls = session.createQuery(hql).setEntity("component",comp).list();

Of course, this is just sample code. I assume that you would already have the Component loaded previously, and wouldn't really load it by id just for this query. If that were the case, then you could do it in one shot with a query like:

Code:

from ComponentPowerLimit cpl where cpl.component.componentId = :componentid and terminated is null

where componentId is the name of the field in your Component object for the primary key (you didn't show one in your posted code, so I don't know what yours is really named).

Hope this helps.

kenhans · **Joined:** Tue Dec 13, 2005 5:43 pm **Posts:** 5

thank you for your very helpful answer!