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)

Answer 1

You can use this for :-

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

Answer 2

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;