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:
SQLQuery query = session.createSQLQuery("SELECT cat.* from cat inner join owner on cat.owner_id = owner.id where owner.name=:username");
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
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'");
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.