Zardo Zardo -4 years ago 137
Java Question

Hibernate: dynamic join multiple tables

I'm new to Hibernate and I'm trying to dynamically join more table. I have built an example with three tables: Employee, Address, Country. I want to retrieve all employees of a certain country. Here my config file:

<class entity-name="Employee">
<id name="id" type="int" column="id">
<generator class="native"/>
</id>
<property name="firstName" column="first_name" type="string"/>
<property name="lastName" column="last_name" type="string"/>
<property name="salary" column="salary" type="int"/>

<many-to-one name="address" column="address" unique="true"
class="Address" not-null="true"/>
</class>

<class entity-name="Address">
<id name="id" type="int" column="id">
<generator class="native"/>
</id>
<property name="street" column="street_name" type="string"/>
<property name="city" column="city_name" type="string"/>
<property name="state" column="state_name" type="string"/>
<property name="zipcode" column="zipcode" type="string"/>

<many-to-one name="country" column="country" unique="true"
class="Country" not-null="true"/>
</class>

<class entity-name="Country">
<id name="id" type="int" column="id">
<generator class="native"/>
</id>
<property name="name" column="name" type="string"/>
<property name="code" column="code" type="string"/>
</class>


If I do

List employees = session.createCriteria("Employee")
.createCriteria("address")
.createCriteria("country")
.add(Restrictions.eq("code","IT"))
.list();


I get the right result back but my goal is not to manually specify the whole path between start table and filter table: I want that hibernate makes the job for me. I would like to write something like this:

List employees = session.createCriteria("Employee")
.createCriteria("country")
.add(Restrictions.eq("code","IT"))
.list();


but I get the error message

org.hibernate.QueryException: could not resolve property: country of: Employee

Answer Source

Hibernate is just a wrapper or abstraction layer to hide away SQL expressions.

In your working example Hibernate builds a SQL select statement creating a double JOIN and returning the desired output. You are feeding Hibernate with the "knowledge" of how to filter the associated table. The same as you would write your own SQL statement.

But in you second example you expect Hibernate to guess a second level association from the first table. Hibernate simply looks up if table Employee has a column/foreign key country and it fails to find it. So the error is: could not resolve property: country of: Employee

You can not expect from Hibernate to do this kind of magic as it would produce or sorts of false queries.

Just to illustrate on your example.

In case your Employee would have two fields of type Address:

<class entity-name="Employee">
        <id name="id" type="int" column="id">
            <generator class="native"/>
        </id>
        <property name="firstName" column="first_name" type="string"/>
        <property name="lastName" column="last_name" type="string"/>
        <property name="salary" column="salary" type="int"/>

        <many-to-one name="bussiness_address" column="bussiness_address" unique="true"
                     class="Address" not-null="true"/>
        <many-to-one name="home_address" column="home_address" unique="true"
                     class="Address" not-null="true"/>
    </class>

Both would have a Country linked ... what should in case of this query hibernate do?

List employees = session.createCriteria("Employee")
          .createCriteria("country")
          .add(Restrictions.eq("code","IT"))
          .list();

Filter country by the home or the business address or both? Or just imagine a case where you do filtering by an id field present in every table.

So to wrap things up: Hibernate can't do magic it is only here to hide away raw SQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download