Does hibernate support count(*) over()

Question

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)

Well you can always use native sql. I'm assuming you want to avoid that? — Daniel Kaplan, Feb 07 '14 at 00:03
Yes, i'm trying to avoid native sql and stick to HQL and detached criteria. Using native SQL defeats the purpose of using hibernate. — user2596860, Feb 07 '14 at 00:26
Simply use native SQL with or without hibernate. Analytic functions are standard and they are supported by most of modern RDBMS implementations. Actually it is always better to use plain SQL instead of "object oriented queries" because they are more portable between tools, easy to test, no generated obfuscation and they are faster. — jbaliuka, Feb 13 '14 at 18:41

score 3 · Answer 1 · answered Oct 17 '16 at 18:13

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;

score 1 · Answer 2 · edited Sep 19 '17 at 18:02

1

You can use this for :-

return (Number) session.createCriteria("Book").setProjection(Projections.rowCount()).uniqueResult();

edited Sep 19 '17 at 18:02

Gilad Green

36,708
7
61
95

answered Feb 23 '14 at 13:57

Vijay

4,694
1
30
38

Does hibernate support count(*) over()

2 Answers2

Linked