JPA Query with null object

java, jpa, sql

Solution

Use an explicit `LEFT JOIN` for that:

SELECT cu from Customer cu  
LEFT JOIN cu.person p
LEFT JOIN cu.company co
where lower(p.name) like :name escape '!' 
or lower(co.name) like :name escape '!'

Problem

I have the following query: ``` Query query1 = getEntityManager().createQuery( " select o from Customer o " + " where lower(o.person.name) like :name escape '!' " + " or lower(o.company.name) like :name escape '!'"); ``` And my mapping is: ``` @Entity public class Customer extends AbstractBaseEntity { @OneToOne(optional = true, cascade = CascadeType.ALL) private Company company; @OneToOne(optional = true, cascade = CascadeType.ALL) private Person person; ... } ``` The problem is: Each `Customer` may be a `Person` OR a `Company`, but not both at same time. This means that one of these entities will be always null. With the query that I wrote, it will only fetch the result when both of the entities are not null, which is a behavior that I didn't expect. Does anyone know how to create a query that searches for `Person.name` when `Company.name` is null and search for `Company.name` when `Person.name` is null? I can make 2 separated queries, but I'm trying to do it all just in one query to perform the spring-data-jpa pagination.

Original source