Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
517 views
in Technique[技术] by (71.8m points)

hibernate - How to avoid unnecessary selects and joins in HQL and Criteria

I have been trying different combinations of HQL and Criteria and I haven't been able to avoid some unnecessary joins (in both) and some unnecessary selects (in Criteria).

In our scenario, we have a @ManyToMany relationship between Segment and Application entities (navigation is from Segment to Applications).

First I tried this Criteria:

Application app = ...
List<Segment> segments = session.createCriteria(Segment.class)
    .createCriteria(Segment.APPLICATIONS)
    .add(Restrictions.idEq(app.getId()))
    .list();

Wich produces this SQL:

select
    this_.id as id1_1_,
    this_.description as descript2_1_1_,
    this_.name as name1_1_,
    applicatio3_.segment_id as segment1_1_,
    applicatio1_.id as app2_,               <==== unnecessary APPLICATIONS columns
    applicatio1_.id as id7_0_,
    applicatio1_.name as name7_0_,
    applicatio1_.accountId as accountId7_0_,
    applicatio1_.applicationFlags as applicat5_7_0_,
    applicatio1_.description_ as descript6_7_0_,
from
    SEGMENTS this_ 
inner join
    SEGMENTS_APPLICATIONS applicatio3_ 
        on this_.id=applicatio3_.segment_id 
inner join                                       <==== unnecessary join
    APPLICATIONS applicatio1_ 
        on applicatio3_.app_id=applicatio1_.id 
where
    applicatio1_.id = ?

As you can see, Criteria selects columns from APPLICATIONS, which I don't want to be selected. I haven't found a way to do it (is it possible?). Also, it joins with APPLICATIONS, which I think is not necessary because the application id is already in the join table SEGMENTS_APPLICATIONS (the same happens with HQL).

(As an additional doubt, I'd like to know a Restriction that uses the app directly, and not app.getId(). As you will see, I could do that in the HQL version of the query)

Since I couldn't limit the select part (I don't need Application properties) I tried this HQL with the "select" clause:

Application app = ...
List<Segment> segments = session.createQuery(
    "select s from Segment s join s.applications as app where app = :app")
    .setParameter("app", app)
    .list();

wich produces:

select
    segment0_.id as id1_,
    segment0_.description as descript2_1_,
    segment0_.name as name1_,
from
    SEGMENTS segment0_ 
inner join
    SEGMENTS_APPLICATIONS applicatio1_ 
        on segment0_.id=applicatio1_.segment_id 
inner join                                        <==== unnecessary join
    APPLICATIONS applicatio2_ 
        on applicatio1_.app_id=applicatio2_.id 
where
    applicatio2_.id=? 

You can see the HQL doesn't select properties from Application (thanks to the "select s" part), but still joins the APPLICATIONS table, which I think is unnecessary. How can we avoid that?

(As a side note, notice that in HQL I could use app directly, and not app.getId() like in the Criteria)

Can you please help me find a way to avoid "selects" in Criteria and unnecessary "joins" in both Criteria and HQL?

(This example is with @ManyToMany but I think it also happens with @OneToMany and also with @ManyToOne and @OneToOne, even with fetch = LAZY).

Thank you very much, Ferran

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The additional selected columns when using Criteria come from a long-standing bug in Hibernate. AFAIK, the only way to avoid it is to use HQL, or the JPA2 criteria API.

The other problem is also signalled as a bug, but it has fewer impacts, and I wouldn't care much about it.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...