如何避免 HQL 和 Criteria 中不必要的选择和连接 [英] How to avoid unnecessary selects and joins in HQL and Criteria

问题描述

我一直在尝试 HQL 和 Criteria 的不同组合，但我无法避免一些不必要的连接(在两者中)以及一些不必要的选择(在标准中).

在我们的场景中，Segment 和 Application 实体之间存在 @ManyToMany 关系(导航是从 Segment 到 Applications).>

首先我尝试了这个标准:

应用程序 app = ...列表<段>段 = session.createCriteria(Segment.class).createCriteria(Segment.APPLICATIONS).add(Restrictions.idEq(app.getId())).列表();

Wich 生成此 SQL:

选择this_.id 为 id1_1_，this_.description as descript2_1_1_,this_.name as name1_1_,application3_.segment_id 作为 segment1_1_​​，applicatio1_.id 为 app2_，<==== 不必要的 APPLICATIONS 列applicatio1_.id 为 id7_0_，application1_.name 为 name7_0_，applicatio1_.accountId 作为 accountId7_0_，applicatio1_.applicationFlags 为 applicat5_7_0_，application1_.description_ 作为descript6_7_0_，从SEGMENTS this_内部联接SEGMENTS_APPLICATIONS application3_在 this_.id=applicatio3_.segment_id内连接 <==== 不必要的连接应用应用1_在 applicatio3_.app_id=applicatio1_.id在哪里应用程序 1_.id = ?

如您所见，Criteria 从 APPLICATIONS 中选择列，我不想选择这些列.我还没有找到一种方法来做到这一点(有可能吗?).此外，它与 APPLICATIONS 连接，我认为这没有必要，因为应用程序 ID 已经在连接表 SEGMENTS_APPLICATIONS 中(HQL 也是如此).

(另外一个疑问，我想知道直接使用应用程序的限制，而不是 app.getId().如您所见，我可以在查询的 HQL 版本中做到这一点)

>

由于我无法限制选择部分(我不需要应用程序属性)，因此我尝试使用select"子句使用此 HQL:

应用程序 app = ...列表<段>段 = session.createQuery("select s from Segment s join s.applications as app where app = :app").setParameter("app", app).列表();

产生:

选择segment0_.id 为 id1_，segment0_.description 为 descript2_1_，segment0_.name 作为 name1_，从SEGMENTS 段 0_内部联接SEGMENTS_APPLICATIONS application1_在 segment0_.id=applicatio1_.segment_id内连接 <==== 不必要的连接应用应用程序2_在 applicatio1_.app_id=applicatio2_.id在哪里应用程序2_.id=?

您可以看到 HQL 没有从 Application 中选择属性(感谢select s"部分)，但仍然加入了 APPLICATIONS 表，我认为是不必要的.我们怎样才能避免这种情况?

(作为旁注，请注意在 HQL 中我可以直接使用 app，而不是像 Criteria 中那样的 app.getId())

你能帮我找到一种方法来避免标准中的选择"和标准和 HQL 中不必要的连接"吗?

(这个例子是@ManyToMany，但我认为@OneToMany、@ManyToOne 和@OneToOne 也会发生这种情况，即使是 fetch = LAZY).

非常感谢，费兰

解决方案

使用 Criteria 时附加的选定列来自长期存在的 Hibernate 中的错误.AFAIK，避免它的唯一方法是使用 HQL 或 JPA2 标准 API.

另一个问题也被标记为一个错误，但它的影响较小，我不会太在意它.

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

解决方案

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.

这篇关于如何避免 HQL 和 Criteria 中不必要的选择和连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！