This is not classical N+1 problem. My issue is conserning using projections and DTO objects in Jpa.
I have next method with JPA Query:
public List<MeterDTO> getAllBrokenMeterByHouseServ(House house, Serv serv, Date dt) {
Query query =em.createQuery("select new MeterDTO(m, g.kart.lsk, nvl(e.tp,0)) from Meter m "
+ "join m.exs e with m.id=e.meter.id "
+ "join m.meterLog g with m.meterLog.id=g.id "
+ "join g.kart k with g.kart.id=k.id and :dt between k.dt1 and k.dt2 "
+ "join g.serv s with g.serv.id=s.id "
+ "join k.kw kw with k.kw.id=kw.id "
+ "join kw.house h with kw.house.id=h.id "
+ "where s.id = :servId "
+ "and kw.house.id = :houseId "
+ "and :dt between e.dt1 and e.dt2 and nvl(e.tp,0) in (2,3,4) "
+ "");
query.setParameter("servId", serv.getId());
query.setParameter("houseId", house.getId());
query.setParameter("dt", dt);
return query.getResultList();
}
I fetch records from the query above into data transfer object:
meterDao.getAllBrokenMeterByHouseServ(house, serv, dt2).stream().forEach(t-> {
log.info("meter.id={}, lsk={}, tp={} ", t.getMeter().getId(), t.getLsk(), t.getTp());
});
MeterDTO:
@Getter @Setter
public class MeterDTO {
private Meter meter;
private Integer lsk;
private Double tp;
public MeterDTO(Meter meter, Integer lsk, Double tp) {
super();
this.meter = meter;
this.lsk = lsk;
this.tp = tp;
}
}
Why does hibernate produce one main query:
select
meter0_.ID as col_0_0_,
kart3_.lsk as col_1_0_,
nvl(exs1_.TP,
0) as col_2_0_
from
MT.METER meter0_
inner join
MT.METER_EXS exs1_
on meter0_.ID=exs1_.FK_METER
and (
meter0_.ID=exs1_.FK_METER
)
inner join
MT.METER_LOG meterlog2_
on meter0_.FK_METER_LOG=meterlog2_.ID
and (
meter0_.FK_METER_LOG=meterlog2_.ID
)
inner join
AR.KART kart3_
on meterlog2_.FK_KLSK_OBJ=kart3_.FK_KLSK_OBJ
and (
kart3_.lsk=kart3_.lsk
and (
? between kart3_.DT1 and kart3_.DT2
)
)
inner join
AR.KW kw6_
on kart3_.FK_KW=kw6_.ID
and (
kart3_.FK_KW=kw6_.ID
)
inner join
AR.HOUSE house7_
on kw6_.FK_HOUSE=house7_.ID
and (
kw6_.FK_HOUSE=house7_.ID
)
inner join
TR.SERV serv5_
on meterlog2_.FK_SERV=serv5_.ID
and (
meterlog2_.FK_SERV=serv5_.ID
)
where
serv5_.ID=?
and kw6_.FK_HOUSE=?
and (
? between exs1_.DT1 and exs1_.DT2
)
and (
nvl(exs1_.TP, 0) in (
2 , 3 , 4
)
)
and multiple queries with different bind argument "?" to load every entity:
select
meter0_.ID as ID1_44_0_,
meter0_.FK_K_LSK as FK_K_LSK2_44_0_,
meter0_.FK_METER_LOG as FK_METER_LOG4_44_0_,
meter0_.TRANS_RATIO as TRANS_RATIO3_44_0_
from
MT.METER meter0_
where
meter0_.ID=?
How to avoid this issue? I want to load all entities Meter in one main query. Is it possible?
I use:
<spring-framework.version>5.0.5.RELEASE</spring-framework.version>
<hibernate.version>5.1.0.Final</hibernate.version>
Any help would be greatly appreciated.
upd1
I simplified my JPA query code to this:
public List<MeterDTO> getAllBrokenMeterByHouseServ(House house, Serv serv, Date dt) {
Query query =em.createQuery("select new com.ric.bill.dto.MeterDTO(m) from Meter m ");
}
But it still produces mutiple queries:
select
meter0_.ID as ID1_44_0_,
meter0_.FK_K_LSK as FK_K_LSK2_44_0_,
meter0_.FK_METER_LOG as FK_METER_LOG4_44_0_,
meter0_.TRANS_RATIO as TRANS_RATIO3_44_0_
from
MT.METER meter0_
where
meter0_.ID=?
20-04-2018 12:52:49.482 [main] DEBUG o.h.l.p.e.p.i.ResultSetProcessorImpl - Starting ResultSet row #0
20-04-2018 12:52:49.482 [main] DEBUG org.hibernate.SQL -
select
meter0_.ID as ID1_44_0_,
meter0_.FK_K_LSK as FK_K_LSK2_44_0_,
meter0_.FK_METER_LOG as FK_METER_LOG4_44_0_,
meter0_.TRANS_RATIO as TRANS_RATIO3_44_0_
from
MT.METER meter0_
where
meter0_.ID=?
<Skipped>
very strange!
upd2 Meter entity:
@SuppressWarnings("serial")
@Entity
@Table(name = "METER", schema="MT")
@Getter @Setter
public class Meter extends Base implements java.io.Serializable, Storable {
public Meter (){
}
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID", updatable = false, nullable = false)
protected Integer id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="FK_METER_LOG", referencedColumnName="ID")
private MeterLog meterLog ;
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval=true)
@JoinColumn(name="FK_METER", referencedColumnName="ID")
@BatchSize(size = 50)
private List<Vol> vol = new ArrayList<Vol>(0);
@OneToMany(fetch = FetchType.LAZY)
@JoinColumn(name="FK_METER", referencedColumnName="ID")
@BatchSize(size = 50)
private List<MeterExs> exs = new ArrayList<MeterExs>(0);
@Column(name = "TRANS_RATIO", updatable = true, nullable = true)
private Double trRatio;
}
In DTO you have 'Meter meter' field, in meter field you have 'MeterLog meterlog' etc. In this case Hibernate is additionally loading for field for full object. This DTO is to much complex. Try to create more flat object:
public class MeterDTO {
private Integer meterId
private Double meterTrRatio
private Integer lsk;
private Double tp;
(...)
And query will be:
(...) new MeterDTO(m.id, m.trans_ratio, g.kart.lsk (...)
And after that you can extending your DTO for the next fields you want.