hibernate支持count（*）over（） [英] Does hibernate support count(*) over()

问题描述

我试图阻止必须为count创建一个单独的查询，并为实际查询创建一个查询。我发现SesssionImpl :: createQuery需要大量的时间来处理复杂的查询，并且通过将count和主查询结合起来，我可以消除一个createQuery调用。

In SQL我可以做一些像

  select count（*）over（），col_A，col_B 
 TABLE_XX 
 where col_C> 1000 
  

这可以在hibernate中实现吗？

< （我试图避免原生sql并坚持HQL和分离条件，使用本机SQL会破坏使用hibernate的目的，我的系统必须同时支持Oracle和Sybase）

解决方案

Hibernate的 @Formula 注释可能会用作解决方法。在 @Entity 中提供一个额外的列来表示计数，并用 @Formula 进行注释，其中包含 COUNT OVER query：

  @Formula（count（*）over（）） 
 private Integer totalResults; 
  

使用这种方法，Hibernate会生成一些SQL，所以您可能还需要注册 Interceptor 来清理SQL：

  public class CountOverQueryInterceptor extends EmptyInterceptor {
 
 private static final long serialVersionUID = -2980622815729060717L; 
 
 @Override 
 public String onPrepareStatement（String sql）{
 return super.onPrepareStatement（cleanCountOver（sql））; 
 
 $ b $ ** 
从Hibernate的sql 
 * decoration中清除{@code count（*）over（）}查询片段{@代码计数（*）<别名> .over（）}。 
 * / 
 static String cleanCountOver（String sql）{
 return sql.replaceAll（（？i）（count）\\（\\ * \\） （over）\\（\\），$ 1（*）$ 2（））; 
 
 
 $ b除了明确的Hibernate依赖关系之外（而不是纯粹的JPA），还有一个缺点，例如默认加载此列，这可能会增加一些不必要的开销，以便不需要计数。可以将列设置为可选，并且 lazily loaded ，但这需要字节码检测，并增加了更多层次的复杂性。 
 
 
  @Formula （count（*）over（））
 @Basic（optional = true，fetch = FetchType.LAZY）
 private Integer totalResults; 
  
 
I'm trying to prevent having to create one separate query for count and one for the actual query.  What I found is SesssionImpl::createQuery takes a considerable amount of time for a complex query and by combining count and the main query I can then eliminate one createQuery call.

In SQL I can do something like 
select count(*) over(), col_A, col_B 
from TABLE_XX 
where col_C > 1000
Can this be achieved in hibernate?

(I'm trying to avoid native sql and stick to HQL and detached criteria. Using native SQL defeats the purpose of using hibernate. My system has to support both Oracle and Sybase)
解决方案
Hibernate's @Formula annotation may work as a workaround. Provide an additional column in your @Entity to represent the count, annotated with @Formula which contains the COUNT OVER query:
@Formula("count(*) over ()")
private Integer totalResults;
With this approach, Hibernate butchers the generated SQL a bit, though, so you may also need to register an Interceptor to clean up the SQL:
public class CountOverQueryInterceptor extends EmptyInterceptor {

    private static final long serialVersionUID = -2980622815729060717L;

    @Override
    public String onPrepareStatement(String sql) {
        return super.onPrepareStatement(cleanCountOver(sql));
    }

    /**
     * Cleans the "{@code count(*) over ()}" query fragment from Hibernate's sql
     * decoration "{@code count(*) <alias>.over ()}".
     */
    static String cleanCountOver(String sql) {
        return sql.replaceAll("(?i) (count)\\(\\*\\) \\w+\\.(over) \\(\\)", " $1(*) $2 () ");
    }

}
Aside from the explicit Hibernate dependencies (rather than pure JPA), there is one other downside, such that this column will be loaded by default which may add some unnecessary overhead to queries where the count is not needed. It may be possible to make the column optional and lazily loaded, but this requires bytecode instrumentation and adds further layers of complexity.
@Formula("count(*) over ()")
@Basic(optional = true, fetch = FetchType.LAZY)
private Integer totalResults;


                        
这篇关于hibernate支持count（*）over（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！