JPA Query with null object

Question

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.

Answer 1

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 '!'