Hi,

I'm banging my head against the wall trying to convert this SQL to HQL;

select faculty.* from faculty, presenters
where presenters.event_id = SOME_ID
and presenters.presenter_id = faculty.id

I know it's simple but I'm not getting it right. I just need to the Faculty object back based on the match from the event_id. Presenters is the mapping table between Faculty and events.

Thoughts? Hazings? Help? All three?

TIA,

Jon

Please provide your mappings!

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Aug 22, 2007 6:49:42 AM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
<class name="com.bem.regsite.model.Presenter" table="PRESENTERS" catalog="ebi">
<comment></comment>
<id name="id" type="int">
<column name="ID" />
<generator class="identity" />
</id>
<property name="eventId" type="int">
<column name="EVENT_ID" not-null="true">
<comment></comment>
</column>
</property>
<property name="presenterId" type="int">
<column name="PRESENTER_ID" not-null="true">
<comment></comment>
</column>
</property>
<property name="dateAdded" type="date">
<column name="DATE_ADDED" length="10" not-null="true">
<comment></comment>
</column>
</property>
<property name="dateModified" type="date">
<column name="DATE_MODIFIED" length="10" not-null="true">
<comment></comment>
</column>
</property>
<property name="modifiedBy" type="int">
<column name="MODIFIED_BY" not-null="true">
<comment></comment>
</column>
</property>
</class>
</hibernate-mapping>


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jan 14, 2007 6:54:10 PM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
<class name="com.bem.regsite.model.Faculty" table="FACULTY" catalog="regsite">
<comment></comment>
<id name="id" type="int">
<column name="ID" />
<generator class="assigned" />
</id>
<property name="firstName" type="string">
<column name="FIRST_NAME" length="50" not-null="true">
<comment></comment>
</column>
</property>
<property name="middleName" type="string">
<column name="MIDDLE_NAME" length="50">
<comment></comment>
</column>
</property>
<property name="lastName" type="string">
<column name="LAST_NAME" length="50" not-null="true">
<comment></comment>
</column>
</property>
<property name="suffix" type="string">
<column name="SUFFIX" length="10">
<comment></comment>
</column>
</property>
<property name="specialty" type="string">
<column name="SPECIALTY" length="50">
<comment></comment>
</column>
</property>
<property name="dateAdded" type="date">
<column name="DATE_ADDED" length="10" not-null="true">
<comment></comment>
</column>
</property>
<property name="dateModified" type="date">
<column name="DATE_MODIFIED" length="10" not-null="true">
<comment></comment>
</column>
</property>
<property name="modifiedBy" type="int">
<column name="MODIFIED_BY" not-null="true">
<comment></comment>
</column>
</property>
</class>

<query name="com.bem.regsite.model.getAllFaculty">
<![CDATA[
from com.bem.regsite.model.Faculty as faculty
order by faculty.id asc
]]>
</query>
<query name="com.bem.regsite.model.getOneFacultyMember">
<![CDATA[
from com.bem.regsite.model.Faculty as faculty
where faculty.id = :id
]]>
</query>
<query name="com.bem.regsite.model.deleteFacultyMember">
<![CDATA[
from com.bem.regsite.model.Faculty as faculty
where faculty.id = :id
]]>
</query>
</hibernate-mapping>

If I were you, I would change my entity classes. I think that would help a lot (I normally prefer annotations so you must forgive me if I write something wrong ....).

My sollution is:
Declare the mapping from the Presenter class to both the Event class and the Faculty class as what it is: A One to One or One to Many mapping. Don't save and manage the id's yourselves.

In Annotations:
Instead writting (Presenter class)
public void setEventId(int id)

do write (this change wouldn't change the db schema!)
@OneToOne
public void setEvent(Event event)

A benefit of the declaration above is, that you can declare the relation as bidirectional (this too doesn't change the db schema). So you can easily navigate in both directions without writing complex queries.

It would look like this (class Event):
@OneToOne(mappedBy ... ) <- I don't know the exact syntay without my book :-(, because I'm a beginner
public void setPresenter(Presenter presenter) ...

=> Hibernate will use the colum Presenter.ENVENT_ID (you already HAVE this column) to manage the mapping between Presenters and Events

If you do this changes, it will be easy to navigate between your classes. The db-schema won't change and hibernate will do all the (searching) work for you. Your hql statement will be very easy:
Select f
from Faculty f
where f.presenters.event.id = :event

Hopefully I was able to help you
Hoeft

Hi

Uou can use criteria query

List cats = session.createCriteria(Faculty.class,"f")
.createCriteria("f.presenter","p")
.add( Restrictions.eq("p.event_id", SOME_ID ) )
.setResultTransformer(new AliasToBeanResultTransformer(Faculty.class)
.list();


You shold add in FACULTY mapping file
<set
name="presenter"
lazy="false"
inverse="true"
cascade="all"
sort="unsorted"
>

<key
column="PRESENTER_ID"
>
</key>

<one-to-many
class="com.bem.regsite.model.Presenter"
/>

</set>


Amila

(Don't forget to rate if helps)

....for the help and insight here. hoeft, I'm not familiar with annotations (it's on the list of things to do) but what you wrote makes sense. amila733, what you wrote also makes sense and I learned a bit about crieteria query, something I've not used before.

Thanks!