如何将跨多个表的联接查询从 JDOQL 移植到 HQL

Question

我正在将 KodoJDO 的应用程序移植到 Hibernate。 我有一个查询跨越数据库中的 4 个表和 java 代码中的 3 个对象。

在英语中，查询是 Find the users that have entitlements in system X.

我的 JDOQL where 子句在 User 对象上调用是 其中 entitlements.contains(ent) && (upper( ent.system.id ) = 'EVPN')

执行查询的一些 sql 是：

 select unique(u.id)
 from USER u, USERENTITLEMENT ue, ENTITLEMENT e, SYSTEM s
 where u.id = ue.userid
 and ue.entitlementid = e.id
 and e.systemid = s.id
 and s.id = 'evpn'

我对 HQL 的最佳猜测给了我一个例外，

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( [select user from com.ebig.entity.User as user, com.ebig.entity.Entitlement as ent, com.ebig.entity.System as sys where  entitlements.contains(ent) and ent.system = sys and sys.id  = 'evpn']

数据库的结构如下：

 User
   id

 UserEntitlement
   userid
   entitlementid

 Entitlement
   id
   systemid

 System
   id

java 代码的结构如下：

 class User 
 {
    String id;
    Set<Entitlement> entitlements;
 }

 class Entitlement
 {
    String id;
    System system;
 }

 class System
 {
    String id;
 }

更新 我的最终查询有效

            hqlQuery = "select distinct user from User as user "+
        "inner join user.entitlements as entitlement inner join entitlement.system as system "+
        "where  system.id  = 'evpn'  AND mod(user.flags, 2) = 0  AND source = 1";

是的，我知道我应该使用参数，但是我有很多问题需要解决，并将在另一天发布该代码。

另一种带有隐式内部连接的变体，用于获取系统权限

        hqlQuery = "select distinct user from User as user "+
        "inner join user.entitlements as entitlement "+
        "where  entitlement.system.id  = 'evpn'  AND mod(user.flags, 2) = 0  AND source = 1";

Answer 1

您应该使用 joins :

select distinct u.id from User u
inner join u.entitlements as entitlement
inner join entitlement.system as system
where system.id = :evpn

其中 :evpn 是您必须绑定的命名参数。

进行 HQL 时必须考虑对象和对象之间的关系，而不是表、外键和连接表。