Hibernate's HQL "inner join on" limitations

We use hibernate on our projects for the ORM technology. We are starting to use EJB3 now for all new development. Luckily its very similar to hibernate so we don't have to re-skill too much.

Hibernate HQL Inner Join

An issue that I came across recently was hibernate's HQL language inability to handle "inner join on" clauses. If your domain entity model has relationships defined between the related objects then something like

Query query = session.createQuery("from Cat cat inner join Owner owner where owner.Name ='Duke'");

will work as intended. HQL knows how to join the Cat and Owner classes based on the association mapping in the hbm.xml file. Since the association is defined in the mapping file you don't really need to stipulate the join in the query. E.G "from Cat cat where cat.Owner.Name='Duke'" will work too. Explicit joins in this usage are mainly used to initialise collections and many-to-one mappings. i.e to avoid lazy load errors.

No defined association in hbm.xml file

But if you don't have relationships defined between objects then you need to tell hibernate what to join on. This happens in the following for example. Lets say that we don't want to enforce each cat to have an owner. Maybe they gone feral or the cat just feels it's its own boss. So there is no association in the hbm.xml file. One would expect the following to work:

Query query = session.createQuery("from Cat cat inner join Owner owner on cat.OwnerId = owner.Id where owner.Name='Duke'");

But the query will fail with the following error message:

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column xx

HQL on clause bug

It seems that hibernate does not support arbitrary relationships between object being defined in the "on" clause. I did find a bug report about this. Unfortunately I can't find the url again but apparently it is a long standing issue that is to be fixed. One of the solutions offered in the bug report was to use "with" instead of "on" eg "from Cat inner join Owner with Owner.Id = Cat.OwnerId" but this didn't work either.

Native SQL is the solution

In the end the way around this problem is to use native SQL to do the query. The code below shows how:

import org.hibernate.SQLQuery;

...

SQLQuery query = session.createSQLQuery("SELECT cat.* from cat inner join owner on cat.owner_id = owner.id where owner.name=:username");

query.addEntity(Cat.class);

query.setInteger("username",'Duke');

List =query.list();

This is an native SQL query so you can run it in MySQL query browser for example. You don't use the objects that wrap the tables in the query so any column names are actual table column names and not the names used for the columns in the object. eg owner_id instead of ownerId. You must define the class to map result to with addEntity. You can define multiple classes as well.

This works like a charm. It is better for maintainability to define this as a named query in the hbm.xml file

Alternative Solution?

An alternative is to use HQL with a cartesian product or cross join and do something like

Query query = session.createQuery("from Cat cat,Owner owner where cat.OwnerId = owner.Id and owner.Name='Duke'");

{C}

Although this will compile fine I did not pursue this solution so can't really comment on its effectiveness. I abandoned it because it just felt wrong. But it should work.

We offer JEE Training and JBoss Training

 

 

Comments

Thanks Mark,

This was helpfull information for me.

>Although this will compile fine I did not pursue this solution so can't really comment on its effectiveness. I >abandoned it because it just felt wrong. But it should work.
I've tested it and it works. Why do does it feel wrong to you? As far as I know most internal query planners handle table additions with where statements in exactly the same way as joins with on statements.

Greet,
Marco Wayop

I tried the alternative solution as well. It works fine as long as you are not trying to do a left join. If you attempted to use the above alternative solution, your resulting list will only contain cats that have owners instead of all cats with owners being either populated or null when they do not exist.

Worked for me as well - better if you do:

select cat from Cat cat,Owner owner where cat.OwnerId = owner.Id and owner.Name='Duke'

The generated sql is pretty much the same as doing the query in ansi sql. The benefit is you can use the query without having to associate a class.

Does it mean that I don't even need to map the objects within the xml file? I think I should, because otherwise how could hibernate know what are we talking about when we say Owner or Cat. I agree that the association should not be added to the xml, but both objects should exist there as individual class mappings. Am I right?

I used with in place of on. But still getting null pointer exception while it is working fine in simple sql
part of code generating problem help me out....

PaymentInstructionExtract left outer join Currency with PaymentInstructionExtract.interBnkStlmtAmountCurr = Currency.currency_Code

I came upon this thread while having a similar problem that u mentioned in the alternative thread. Can u help me out here? Here's the link to the details on stackoverflow : http://stackoverflow.com/questions/11929634/hibernate-native-query-with-multiple-joins-on-same-table-returning-wrong-results

Issues surrounding gunblood tricks can't possibly be
over analysed. There are several elements which swayed the actual development involving gunblood cheats.
Given that it is effect pervades the society,
gunblood secrets and cheats can be showcasing increasingly more
in the ideals in the fresh along with upwardly cell phone.
It truly is a good unfortunate consequence individuals civilizations background that
gunblood cheats is usually almost never offered reasonable
concern simply by global industrial enterprises, who
type a final great hope for the civilzation. Complex although it is When i shall currently try to provide an exaustive survey upon gunblood cheats and its
a lot of 'industries'. It's lately come to the attention that too few individuals appreciate how great gunblood secrets and
cheats continues to be to existence. Daily most of us arise and probably include a number gunblood tricks resting for the foot your furniture.
It really is superb to wake in addition to laugh each day for this reason.

Stop by my web-site: gunblood hack

I would probably have taken hours to figure that out without this post, thanks!

Hi,

I tried your native sql example and as you mentioned there aren't any association in the hbm.xml files. When I use addEntity for one table, it works fine but I want to get data also from other table. How can I get datas from all the tables in the join query?

Can you give me an example?

Thanks in advance.

Duygu Colak
Turkey

Isn't the correct HQL to use 'with', where you would use 'on' in SQL?

Query query = session.createQuery("from Cat cat inner join Owner owner with cat.OwnerId = owner.Id where owner.Name='Duke'");

Here's a query to return a list of forums, along with message counts and last post date. The part where the with clause comes in is where I want to filter out messages that have been "blocked" (i.e., message.visible = false).

select
forum,
count(message),
max(message.dateCreated)
from
Forum as forum
left outer join
forum.messages as message
with
message.visible = true
group by
forum

Is not a good solution, but if you see at the documentation http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-joins.

You will find that you don't have to do a reference to the table.
in the query above you should do the following:

Query query = session.createQuery("from Cat cat inner join cat.owner owner where owner.Name ='Duke'");

Hibernate understands that you want to do an inner join between cat and owner by Cat's owner property.

After coming across this post we realised that we were not insane, it's just Hibernate. Thanks!