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

问题描述

我一直在尝试 HQL 和条件的不同组合，并且我无法避免一些不必要的连接和一些不必要的选择（在标准中）。

在我们的场景中， @ManyToMany 段和应用实体（导航是从段到应用程序）。 首先，我尝试了 Criteria ：

 应用程序应用程序= ... 
列表< Segment> segments = session.createCriteria（Segment.class）
 .createCriteria（Segment.APPLICATIONS）
 .add（Restrictions.idEq（app.getId（）））
 .list（）; 
  

产生这个SQL：

 选择
 this_.id作为id1_1_，
 this_.description作为descript2_1_1_，
 this_.name作为name1_1_，
 applicationatio3_.segment_id作为segment1_1_​​，
 applicatio1_.id如app2_，< ====不必要的应用程序列
 applicatio1_.id如id7_0_，
 applicatio1_.name如name7_0_，
 applicatio1_.accountId如accountId7_0_，
 applicatio1_.applicationFlags如applicat5_7_0_，
 applicatio1_.description_如descript6_7_0_，从
 SEGMENTS THIS_ 
内
加入
 SEGMENTS_APPLICATIONS applicatio3_ 
上this_.id = applicatio3_.segment_id 
内部连接< ====不必要的连接
应用程序application_tio1_ 
在applicatio3_.app_id = applicationatio1_.id 
其中
 applicatio1_.id =？ 
  

正如您所见， Criteria会从APPLICATIONS 中选择列，我不会不想被选中。我还没有找到办法做到这一点（有可能吗？）。另外，它还会加入APPLICATIONS ，我认为这不是必须的，因为应用程序ID已经在连接表SEGMENTS_APPLICATIONS中（HQL也是如此）。

（另外还有一个疑问，我想知道直接使用应用程序的限制，而不是app.getId（）。正如您将看到的，我可以在查询的HQL版本中执行此操作）

由于我无法限制选择部分（我不需要应用程序属性），因此我使用select子句尝试了 HQL ：

 应用程式应用程式= ... 
清单<区段> segments = session.createQuery（
从段中选择s加入s.applications作为app在哪里app =：app）
 .setParameter（app，app）
 .list（） ; 
  

产生：

 选择
 segment0_.id作为id1_，
 segment0_.description作为descript2_1_，
 segment0_.name作为name1_，
从
 SEGMENTS segment0_ 
内加入
 SEGMENTS_APPLICATIONS applicatio1_ 
上segment0_.id = applicatio1_.segment_id 
内加入<有关applicatio1_.app_id ====不必要加入
申请applicatio2_ 
 = applicatio2_.id 
其中
 applicatio2_.id =？ 
  

您可以看到 HQL不从应用程序中选择属性到select s部分），但是仍然加入了我认为不必要的APPLICATIONS 表。 （请注意，在HQL中，我可以直接使用应用程序，而不是像Criteria中那样使用app.getId（））。

/ p>

您能否帮我找到一种方法来避免Criteria中的选择和Criteria和HQL中不必要的连接？

（这个例子和@ManyToMany一样，但我认为它也会发生在@OneToMany和@ManyToOne和@OneToOne上，即使是fetch = LAZY也是如此）。

非常感谢，
Ferran

解决方案

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

另一个问题也被标记为一个bug ，但影响较小，我不会太在意。

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屋！