how can get count of rows in hibernate when hql have group by?

Question

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

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

i find main "from" keyword and substring hql and add count(*) and then make this query

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

when i have query that contain group by i can not do it like this

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

count of that query in sql is

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

how can i generate group by query from hql ??

I have GenericRepository that have method like this

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

and developer call that like this

   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*/) );

in GenericRepository i will return PagingResult object that have property

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;
    }

}

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 .

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('\t', ' '));
        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());
    }

how can i solve this problem without change signature of my GenericRepository???

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

1. hql to sql dose not have api that support parameter
2. hql to sql dose not have api that support hibernate filter

Hollie

Answer 1

Why not replace the group by with a count(distinct)?

So instead of

select u from tbl_personel u group by u.lastname

you do

select count(distinct u.lastname) from tbl_personel u

Froward

Answer 2

If this is really your HQL query that you need to paginate:

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

and you need the total count for this query, then you can only run an SQL query since Hibernate JPQL does not support derived tables.

So, just run the native SQL query:

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

That's it! You don't have to translate every SQL query to HQL. Native SQL is a Magic Wand, and a paginated table is much more suitable for a DTO projection than for a HQL query which is meant mostly for fetching entities.

Parbuckle

Answer 3

I run into the same problem, but my HQL-statement has multiple columns in the GROUP-BY clause:

select u.firstname, u.lastname
from Personel u 
group by u.firstname, u.lastname;

I need to count the resulting rows for pagination. The approach from Christian Beikov didn't work, because I can't use multiple columns in a count(distinct)- function.

I solved the problem with nested aggregate functions:

select sum(count(distinct u.lastname))
from Personel u 
group by u.firstname, u.lastname;

'u.lastname' has to be an NOT NULL column.

This statement works in HQL with an Oracle-DB.

Defiance

Answer 4

I came across the same issue:

• Hibernate does not allow subselects which are required when using GROUP BY
• Hibernate DISTINCT operator only allows a single param

Workaround

Using COUNT(DISTINCT a, b, c, d) and ommiting the GROUP BY clause.

We must register our own function that mimics it.I registered it in my configuration object, since i did not want to extend the Dialect class and registering it as a Contributer did not work, and this was the easier way.

public class HqlFunctions
{
    /**
     * Adds all functions to this configuration
     *
     * @param configuration
     */
    public static void addSqlFunctions(final Configuration configuration)
    {
        configuration.addSqlFunction(HqlFunctions.NAME_COUNTDISTINCT, HqlFunctions.createFunctionCountDistinct());
    }

    /*
     * COUNT(DISTINCT(?))
     */

    public static String NAME_COUNTDISTINCT = "COUNT_DISTINCT";

    /**
     * Creates a new SQLFunction representing COUNT(DISTINCT ?) with variable
     * arguments.
     *
     * @return
     */
    public static VarArgsSQLFunction createFunctionCountDistinct()
    {
        return new VarArgsSQLFunction(LongType.INSTANCE, "COUNT(DISTINCT ", ",", ")");
    }

    /**
     * Creates a new COUNT DISTINCT query with given non-null / empty values.
     *
     * @param values
     *
     * @return
     */
    public static String createCountDistinctHQL(final String... values)
    {
        final StringJoiner sj = new StringJoiner(", ", "(", ")").setEmptyValue("(1)");

        // add each non-null / empty value to joiner
        if (values != null)
            Arrays.stream(values).filter(v -> v != null && !v.isEmpty()).forEach(v -> sj.add(v));

        return HqlFunctions.NAME_COUNTDISTINCT + sj.toString();
    }
}

The createCountDistinctHQL(String ... values) allows to use it inside programmatic HQL queries like:

SELECT 
    COUNT_DISTINCT(o.time.year, o.time.month, o.product.brand)
FROM
    Order AS o

Instead of not supported

SELECT COUNT(*) 
FROM
    (SELECT 
        o.time.year, o.time.month, o.product.brand
    FROM
        Order AS o
    GROUP BY
        o.time.year,
        o.time.month,
        o.time.brand)

Springy

Answer 5

Assign the output of your query to a list..

Query query = getEntityManager().createQuery("select u.lastname,count(*) from Personel u group by u.lastname;");

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

And your count is here..

Integer.toString(list.size())

Schrimsher

If result count is 100000 record then this solution is ok and have good performance ? – Hollie

You need only count or you are showing the results of the query somewhere? i have not tried it but change the above code in this style and see the results. query.getMaxResults(); – Schrimsher

i show pagination result .for example pagesize=10 and i show pagenumber=2 and all result (by group) is 100000 – Hollie

This is highly inefficient – Looper

Answer 6

1.Write your HQL:

String hql = "select count(*) from (select u.lastname,count(*) from tbl_personel u group  by u.lastname)";

2.Create a Query from the Session:

Query query = session.createQuery(hql);

3.Execute the query:

List listResult = query.list();

Radius

This is not hql query. – Radius

refer codejava.net/frameworks/hibernate/… – Radius

hql dose not suppert hql from in subselect .check again – Hollie