CODEWITHSUNDEEP
javahibernatenullhql
marcus m
marcus m

Reputation: 83

Hibernate, replace null value in hql

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.

Upvotes: 1

Views: 2308

Answers (2)

JB Nizet
JB Nizet

Reputation: 691635

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

Upvotes: 0

JimmyB
JimmyB

Reputation: 12610

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.

Upvotes: 2

Related Questions