CODEWITHSUNDEEP
javasqlhibernatejpajpql
John John
John John

Reputation: 4575

How to query a nullable @OneToOne relationship with JPA

I have Entity1 and Entity2. They have a OneToOne nullable relationship.

@Entity
class Entity1 {

   @Id
   @Column(name = "id")
   private Long id;

   @OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "entity2")
   @JoinColumn(nullable = true)    
   private Entity2 entity2;
   ...
}

How can I query all Entity1 objects that has a null entity2?

Because if I do:

SELECT e FROM Entity1 e WHERE e.entity2 IS NULL

JPA engine do a JOIN between the two tables and put a useless WHERE clausule (WHERE entity_id = NULL). Resuming, it executes an useless native SQL. How can

Current solution:

Reading the OpenJPA documentation, I found that Native Queries should be used to workaround JPA limitations. I can get it very easy using native query and I'm currently doing it, but I would like to avoid to use it.

Upvotes: 12

Views: 14799

Answers (2)

Suken Shah
Suken Shah

Reputation: 1682

Yes it can be done without performing join and using JPA query. Check out the following code snippet:

@Entity
class Entity1 {
   ...
   @OneToOne(...)
   @JoinColumn(name="entity2ID")
   private Entity2 entity2;
   @Column(name="entity2ID", nullable=true, insertable=true, updatable=true)
   private Long entity2ID;
   ...
}

Simply map the joining column in entity as insertable/updatable as FALSE. The Make sure that you absolutely not provide setter method for xyz or joining column. Once this is done you can use following query:

SELECT e FROM Entity1 e WHERE e.entity2ID IS NULL

This won't perform any joins as you are querying for joining column directly.

Upvotes: 0

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154090

You can simply run this JPQL query:

SELECT e1 
FROM Entity1 e1 
LEFT JOIN e1.entity2 e2
WHERE e2 IS NULL

The LEFT JOIN is what you were looking for.

Upvotes: 9

Related Questions