JPA: Using Inner Joing when Combining Two Tables

Question

I have a Patients table mapped by the following class:

public class Patient {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int uid;
  @Column(columnDefinition = "VARCHAR(20)")
  private String id;
  private boolean isId;
  private String name;
  @ManyToOne
  @JoinColumn(name="hmo")
  private Hmo hmo;
  @ManyToOne
  @JoinColumn(name="doctor")
  private Doctor doctor;
  private Instant openDate;
  private String comments;
  ...
}

And a HMO table mapped by the following class:

public class Hmo {

  public static final String UID = "uid";
  public static final String NAME = "name";

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int uid;
  private String name;
  ...
}

(Doctor class is similar to HMO) I would like to extract only a subset of the patient: Its id, name and the HMO's name, so I have created a metadata object:

public class PatientMetadata {

  public static final String UID = "uid";
  public static final String NAME = "name";

  private int uid;
  private String id;
  private boolean isId;
  private String name;
  private String hmo;
  ...
}

Trying to fill this object using join and fetch failed, so I have written the following code without any join, and looks like Hibernate is implicitly creating the join:

try (SessionFactory sessionFactory = HibernateUtils.getSessionFactory();
         Session session = sessionFactory.openSession()) {
      CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
      CriteriaQuery<PatientMetadata> criteria = criteriaBuilder.createQuery(PatientMetadata.class);
      Root<Patient> patientRoot = criteria.from(Patient.class);
      Root<Hmo> hmoRoot = criteria.from(Hmo.class);
      criteria.select(criteriaBuilder.construct(PatientMetadata.class,
                                                patientRoot.get("uid"),
                                                patientRoot.get("id"),
                                                patientRoot.get("isId"),
                                                patientRoot.get("name"),
                                                hmoRoot.get("name")))
              .where(criteriaBuilder.equal(patientRoot.get("hmo"), hmoRoot.get("uid")));
      System.out.println(session.createQuery(criteria).getResultList());
}

It looks like the code is working well: I am getting the correct answers, and only a single SQL query is sent to the server.
The problem is that the generated SQL query contains cross join and not inner join: Hibernate: select patient0_.uid as col_0_0_, patient0_.id as col_1_0_, patient0_.isId as col_2_0_, patient0_.name as col_3_0_, hmo1_.name as col_4_0_ from patients patient0_ cross join hmos hmo1_ where patient0_.hmo=hmo1_.uid

However, according to documentations it is discouraged to use such queries (CROSS JOIN with WHERE), so my question is how can I correctly fill this metadata object correctly?

Should it be an Entity class?

Should I somehow map it with fetch/join?

It is most probably because you are using 2 distinct roots. Try to use one root eg. `Root` and walk to `HMO` from there. Your results requires data from `Patient` and `HMO` so why no inner joining them ? — Antoniossss, Sep 04 '16 at 18:30

score 0 · Answer 1 · edited May 23 '17 at 10:29

0

It is most probably because you are using 2 distinct roots. Try to use one root eg. Root and walk to HMO from there. Your results requires data from Patient and HMO so why no inner joining them ?

Here you have example on how you can "walk" to related object's properties using crietria API joins: https://stackoverflow.com/a/13856999/1527544

or even better here: https://stackoverflow.com/a/3427661/1527544

edited May 23 '17 at 10:29

Community

1
1

answered Sep 04 '16 at 18:33

Antoniossss

31,590
6
57
99

I I am not mistaken, this is only possible if the PatientMetadata class contains the HMO as an `Hmo` object and not as a String? – Guy Yafe Sep 04 '16 at 19:25
@GuyYafe `PatentMetadata` has nothing to do with it. It is just a POJO. Build criteria query just a s you did, just use one join (Patient to HMO or the other way around) insteed of 2 roots. – Antoniossss Sep 04 '16 at 21:30

JPA: Using Inner Joing when Combining Two Tables

1 Answers1