Hibernate: dynamic join multiple tables

Question

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

score 1 · Accepted Answer · answered Nov 05 '14 at 19:49

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.

score 1 · Answer 2 · answered Nov 10 '14 at 16:41

If I translate your query to Java language, what you are trying to do is getting value of coutry directly out of employee. In Java you would get a compile error though. Such thing automatically does not work neither in Java, nor in Hibernate or plain SQL. There is no direct link between employee and country, but there is a sequence of links employee -> address -> country.

To fix this in Java way, one could maitain a secondary reference to country directly from employee. Then calling employee.getAddress().getCountry() and employee.getCountry() would be equal.

To achieve this in hibernate, you would need to add new field country to Employee and map it as a non-modifiable many-to-many relationship with entity Country, using entity Address to map the relationship. This would only reduce 2 hibernate joins to one, it could not be applied recurrently to more than 2 joins.

If this solution is OK for you, I can provide you with example syntax.

Ok, I understand your comparison. The point is that Hibernate has a query (builder) engine, but it is not capable to dynamically find the path between two entities in the graph as I supposed. — Neo, Nov 11 '14 at 09:55
You are correct. Hibernate needs a join in the query to give the path from employee to country _through_ the address entity — OndroMih, Jan 07 '15 at 11:19

Hibernate: dynamic join multiple tables

2 Answers2