Querying for one object using a owned object's properties

CJGiant · **Joined:** Tue Mar 09, 2004 2:44 pm **Posts:** 15

I have a user object with properties stored in the userex table (first, last, user names, id). I also have a join table to the jobs table (a many-to-many user to jobs). The jobs table contains employer and city fields along with an id.

This is a basic many-to-many realtional database setup. I have mapped user to jobs successfully and queried and saved users.

Code:

    <class name="HibernateExUser" table="userex">

      <id name="userId" type="long" column="userid" unsaved-value="-1">
         <generator class="identity" />
      </id>

        <property name="username" />

        <property name="firstName" column="fname">      </property>

        <property name="lastName" column="lname">      </property>
      <set name="jobs" table="userjobs" lazy="true">
          <key column="usernum"/>
          <many-to-many column="jobid" class="HibernateExJob"/>
      </set>

    </class>

The job class knows nothing about a user, just its id, employer, and city fields:

Code:

   <class name="HibernateExJob" table="jobs">

        <id name="jobid" type="long" column="jobid" unsaved-value="-1">
         <generator class="identity" />
      </id>

      <property name="employer" column="employer" />
      <property name="city" />

    </class>  

If I want to write a query that will return me all the users who work for employer "XYZ", how would I do that with HQL? I have tried to do a join, but I am not sure what to join on. I either get too many results or an error. The basic SQL would be:

SELECT userex.all_fields
FROM userex JOIN userjobs JOIN jobs -- with appropriate ON clauses
WHERE jobs.employer = 'XYZ';

Is there a possible query using hibernate? Or to accomplish this, must a job object know the user that it comes from?

CJGiant · **Joined:** Tue Mar 09, 2004 2:44 pm **Posts:** 15

Well, it's a new day, and I figured I'd start anew, and the following seems to work. I am sure I tried this yesterday and it didn't, but I must have done something wrong.

Code:

            Query query = session.createQuery("SELECT user FROM HibernateExUser as user JOIN user.jobs as job" +
                                              " where user.username LIKE '%' + :uname + '%'" +
                                              " AND user.firstName LIKE :fname +'%'" +
                                              " AND job.employer LIKE :emp +'%'");