JPA标准查询-如何在两个表上实现联接以在单个查询中获得所需的结果 [英] JPA Criteria Query - How to implement Join on two tables to get desired result in single Query
问题描述
我有2个用db表映射的类.
I have 2 classes mapped with db Tables.
复合主键类:
@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
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
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());
}
}
我要执行查询
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中编写了等效查询
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();
我将选项设置为hibernate.show_sql = true. 现在，该查询为我提供了准确的1000个所需结果. 当我看到由上述代码由ORM生成的休眠查询时. ORM为能源表创建1个查询，为前向功率表创建1000个查询，这会导致性能问题，而获取1000条记录所需的查询花费了大约55-60秒的太多时间.
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.
我如何创建标准查询，以便ORM为该代码生成1个查询?
How i can create a criteria Query so that ORM generate exactly 1 query for that code?
谢谢.
推荐答案
您可以在关系中添加一个获取类型的详细说明，并且ForwardPower将与具有任何LoadProfile.find的LoadProfile一起加载
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);
有关通过以下方式进行抓取的更多信息，请参见 JPA 2标准抓取路径导航. CriteriaBuilder.
See JPA 2 Criteria Fetch Path Navigation for more information about fetch with CriteriaBuilder.
这篇关于JPA标准查询-如何在两个表上实现联接以在单个查询中获得所需的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
