Why does using a column name directly in HQL only work sometimes?

Question

I have two HQL queries I am using for a quick-and-dirty unit test. The first looks somewhat like this:

from Foo where SOME_FOREIGN_KEY = 42

The second looks like this:

from Foo as foo
 inner join foo.Bar as bar
 where foo.SOME_FOREIGN_KEY = 42

The SOME_FOREIGN_KEY column is not the name of something that Hibernate knows is mapped.

For some reason, the first HQL query works, but the second one does not.

My goal here is to get the second version to work, without traversing the object graph to the object identified by the foreign key. For this test, I have a known ID and I only want the objects related to that ID. The object itself on the other end of the relationship is irrelevant. Is this possible?

Answer 1

So Foo in first example is without alias and in second it is. This means that in second example Hibernate is looking for property of the 'foo'. This should be the answer.

Maybe this will work:

      select  f
      from    Foo f
      inner join f.Bar bar
      where   f.SomeForeignKeyId = 42

SomeForeignKeyId is property mapped to SOME_FOREIGN_KEY, either way you will have to do this through Id field of referencing entity.

Also fetching Foo as in first example, should work just fine, depending on your mapping. So if in your mapping you have Eager fetching, that should work as far as I know.

Answer 2

For some reason, the first HQL query works, but the second one does not.

When you use something that isn't known by Hibernate in the WHERE clause of an HQL query (e.g. a function that is not registered in the SQL dialect), Hibernate acts smartly and passes it directly to the database.

In other words, assuming Foo is mapped on TABLE_FOO, the following HQL

from Foo where SOME_FOREIGN_KEY = 42

is translated into the following SQL

SELECT FROM TABLE_FOO WHERE SOME_FOREIGN_KEY = 42

And works if TABLE_FOO actually has a SOME_FOREIGN_KEY column.

However, when using an alias like in the second example:

from Foo as foo where foo.SOME_FOREIGN_KEY = 42

Hibernate tries to resolve SOME_FOREIGN_KEY as a property of the Foo entity, and this obviously fails.

My goal here is to get the second version to work, without traversing the object graph to the object identified by the foreign key.

It won't if you prefix the column with the alias. So the following should work:

from Foo as foo
 inner join foo.Bar as bar
 where SOME_FOREIGN_KEY = 42

But honestly, I don't understand why you don't want to use a path expression and I would advice against using the above solution. One of the the point of HQL is to abstract the table and column names and you would be totally defeating this goal here.