I've a scenario which I'm finding difficult to solve using a hql named query. Please if anybody can help.
I have Entities with all the relationships been defined using annotations. I've a case where I wanted to write a report query which fetches me all the details in a single DB interaction. The case is:

The relationships between entities are:
Person {
String key;
PhoneNumber phoneNumber;
}

abstract PhoneNumber {
String key;
String number;
}

MobileNumber extends PhoneNumber {
String countryCode;
}

LandLineNumber extends PhoneNumber {
String countryCode;
String areaCode;
}

Each one of them has a table in DB with proper constraints. I wanted to fetch a data transfer object like this:

select new com.DTO(p,
(case when m.key is not null then m.countryCode else ln.countryCode end),
(case when ln.key is not null then ln.areaCode else null end),
pn.number)
from Person p, PhoneNumber pn left join MobileNumber m on pn.key=m.key
left join LandLineNumber on pn.key=ln.key
where p.key = ?
and pn.key=p.phoneNumber.key

It gives error while parsing orm and making entity manager. Then I removed the colored section to see if it automatically adds the on while compiling to actual query, that as well didn't run.
Note: In abv case Person would always have a phoneNumber but the type number could differ.

No offense, but your class / table structure seems overly complicated for something so simple. Personally, I'd abandon inheritance entirely (especially a table per subclass approach) and consolidate all of the properties into a single class with a type property if you need to differentiate between mobile and home phone numbers:



If the aim of your query is to select the phone number for a given person, then the HQL would simply be a matter of:

select phoneNumber from Person where key = :key

On a side note, you don't typically specify the join conditions in a query (with the exception of the join type). Assuming your classes are configured properly, Hibernate is aware of the underlying relationship that exists between the associations and will automatically generate the proper join SQL necessary to retrieve the data.

What I had given was just a simple example to my situation. Inheritance is defined in entities and each parent and its 7-8 child entities and all other linked entities have almost 50-60 attributes each and also the business scenario needs inheritance.

The problem I'm facing that I don't want to fetch whole of entity graph, I just want 1-2 fields from parent and 1 field from the child (i.e. attribute1 if child is child 1 or attribute 2 if child is child2).

It's a little strange... but this should work:



Assuming the problem that you're facing is that some of your subclasses share attributes of the same name...

surprise ya..

it gives NullPointerException while doing so.

Hmm... not sure why. I was able to run that myself against v3.5.6 without any issues. Posting the stack trace and HQL might help.

Thanks burroinquieto, now I'm able to successfully run my query, just using components helps. The problem I was facing may be because hibernate may not be supporting subselect within functions like 'coalesce', even in 'case when then end'.

Even I was facing problem trying to use mathematical operands like (+,-,..) with subselect on both side of operand. So now I'm doing the calculation on application side rather within query.

Thanks for all the help.

No problem. Glad you were able to get it working. As far as using a subselect within a coalesce, I didn't seem have any issues with the HQL that I posted. I tested in v3.5.6, so if you're using an earlier version then it's possible that may have something to do with the problems that you're seeing.