How can I replace (or get result with null value) null value in HQL? In my case the SomeObject can be null. I have relation Many-To-One between Clazz and SomeObject:
SELECT c.name, c.someObject.name from Clazz c
I've tried:
SELECT c.name, coalesce(c.someObject.name, ' ') from Clazz c
but it doesn't return any row.
You may want to note that there is no such thing as a 'many-to-null' relation. If the referenced object (SomeObject) is null when persisting a Clazz entity no entry will be made into the SomeObject database table.
Then, when you try to access the (non-existent) SomeObject in your query (c.someObject.name) Hibernate will implicitly build an inner join, which of course will exclude the Clazz object in question because the join cannot be fulfilled.
Try to explicitly use an outer join; this will solve your problem.
By the way: You can let hibernate output the SQL statements it generates, which allows to better understand why a given query behaves in a certain way.
c.someObject.name is an implicit inner join between Clazz and SomeObject. So, since it's an inner join, it automatically filters out Clazz instances having a null someObject.
You need an outer join:
select c.name, s.name from Clazz c
left join c.someObject s
Clazz, 'one'-side isSomeObjectand it's value can be null