Entity:

package com.igorgorbunov3333.core.entities.domain; import javax.persistence.*; import java.util.Date; /** * Created by Игорь on 03.04.2016. */ @Entity @Table(name = "cases") @NamedQueries({ @NamedQuery(name = "findAllCases", query = "SELECT c FROM Case c JOIN c.client JOIN c.firstInstance " + "JOIN c.secondInstance JOIN c.thirdInstance JOIN c.category"), }) public class Case { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private long id; @Column(name = "case_number") private String caseNumber; @ManyToOne @JoinColumn(name = "client_id") private Client client; @OneToOne @JoinColumn(name = "first_instance_id") private FirstInstance firstInstance; @OneToOne @JoinColumn(name = "second_instance_id") private SecondInstance secondInstance; @OneToOne @JoinColumn(name = "third_instance_id") private ThirdInstance thirdInstance; @Temporal(TemporalType.DATE) @Column(name = "closing_date") private Date closingDate; @ManyToOne @JoinColumn(name = "category_id") private Category category; private float price; .....

As you can see there is a named findAllCases request. I get all the entities ( Case ) like this:

 public List<Case> findAll() { Query query = entityManager.createNamedQuery("findAllCases"); return query.getResultList(); }

But the method returns all entities in which the secondInstance , thirdInstance not null . How to make it so that entities with zero secondInstance and thirdInstance fields thirdInstance ?

  • one
    Remove JOIN c.secondInstance JOIN c.thirdInstance . Generally remove all join. On a fig they are necessary if you want to receive everything? After all, join restricts the selection to only those entities that have corresponding entities in the join (not zero). - Sergey

1 answer 1

In SQL there are different types of table joins ( JOIN ), in your case two of them are interesting.

  1. INNER JOIN : if there is no record in the joined table, the record from the main table does not fit into the resulting selection
  2. LEFT JOIN : an entry from the main table is included in the result set, regardless of whether there is an entry in the joined table

In JPQL the JOIN operator by default works as an INNER JOIN , therefore, in your case, the sample contains records for which no records were found in secondInstance and thirdInstance . In order for a sample of the Case entity to secondInstance records with empty secondInstance and thirdInstance must explicitly specify LEFT JOIN .

  • All is clear, thank you! - Igor Gorbunov