title: how can get count of rows in hibernate when hql have group by?
body: <p>I have hql query that have group by .In pagination result i want to get count of all result to show in pagination .
  In query that donot have group by .I write a utility that create count of query from hql query like this </p>
  
  <pre><code>select u 
  from Personel u 
  where u.lastname='azizkhani'
  </code></pre>
  
  <p>i find main "from" keyword and substring hql and add count(*) and then make this query </p>
  
  <pre><code>select count(*) 
  from Personel u  
  where u.lastname='azizkhani'
  </code></pre>
  
  <p>when i have query that contain group by i can not do it  like this </p>
  
  <pre><code>select u.lastname,count(*) 
  from Personel u 
  group by u.lastname;
  </code></pre>
  
  <p>count of that query in sql is </p>
  
  <pre><code>select count(*) 
     from (
           select u.lastname,count(*) 
           from tbl_personel u 
           group  by u.lastname
      )
  </code></pre>
  
  <p>how can i generate group by query from hql ??</p>
  
  <p>I have GenericRepository that have method like this </p>
  
  <pre><code>public &lt;U&gt; PagingResult&lt;U&gt; getAllGrid(String hql,Map&lt;String, Object&gt; params,PagingRequest searchOption);
  </code></pre>
  
  <p>and developer call that like this</p>
  
  <pre><code>   String hqlQuery = " select e from Personel e where 1&lt;&gt;2 and e.lastname=:lastname";
  
      HashMap&lt;String, Object&gt; params = new HashMap&lt;String, Object&gt;();
      params.put("lastname", 'azizkhani');
  
  
      return getAllGrid(hqlQuery, params, new PagingRequest( 0/*page*/, 10 /*size*/) );
  </code></pre>
  
  <p>in GenericRepository i will return PagingResult object that have property</p>
  
  <pre><code>public class PagingResult&lt;T&gt; {
  
      private int totalElements;
  
      @JsonProperty("rows")
      private List&lt;T&gt; items;
  
      public PagingResult() {
  
      }
  
      public PagingResult(int totalElements, List&lt;T&gt; items) {
          super();
          this.totalElements = totalElements;
          this.items = items;
      }
  
  
      public int getTotalElements() {
          return totalElements;
      }
  
      public void setTotalElements(int totalElements) {
          this.totalElements = totalElements;
      }
  
  
      public List&lt;T&gt; getItems() {
          return items;
      }
  
      public void setItems(List&lt;T&gt; items) {
          this.items = items;
      }
  
  }
  </code></pre>
  
  <p>In GenericRepository i will execute two query ,first one for get 10 result and second for get totalRecords .Developer just send Hql .I will make hql for get totalcount . for query that dose not have "distinct" or "group by" i make hql .but when hql have "distinct" and "group by" i have problem . </p>
  
  <pre><code>public &lt;U&gt; PagingResult&lt;U&gt; getAllGrid(String hql, Map&lt;String, Object&gt; params, PagingRequest searchOption) {
          Session session = getSession();
          applyDafaultAuthorizeFilter(session);
  
  
          Query query = session.createQuery(hql);
          if (searchOption != null) {
              if (searchOption.getSize() &gt; 0) {
                  query.setFirstResult(searchOption.getPage() * searchOption.getSize());
                  query.setMaxResults(searchOption.getSize());
              }
          }
          if (params != null)
              HQLUtility.setQueryParameters(query, params);
  
          List&lt;U&gt; 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&lt;U&gt;(searchOption.getPage(), count, searchOption.getSize(), list);
          else
              return new PagingResult&lt;U&gt;(0, count, 0, list);
      }
  
  
     public static StringBuffer retriveCountQueryFromHql(StringBuffer jql) {
          if(jql.indexOf("order by")&gt;=0)
              jql.replace(jql.indexOf("order by"), jql.length(),"");
          String mainQuery = jql.toString();
  
          jql = new StringBuffer(jql.toString().replace('\t', ' '));
          int firstIndexPBas = jql.indexOf(")");
          int firstIndexPBaz = jql.lastIndexOf("(", firstIndexPBas);
          while (firstIndexPBas &gt; 0) {
              for (int i = firstIndexPBaz; i &lt; 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());
      }
  </code></pre>
  
  <p><strong>how can i solve this problem without change signature of my GenericRepository???</strong></p>
  
  <p>I think i have solution that convert hql to sql and create native query like this
      select count(*) from ( hql_to_sql)
   but i have two problem </p>
  
  <ol>
  <li>hql to sql dose not have api that support parameter</li>
  <li>hql to sql dose not have api that support hibernate filter</li>
  </ol>