JPA Criteria Query - How to implement Join on two tables to get desired result in single Query

Question

I have 2 classes mapped with db Tables.

Composite Primary Key Class :

@Embeddable
public class Pk implements Serializable, Cloneable {

  @Column(name = "dataId")
  private String dataId;

  @Column(name = "occurrenceTime")
  private Timestamp occurrenceTime;

  public String getDataId() {
    return dataId;
  }

  public Pk setDataId(String dataId) {
    this.dataId = dataId;
    return this;
  }

  public Timestamp getOccurrenceTime() {
    return occurrenceTime;
  }

  public Pk setOccurrenceTime(Timestamp occurrenceTime) {
    this.occurrenceTime = occurrenceTime;
    return this;
  }

  @Override
  public boolean equals(Object o) {
    if (this == o) {
      return true;
    }
    if (o == null || getClass() != o.getClass()) {
      return false;
    }
    Pk pk = (Pk) o;
    return Objects.equals(getDataId(), pk.getDataId()) &&
        Objects.equals(getOccurrenceTime(), pk.getOccurrenceTime());
  }

  @Override
  public int hashCode() {

    return Objects.hash(getDataId(), getOccurrenceTime());
  }
}

1 : LoadProfile

@Entity
@Table(name = "energy")
public class LoadProfile implements Serializable, Cloneable {

  public LoadProfile() {
  }

  @EmbeddedId
  private Pk pk;

  @Column(name = "RECEIVE_TIME")
  private Timestamp reportingTime;

  @Column(name = "DATA1")
  private Double DATA1;


  @OneToOne
  @JoinColumns({
      @JoinColumn(name = "dataId", insertable = false, updatable = false, referencedColumnName = "dataId"),
      @JoinColumn(name = "occurrenceTime", insertable = false, updatable = false, referencedColumnName = "occurrenceTime")
  })
  private ForwardPower forwardPower;

  public Pk getPk() {
    return pk;
  }

  public LoadProfile setPk(Pk pk) {
    this.pk = pk;
    return this;
  }

  public Timestamp getReportingTime() {
    return reportingTime;
  }

  public LoadProfile setReportingTime(Timestamp reportingTime) {
    this.reportingTime = reportingTime;
    return this;
  }

  public Double getDATA1() {
    return DATA1;
  }

  public LoadProfile setDATA1(Double DATA1) {
    this.DATA1 = DATA1;
    return this;
  }

  public ForwardPower getForwardPower() {
    return forwardPower;
  }

  public LoadProfile setForwardPower(
      ForwardPower forwardPower) {
    this.forwardPower = forwardPower;
    return this;
  }

  @Override
  public boolean equals(Object o) {
    if (this == o) {
      return true;
    }
    if (o == null || getClass() != o.getClass()) {
      return false;
    }
    LoadProfile that = (LoadProfile) o;
    return Objects.equals(getPk(), that.getPk());
  }

  @Override
  public int hashCode() {

    return Objects.hash(getPk());
  }
}

2 : ForwardPower

@Entity
@Table(name = "forward_power")
public class ForwardPower implements  Serializable, Cloneable  {

  public ForwardPower() {
  }

  @EmbeddedId
  private Pk pk;

  @Column(name = "RECEIVE_TIME")
  private Timestamp reportingTime;

  @Column(name = "DATA2")
  private Double DATA2;

  public Pk getPk() {
    return pk;
  }

  public ForwardPower setPk(Pk pk) {
    this.pk = pk;
    return this;
  }

  public Timestamp getReportingTime() {
    return reportingTime;
  }

  public ForwardPower setReportingTime(Timestamp reportingTime) {
    this.reportingTime = reportingTime;
    return this;
  }

  public Double getDATA2() {
    return DATA2;
  }

  public ForwardPower setDATA2(Double DATA2) {
    this.DATA2= DATA2;
    return this;
  }

  @Override
  public boolean equals(Object o) {
    if (this == o) {
      return true;
    }
    if (o == null || getClass() != o.getClass()) {
      return false;
    }
    ForwardPower that = (ForwardPower) o;
    return Objects.equals(getPk(), that.getPk());
  }

  @Override
  public int hashCode() {

    return Objects.hash(getPk());
  }
}

I want to execute a query

Select * From energy e    
Left join forward_power fp    
on fp.dataId== e.dataId and fp.occurrenceTime == e.occurrenceTime    
where     e.occurrenceTime >= '2017-12-28 00:00:00'     
and       e.occurrenceTime <= '2018-01-02 00:00:00'    
Limit 1000;

I wrote a equivalent Query in java using JPA criteria Query

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<LoadProfile> cq = cb.createQuery(LoadProfile.class);
Root<LoadProfile> loadProfileRoot = cq.from(LoadProfile.class);
Join<LoadProfile, ForwardPower> join = loadProfileRoot.join(LoadProfile_.forwardPower);
List<Predicate> conditions = new ArrayList();
conditions.add(cb.equal(loadProfileRoot.get(LoadProfile_.pk).get(Pk_.dataId), join.get(
    ForwardPower_.pk).get(Pk_.dataId)));
conditions.add(cb.equal(loadProfileRoot.get(LoadProfile_.pk).get(Pk_.occurrenceTime),
    join.get(ForwardPower_.pk).get(Pk_.occurrenceTime)));

conditions.add(
    cb.greaterThanOrEqualTo(loadProfileRoot.get(LoadProfile_.pk).get(Pk_.occurrenceTime),
        config.getDataStartTime()));
conditions.add(
    cb.lessThanOrEqualTo(loadProfileRoot.get(LoadProfile_.pk).get(Pk_.occurrenceTime),
        config.getDataEndTime()));

cq.select(loadProfileRoot);
cq.where(conditions.toArray(new Predicate[]{}));
Query query = session.createQuery(cq);
List list = query.setFirstResult(0).setMaxResults(1000).getResultList();

I set the Option hibernate.show_sql = true. Now that query gives me exact 1000 desired result. when i see the hibernate query which is generated by ORM by above code. ORM create 1 query for energy table and 1000 queries for forwardpower table which cause performance issue and query take too much time aproximately 55 - 60 seconds for fetching 1000 records.

How i can create a criteria Query so that ORM generate exactly 1 query for that code?

Thanks in advance.

Answer 1

You can add a fetch type eager instructions on your relation, and the ForwardPower will be load with LoadProfile with any LoadProfile.find

  @OneToOne(fetch=FetchType.EAGER)
  @JoinColumns({
      @JoinColumn(name = "dataId", insertable = false, updatable = false, referencedColumnName = "dataId"),
      @JoinColumn(name = "occurrenceTime", insertable = false, updatable = false, referencedColumnName = "occurrenceTime")
  })
  private ForwardPower forwardPower;

Or you can add the fetch instruction in your query. I'm not familiar with it but it's probably something like that

//instead of loadProfileRoot.join(LoadProfile_.forwardPower)
Join<LoadProfile, ForwardPower> join = (Join<LoadProfile, ForwardPower>) loadProfileRoot.fetch(LoadProfile_.forwardPower);

See JPA 2 Criteria Fetch Path Navigation for more information about fetch with CriteriaBuilder.