My HQL select query is like below
select user.id, user.address.id from User user
In this query, if user.address is NULL then query does not return that User record.
My requirement is, if user.address is NULL then user.address.id should return all User record with '' (empty value) for user.address.id. and records should be like below
[1,''], [2,1], [3,'']...
Faced similar problem today.
The problem here is that hibernate will produce inner join address SQL.
select user0_.id as col_0_0_, address1_.id as col_1_0_ from user user0_
inner join address address1_ on user0_.address_id=address1_.id
As the result of inner join you will lose all the records where address is null. Use explicit left join to achieve what you need. HQL:
select user.id, addr.id from User user left join user.address addr
Found the exact answer for such stuff.
select user.id, case when user.address is null then '' else user.address.id end
from User user
this case clause in select clause works like if else
if (user.address == null)
return "";
else
return user.address.id;
in this way you will get all records.
You can use 'coalesce' command (like the "nvl" SQL command).
As can be read here:
The equivalent to the nvl command in HQL is the coalesce command. coalesce(a,b) will return a if a is not null, otherwise b.
So you would want something on the lines of:
from Table where col1 = coalesce(:par1, 'asdf')
Edit: In your case I assume you are looking for something like:
select user.id, coalesce(user.address.id, '') from User user
If that's the only case, you can do something like this:
select user.id, user.address.id from User user
This will give you all records when user.address is not null.
You can now have this, to have the rest of the records:
select user.id, '' from User user when user.addresss is null
