Consulta de criterios JPA: cómo implementar la unión en dos tablas para obtener el resultado deseado en una sola consulta
Tengo 2 clases mapeadas con tablas de th, db.
Clase de clave primaria compuesta:
@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: Perfil de carga
@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: potencia delantera
@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());
}
}
Quiero ejecutar una consulta
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;
Escribí una consulta equivalente en Java usando la consulta de criterios JPA
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();
Configuré la opción hibernate.show_sql = true. Ahora esa consulta me da exactamente 1000 resultados deseados. cuando veo la consulta de hibernación que ORM genera mediante el código anterior. ORM crea 1 consulta para la tabla de energía y 1000 consultas para la tabla de potencia directa que causan problemas de rendimiento y la consulta lleva demasiado tiempo aproximadamente 55 - 60 segundos para obtener 1000 registros.
¿Cómo puedo crear una consulta de criterios para que ORM genere exactamente 1 consulta para ese código?
Gracias por adelantado.