JPA: How to avoid generating multiple "select from" queries

Question

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; 

}

If you don't want get related data, use the property FetchType.LAZY in your OneToMany annotation — Alberto, Apr 20 '18 at 06:28
@Alberto he wants to get related data (as you can clearly see from the query), but he wants them in a single query. He's creating a projection from the results. — Kayaman, Apr 20 '18 at 06:32
Possible duplicate of [What is the solution for the N+1 issue in JPA and Hibernate?](https://stackoverflow.com/questions/32453989/what-is-the-solution-for-the-n1-issue-in-jpa-and-hibernate) — Nikolai Shevchenko, Apr 20 '18 at 07:26

score 1 · Accepted Answer · answered Apr 20 '18 at 07:22

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.

score 0 · Answer 2 · answered Jan 18 '21 at 13:39

The accepted answer suggests changing the DTO, which would not always an acceptable solution.

Here is a solution with no need to change your DTO.

Write your HQL like this:

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 "
(more joins and wheres)

Note that there should not be any select.

getResultList will give you List<Object[]>. Each entry is an array of {Meter, m.exs, m.meterLog, g.kart, ....}. Pick the ones you need and make your MeterDTO.

In my case:

jpa repo

    @Query("from Bind bind "
        + "left join Employee employee "
        + "with bind.empCode = employee.empCode "
        + "where bind.accountName = :hiveAccount and bind.disabled = 1 ")
    List<Object[]> listMembers(@Param("hiveAccount") String hiveAccount);

DTO

public class BindDTO {
    Bind bind;
    Employee emp;

    public BindDTO(Object[] objs) {
        this((Bind) objs[0], (Employee) objs[1]);
    }

service

myRepo.listMembers(hiveAccount).stream().map(BindDTO::new).collect(Collectors.toList());

JPA: How to avoid generating multiple "select from" queries

2 Answers2