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.

V G · Accepted Answer · 2013-11-23 20:01:19Z

3

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

edited Nov 23, 2013 at 20:01

answered Nov 23, 2013 at 19:14

V G

19k6 gold badges55 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Deividi Cavarzan Over a year ago

Sure! But if the person is null and i'm looking for a comapny name?

V G Over a year ago

Of course that was only an example to show you the idea :) Answer updated. Please don't tell me now that there is no SELECT part in my example :)

Deividi Cavarzan Over a year ago

Of course =) Thanks again. Sadly it doesn't works with the OR statement. I execute in separated queries and works normally. This is strange. I've double checked all parameters and syntax...

V G Over a year ago

Another idea is to use an explict LEFT JOIN. If you dont get it, I will post another answer later.

Deividi Cavarzan Over a year ago

Nice! It works using left join! Thanks! Just edit your answer with the left join to mark as correct =)

Collectives™ on Stack Overflow

JPA Query with null object

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related