如何将一个where子句添加到Hibernate @OneToMany显式连接表实体？ [英] How to add a where clause to a Hibernate @OneToMany explicit join table entity?

问题描述

给定两个实体：

$ ul $ b $ li $ codeard $ <$ $ c $>

PurchaseProductGroup ，它具有 ppg_status 列（字段名为 status 'A'（active）或'D'（已删除）

这些概念上有一个多对多的关系，但有一个明确定义的连接表实体，名为 PurchaseProductGroupCard 被使用（因此可以为每个映射分配一个外部ID）。因此卡和 PurchaseProductGroup 都有一个 @OneToMany 关系 PurchaseProductGroupCard ，例如在卡片中有以下内容：

  @OneToMany（mappedBy =卡）
私人设置< PurchaseProductGroupCard> purchaseProductGroups; 
  

这需要进行限制，以便状态为'D'的purchaseProductGroups不包括。似乎工作的一种方法是在 @OneToMany 下面放置 @Where 注释：

  @Where（clause =exists（select * from purchase_product_group ppg 
 where ppg.ppg_id = ppg_id AND ppg.ppg_status< 'D'））
  

...但是有没有更好的方法来做到这一点？理想情况下，希望Hibernate加入这些表并且有一个像purchaseProduct.status<> D'）的子句。

对于上面的情况，它是这样的：

  / *加载一对多的com.prepaytec.pacasso.common.model。 Card.purchaseProductGroups * / 
选择
 * / *实际的字段列表已被省略以简化* / 
从
 pacasso.purchaseprodgrp_card purchasepr0_ 
内部连接
 pacasso.purchase_product_group purchasepr1_ 
在purchasepr0_.ppg_id = purchasepr1_.ppg_id 
其中
（
存在（
从$ b中选择
 * 
 $ b purchase_product_group ppg 
其中
 ppg.ppg_id = purchasepr0_.ppg_id 
 AND ppg.ppg_status<>'D'
）
）
和purchasepr0_.crd_id =？ 
  

所以必要的连接已经包含在内了，它看起来像所有那样需要的是这样的：

pre $ @Where（clause =ppg_status<>'D'）


然而，事实证明不 正常工作，因为Hibernate会在表名前添加错误：

 其中
（
 purchasepr0_.ppg_status<>'D'
）
和purchasepr0_.crd_id =？ 
  

不幸的是，一旦别名被分配给一个表，就不可能使用原始表名 - 所以 purchase_product_group.ppg_status<> 'D'不起作用。而且我还没有意识到通过编程来确定Hibernate使用的别名的方法 - 所以目前的选择似乎是硬编码Hibernate使用的别名（即 purchasepr1_.ppg_status<>'D'）或使用问题中描述的 exists 方法。

更新：进一步调查发现，对别名进行硬编码并不总是可行的。这是不符合标准的查询：

  / *条件查询* / 
 select 
 * / *为简洁起见，实际的字段列表已被省略* / 
 from 
 pacasso.merchant_acquirer this_ 
 left outer join 
 pacasso.purchaseprod_merchant_acquirer purchasepr2_ 
 on this_。 mac_id = purchasepr2_.mac_id 
和（
 //这不适用于任何别名，因为所需的
 //表是pacasso.purchase_product purchasepr3_，其中
 //已加入
 purchasepr2_.ppr_status<>'D'
）
 left outer join 
 pacasso.purchase_product purchasepr3_ 
 on purchasepr2_.ppr_id = purchasepr3_.ppr_id 
其中
 this_.mac_code =？ 
和this_.cst_id =？ 
  

最后我放弃了 @Where 方法并使用 @Filter 代替，这看起来好多了，因为它可以接受HQL而不是数据库字段名称，并且在实体级别应用时会影响关系（与<$ c $不同c> @Where ）。

Given two entities:

• Card
• PurchaseProductGroup, which has a ppg_status column (field named as status on the entity) that can be 'A' (active) or 'D' (deleted)

These conceptually have a many-to-many relationship but an explicitly defined join table entity named PurchaseProductGroupCard is used (so an external ID can be assigned for each mapping). So both Card and PurchaseProductGroup have a @OneToMany relationship to PurchaseProductGroupCard, e.g. in Card there is the following:

@OneToMany(mappedBy = "card")
private Set<PurchaseProductGroupCard> purchaseProductGroups;

This needs to be restricted so that purchaseProductGroups with a status of 'D' are excluded. One approach that seems to work is to put a @Where annotation just below the @OneToMany:

@Where(clause = "exists (select * from purchase_product_group ppg
                         where ppg.ppg_id = ppg_id AND ppg.ppg_status <> 'D')")

...But is there a better way to do this? Would ideally prefer Hibernate to join the tables and have a clause like "purchaseProduct.status <> 'D'").

解决方案

I turned on SQL logging and examined the query output. For the above case it was this:

/* load one-to-many com.prepaytec.pacasso.common.model.Card.purchaseProductGroups */
select
    * /* the actual field list has been omitted for brevity */
from
    pacasso.purchaseprodgrp_card purchasepr0_
inner join
    pacasso.purchase_product_group purchasepr1_
        on purchasepr0_.ppg_id=purchasepr1_.ppg_id
where
    (
        exists (
            select
                *
            from
                purchase_product_group ppg
            where
                ppg.ppg_id = purchasepr0_.ppg_id
                AND ppg.ppg_status <> 'D'
        )
    )
    and purchasepr0_.crd_id=?

So the necessary join is already included and it looks like all that would be needed is this:

@Where(clause = "ppg_status <> 'D'")

However, it turns out that doesn't work as Hibernate prepends the wrong table alias:

where
    (
        purchasepr0_.ppg_status <> 'D'
    )
    and purchasepr0_.crd_id=?

Unfortunately once an alias is assigned to a table, it isn't possible to use the original table name - so purchase_product_group.ppg_status <> 'D' wouldn't work. And I'm not aware of a way to determine the alias name used by Hibernate programmatically - so at present the choice seems to be either hard-code the alias name that is found to be used by Hibernate (i.e. purchasepr1_.ppg_status <> 'D') or to use the exists method described in the question.

UPDATE: On further investigation it turns out that hard-coding the alias names isn't always workable. Here is a criteria query where this wouldn't work:

/* criteria query */
select
    * /* the actual field list has been omitted for brevity */
from
    pacasso.merchant_acquirer this_ 
left outer join
    pacasso.purchaseprod_merchant_acquirer purchasepr2_ 
        on this_.mac_id=purchasepr2_.mac_id 
        and (
            // This wouldn't work with any alias since the required
            // table is pacasso.purchase_product purchasepr3_, which
            // is joined below.
            purchasepr2_.ppr_status <> 'D' 
        )  
left outer join
    pacasso.purchase_product purchasepr3_ 
        on purchasepr2_.ppr_id=purchasepr3_.ppr_id 
where
    this_.mac_code=? 
    and this_.cst_id=?

In the end I abandoned the @Where approach and used @Filter instead, which seems much better as it can accept HQL rather than database field names and when applied at the entity level will affect relationships (unlike @Where).

这篇关于如何将一个where子句添加到Hibernate @OneToMany显式连接表实体？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！