当hql有GROUP BY时，如何才能获得休眠中的行数？ [英] how can get count of rows in hibernate when hql have group by?

问题描述

我有具有GROUP BY的hql查询。在分页结果中，我希望获得要在分页中显示的所有结果的计数。 在查询不具有GROUP BY I时，编写一个从hql查询创建查询计数实用程序，如下所示

select u 
from Personel u 
where u.lastname='azizkhani'

我找到主"from"关键字和子字符串hql并添加count(*)，然后进行此查询

select count(*) 
from Personel u  
where u.lastname='azizkhani'

当我有包含GROUP BY查询时，我不能这样做

select u.lastname,count(*) 
from Personel u 
group by u.lastname;

SQL中查询计数为

select count(*) 
   from (
         select u.lastname,count(*) 
         from tbl_personel u 
         group  by u.lastname
    )

如何从hql生成GROUP BY查询？

我有具有如下方法的GenericRepository

public <U> PagingResult<U> getAllGrid(String hql,Map<String, Object> params,PagingRequest searchOption);

和开发人员这样称呼

   String hqlQuery = " select e from Personel e where 1<>2 and e.lastname=:lastname";

    HashMap<String, Object> params = new HashMap<String, Object>();
    params.put("lastname", 'azizkhani');


    return getAllGrid(hqlQuery, params, new PagingRequest( 0/*page*/, 10 /*size*/) );

在GenericRepository中，我将返回具有属性的PagingResult对象

public class PagingResult<T> {

    private int totalElements;

    @JsonProperty("rows")
    private List<T> items;

    public PagingResult() {

    }

    public PagingResult(int totalElements, List<T> items) {
        super();
        this.totalElements = totalElements;
        this.items = items;
    }


    public int getTotalElements() {
        return totalElements;
    }

    public void setTotalElements(int totalElements) {
        this.totalElements = totalElements;
    }


    public List<T> getItems() {
        return items;
    }

    public void setItems(List<T> items) {
        this.items = items;
    }

}

在GenericRepository中，我将执行两个查询，第一个是Get 10 Result，第二个是Get totalRecords。开发人员只需发送Hql。我将为Get Totalcount创建hql。对于没有"DISTINCT"或"GROUP BY"查询，我创建了HQL。但是当HQL有"DISTINCT"和"GROUP BY"时，我就有问题了。

public <U> PagingResult<U> getAllGrid(String hql, Map<String, Object> params, PagingRequest searchOption) {
        Session session = getSession();
        applyDafaultAuthorizeFilter(session);


        Query query = session.createQuery(hql);
        if (searchOption != null) {
            if (searchOption.getSize() > 0) {
                query.setFirstResult(searchOption.getPage() * searchOption.getSize());
                query.setMaxResults(searchOption.getSize());
            }
        }
        if (params != null)
            HQLUtility.setQueryParameters(query, params);

        List<U> list = query.getResultList();

        Query countQuery = session.createQuery("select count(*) " + HQLUtility.retriveCountQueryFromHql(hql));

        if (params != null)
            HQLUtility.setQueryParameters(countQuery, params);

        int count = ((Long) countQuery.uniqueResult()).intValue();
        if (searchOption != null)
            return new PagingResult<U>(searchOption.getPage(), count, searchOption.getSize(), list);
        else
            return new PagingResult<U>(0, count, 0, list);
    }


   public static StringBuffer retriveCountQueryFromHql(StringBuffer jql) {
        if(jql.indexOf("order by")>=0)
            jql.replace(jql.indexOf("order by"), jql.length(),"");
        String mainQuery = jql.toString();

        jql = new StringBuffer(jql.toString().replace('	', ' '));
        int firstIndexPBas = jql.indexOf(")");
        int firstIndexPBaz = jql.lastIndexOf("(", firstIndexPBas);
        while (firstIndexPBas > 0) {
            for (int i = firstIndexPBaz; i < firstIndexPBas + 1; i++)
                jql.replace(i, i + 1, "*");
            firstIndexPBas = jql.indexOf(")");
            firstIndexPBaz = jql.lastIndexOf("(", firstIndexPBas);
        }
        int Indexfrom = jql.indexOf(" from ");
        return new StringBuffer(" " + mainQuery.substring(Indexfrom, jql.length()));
    }

    public void applyDafaultAuthorizeFilter(Session session) {
        Filter filter = session.enableFilter("defaultFilter");
        filter.setParameter("userId", SecurityUtility.getAuthenticatedUserId());
        filter.setParameter("orgId", SecurityUtility.getAuthenticatedUserOrganization().getId());
    }

如何在不更改通用存储库签名的情况下解决此问题？

我想我已经有了将hql转换为SQL并创建如下原生查询解决方案 SELECT COUNT(*)From(Hql_To_SQL) 但我有两个问题

1. hql to SQL没有支持参数的接口
2. hql to SQL没有支持休眠筛选器的API

推荐答案

为什么不将group by替换为count(distinct)？

SO而不是

select u from tbl_personel u group by u.lastname

您需要

select count(distinct u.lastname) from tbl_personel u

这篇关于当hql有GROUP BY时，如何才能获得休眠中的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！