如何用OR构造高级Hibernate查询并汇总函数 [英] How to construct advanced Hibernate Query with OR and summarize functions

问题描述

我有一个相当复杂的查询在SQL中工作，但我想在HQL中表达它的可移植性。如果不存在，我将获取用户配置的首选项值，如果不存在，我必须使用默认值。这个值必须从当前日期中减去，并匹配表中我感兴趣的列：

  select d .id，d.guid，d.deletetimestamp，u.id描述符d中的
，ownerkey ow，用户u 
其中
 d.parentid in 
（select td.id 
 from descriptor td，store s 
 where s.type ='Trash'
 and s.descriptorid = td.id 
）
 and d.ownerkeyid = ow.id 
和ow.ownerid = u.id 
和
（
（d.deletetimestamp< CURRENT_TIMESTAMP（） -  INTERVAL 
（select pv.value 
 from preferencevalue pv，userpreference up 
 where u.id = up.userid 
 and up.preferenceid = 26 
 and up.value = pv.id）
 DAY）
或
（d.deletetimestamp< CURRENT_TIMESTAMP（） -  INTERVAL 
（选择强制转换（pv.value为SIGNED）
从preferencevalue pv，defaultpreference dp 
 where dp.preferenceid = 26 
并且不存在（从userpreference选择up.userid，其中u.id = up.userid和up.preferenceid = 26）
和dp.value = pv.id）
 DAY）
）
  

I我试图通过使用Criteria API来构建它，它似乎包含了我需要的大部分逻辑运算符（等于，大于或isEmpty / isNull），但不知道如何表达所有这些部分。

因为我们使用MySQL作为生产数据库，而集成测试使用H2 inmemory数据库运行，所以此时不​​使用视图。我不能在H2中找到sata减法函数，而MySQL支持这一点。

select字段并不重要，因为它们只用于测试目的。

解决方案

1. 您可以使用 Restrictions.disjunction（） for或 - 和 Restrictions.conjuction（） for和子句。为了引用实体的某个属性（如pv.value），可以使用 Projections.property（value）
/ li>

2. 我不确定是否可以在实体上使用 @Formula 注释。但是这是一个hibernate，而不是JPA批注。据我所知，在hibernate中没有对应于 INTERVAL 的等价物，但是在这样的情况下（也可以用于上面的转换），你可以使用 Restrictions.sqlRestriction（some sql ...）

将所有这些结合在一起将您的查询转换为hibernate标准将是一项挑战。

greetz，

Stijn

I have a rather complex query which works in SQL, but I would like to express this in HQL for portability. I'm going to fetch a user configured preference value if they exist, if not I must use a default value. This value must be subtracted from current date and the matched against a column in the table which I'm interested in:

select d.id, d.guid, d.deletetimestamp, u.id 
from descriptor d, ownerkey ow, user u
where 
    d.parentid in 
        (select td.id 
         from descriptor td, store s 
         where s.type = 'Trash' 
         and s.descriptorid = td.id
        )
    and d.ownerkeyid = ow.id
    and ow.ownerid = u.id
    and 
       (
         (d.deletetimestamp < CURRENT_TIMESTAMP() - INTERVAL 
            (select pv.value 
             from preferencevalue pv, userpreference up
             where u.id = up.userid
             and up.preferenceid = 26 
             and up.value = pv.id) 
          DAY)
       or 
        (d.deletetimestamp < CURRENT_TIMESTAMP() - INTERVAL
            (select cast(pv.value as SIGNED) 
             from preferencevalue pv, defaultpreference dp
             where dp.preferenceid = 26
             and not exists(select up.userid from userpreference up where u.id = up.userid and up.preferenceid = 26)
             and dp.value = pv.id)
           DAY)
       )

I'm trying to construct this by using the Criteria API which seems to include most of the logical operators that I need (equals, larger than, or, isEmpty/isNull), but not sure how I would express all these parts.

Using a view is not an option at this point since we're using MySQL as the production database while the integration tests are running with H2 inmemory database. I'm not able to get find the sata substract function in H2 while MySQL do support this.

The select fields isn't important since they have only been used for testing purposes.

解决方案

1. you can use Restrictions.disjunction() for or -and Restrictions.conjuction() for and clauses.
2. To reference a certain property of an entity (like pv.value) you can use Projections.property("value")
3. for the casting I'm not sure, perhaps using the @Formula annotation on your entity? But this is a hibernate and not a JPA annotation.
4. as far as I know there is no equivalent for INTERVAL in hibernate but in such cases (maybe also for the above cast) you could use Restrictions.sqlRestriction("some sql...")

It will be a challenge putting all of this together to transform your query to hibernate criteria.

greetz,
Stijn

这篇关于如何用OR构造高级Hibernate查询并汇总函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！