java - JPA:如何避免生成多个 "select from"查询

Question

这不是经典的 N+1 问题。我的问题是在 Jpa 中使用投影和 DTO 对象。

我有 JPA 查询的下一个方法:

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

我从上面的查询中提取记录到数据传输对象:

    meterDao.getAllBrokenMeterByHouseServ(house, serv, dt2).stream().forEach(t-> {
        log.info("meter.id={}, lsk={}, tp={} ", t.getMeter().getId(), t.getLsk(), t.getTp());
    });

仪表DTO:

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

为什么 hibernate 会产生一个主查询:

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

以及具有不同绑定(bind)参数“?”的多个查询加载每个实体:

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=?

如何避免这个问题？我想在一个主查询中加载所有实体 Meter。可能吗？

我使用:

<spring-framework.version>5.0.5.RELEASE</spring-framework.version>
<hibernate.version>5.1.0.Final</hibernate.version>

任何帮助将不胜感激。

upd1

我将 JPA 查询代码简化为:

    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 ");
}

但它仍然会产生多个查询:

    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>

很奇怪!

upd2 计量实体:

@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; 

}

Answer 1

在 DTO 中，您有“Metermeter”字段，在meter字段中，您有“MeterLogmeterlog”等。在这种情况下，Hibernate 会另外加载完整对象的字段。这个 DTO 太复杂了。尝试创建更扁平的对象:

public class MeterDTO {

    private Integer meterId
    private Double meterTrRatio
    private Integer lsk;
    private Double tp;
    (...)

查询将是:

(...) new MeterDTO(m.id, m.trans_ratio, g.kart.lsk (...)

之后，您可以将 DTO 扩展到您想要的下一个字段。