LEFT OUTER JOIN with Hibernate 5 Core

MrWalterWhite 0
Viewed 32

I have two entities (EntityA and EntityB) with an optional @OneToOne relation between them.

EntityA:

@Setter
@Getter
@NoArgsConstructor
@Entity(name = "EntityA")
public class EntityA {

  @Id
  private Long id;

  @OneToOne(targetEntity = EntityB.class, mappedBy = "entityA", fetch = FetchType.LAZY, cascade = CascadeType.ALL, optional = true)
  private EntityB entityB;

  // other members
}

EntityB:

@Setter
@Getter
@NoArgsConstructor
@Entity(name = "EntityB")
public class EntityB {

  @Id
  private Long id;

  @OneToOne(targetEntity = EntityA.class, fetch = FetchType.LAZY)
  @MapsId
  @JoinColumn(name = "id")
  private EntityA entityA;

  @Column(name = "status", nullable = false, unique = false)
  private Short status;

  // other members
}

I want to select all entities A that do not have any correspondence on entity B, or if having one, the entityB status should be 0.

From plain SQL, I thing the my goal is:

SELECT a.*
  FROM EntityA a
  LEFT JOIN EntityB b ON a.id = b.id
  WHERE b.id IS NULL OR b.status = 0

What's the best strategy to achieve this with Hibernate 5 Core and PostgreSQL?

My approach was:

getSession().createQuery("SELECT a FROM EntityA a LEFT JOIN a.entityB b " +
                            "WHERE b IS NULL OR b.status = :status", EntityA.class)
                    .setParameter("status", (short) 0);

Is this the correct way to do it?

How do I eager fetch EntityB entities in the same query?

Thanks.

hibernate java
1 Answers

You query looks correct. Also if you want to eagerly fetch EntityB just add 'FETCH' to query

createQuery(
    "SELECT a FROM EntityA a " +
    "LEFT JOIN FETCH a.entityB b " +
    "WHERE b IS NULL " +
    "OR b.status = :status", EntityA.class).setParameter("status", (short) 0)
johnnyutts0
Related