Hello,
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:
Code:
Application app = ...
List<Segment> segments = session.createCriteria(Segment.class)
.createCriteria(Segment.APPLICATIONS)
.add(Restrictions.idEq(app.getId()))
.list();
Wich produces this SQL:
Code:
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 a "select" clause:
Code:
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:
Code:
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
Login
FAQ
Search