Java / Hibernate: Could not resolve property with nested object criterias

Question

I'm having problems with a Hibernate criteria. I'm trying to make a Criteria where I look at the id of a member object of the class the query returns.

For example:

Criteria crit = session.createCriteria(Enquiry.class);
crit.add(Expression.eq("lecture.admin.id", userId));`

The result of this is an exception:
org.hibernate.QueryException: could not resolve property: lecture.admin.id of: xxx.yyy.Enquiry

The Enquiry class does contain the lecture variable, which in turn contains the admin variable. I have tried using lecture.id and that works fine.

Is there a limit to the number of levels you can go down the object hierarchy like this?

Thanks!

Code snippets:

public class Lecture extends TransferItem {  
  private User admin;  
  public User getAdmin() {  
    return admin;  
  }
}

The 'User' class extends the Person class, which in turn extends an Itemclass, which has the getId()method:

public Integer getId() {  
  if (id != null) {  
    return id;  
  }  
  return TransferBean.NOT_SET;  
}

From the Hibernate mapping XML:

<class name="User" table="user">  
  <id column="user_id" name="id">
    <generator class="increment"/>  
  </id>
  ...

<class name="Lecture" table="lecture">  
  <many-to-one class="User" column="user_fk" lazy="false" name="admin"/>`  

This is the user table:

mysql> show columns from user;  
+-----------------+--------------+------+-----+---------+-------+  
| Field           | Type         | Null | Key | Default | Extra |  
+-----------------+--------------+------+-----+---------+-------+  
| user_id         | int(11)      | NO   | PRI |         |       |   
| firstname       | varchar(50)  | YES  |     | NULL    |       |   
| lastname        | varchar(50)  | YES  |     | NULL    |       |   
| signature       | varchar(16)  | YES  |     | NULL    |       |   
| email_signature | varchar(256) | YES  |     | NULL    |       |   
| password        | varchar(32)  | YES  |     | NULL    |       |   
| phone           | varchar(16)  | YES  |     | NULL    |       |   
| email           | varchar(255) | YES  | UNI | NULL    |       |   
| lecturer_fk     | int(11)      | YES  | MUL | NULL    |       |   
| access          | int(11)      | YES  |     | NULL    |       |   
| deleted         | tinyint(1)   | YES  |     | NULL    |       |   
+-----------------+--------------+------+-----+---------+-------+  
11 rows in set (0.02 sec)`  

Module

Answer 1

You can't use nested paths directly in Criteria API (unlike HQL). Instead, you need to create nested criteria instances or define aliases on each "entity.property" pair starting with the first non-root entity:

Criteria criteria = session.createCriteria(Enquiry.class)
 .createAlias("lecture", "l")
 .createAlias("l.admin", "a")
 .add( Restrictions.eqProperty("a.id", userId) );

Note that the very first property is not prefixed as it belongs to the root entity (Enquiry), the others are prefixed with previous level alias. Details are in the documentation.

Also note that id is a special property when it comes to associations; in your case user_fk is a column located in lecture table. It should, therefore, be possible (provided that the mappings you've posted are accurate) to rewrite the above criteria as:

Criteria criteria = session.createCriteria(Enquiry.class)
 .createAlias("lecture", "l")
 .add( Restrictions.eqProperty("l.admin.id", userId) );

thus eliminating extra join.

Baillieu

Always instructive as usual @Baillieu – Venlo

That feeling when you facepalm yourself.. good and clear answer, makes things pop into your mind! – Headwater

Is Lecture FK a column located in Enquiry table? According to deleted part ".createAlias("l.admin", "a")", can lecture alias be removed too? – Licensee

I'm not sure what version of Hibernate this answer expects, but I think with Hibernate 5 eq() should be used instead of eqProperty(). – Galloping

Answer 2

Criteria crit = session.createCriteria(Enquiry.class)
crit.createAlias("lecture.admin", "lectureAdmin");
crit.add(Expression.eq("lectureAdmin.id", userId));

You can indeed hit issues when you go down too deep in the object graph. I usually get around this by creating an alias as shown above.

Chee

I tried this, but I then hit an SQLGrammarException instead: Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'lectureadm1_.user_id' in 'where clause' Any ideas? – Module

This looks like an error coming from the database that there is not "user_id" column on the "user" table. Are you sure that you database table has this column? Perhaps the column on the database is also called "id", if that is the case you hibernate mappings are the issue. – Chee

I added the user database table to the question Thanks! – Module