COALESCE in JPA namedQuery

java jpa coalesce toplink nvl
Jåcob picture Jåcob · Dec 17, 2014 · Viewed 26.3k times · Source

I have the following namedQuery

select new test.entity.Emp(COALESCE(k.projectId,'N')
as projectId, k.projectName) from Emp o inner join o.projects k

However I am getting error

expecting RIGHT_ROUND_BRACKET, found '('

How to handle COALESCE in namedQuery?

Are there any other ways to handle null values in JPA?

Answer

gknicker picture gknicker · Dec 17, 2014

Coalesce is supported by JPA 2.0 API.

The new construct is proprietary to Hibernate, not necessarily supported in all JPA implementations. First try the query without also trying to construct an object:

select COALESCE(k.projectId,'N') as projectId, k.projectName from Emp o inner join o.projects k

Related questions

How to fix the Hibernate "object references an unsaved transient instance - save the transient instance before flushing" error

I receive following error when I save the object using Hibernate object references an unsaved transient instance - save the transient instance before flushing

Read More
What's the difference between JPA and Hibernate?

I understand that JPA 2 is a specification and Hibernate is a tool for ORM. Also, I understand that Hibernate has more features than JPA 2. But from a practical point of view, what really is the difference? I have experience using …

Read More
Difference between FetchType LAZY and EAGER in Java Persistence API?

I am a newbie to Java Persistence API and Hibernate. What is the difference between FetchType.LAZY and FetchType.EAGER in Java Persistence API?

Read More