JPA条件查询-如何在两个表上实现Join以在单个查询中获得所需的结果

Question

我有2个与数据库表映射的类。

复合主键类：

@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 ： 加载配置文件

@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:前进动力

@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());
  }
}

我想执行查询

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;

我使用JPA标准在java中编写了一个等效的查询 查询

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();

我将选项设置为hibernate.show_sql = true。现在，该查询为我提供了确切的1000个所需结果。当我看到由ORM通过上述代码生成的Hibernate查询时。ORM为能量表创建1个查询，为前功率表创建1000个查询，这会导致性能问题，查询花费太多时间大约55 - 60秒来获取1000条记录。

我如何创建一个条件查询，以便ORM为该代码生成一个查询？

提前致谢。

Answer 1

您可以在您的关系上添加一个fetch类型的eager指令，ForwardPower将加载任何LoadProfile的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;

或者您可以在查询中添加fetch指令。我不熟悉它，但可能是这样的。

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

有关使用CriteriaBuilder提取的更多信息，请参见JPA 2 Criteria提取路径导航。