Wrap criteria query with count JPA 2.0 with hibernate vendor

Question

Is there a way, to wrap a CriteriaQuery with a count query? My idea was that to create a function that create a count query from any given query.

For example in plane sql:

SELECT
  item_type,
  count(*) AS lol
FROM inventory_movements
WHERE movement_date_time BETWEEN '2017-05-08 12:00:00' AND '2017-05-08 13:00:00'
GROUP BY item_type

and i want to create something like this(i know in this specific query a count distinct would solve the problem, but i need a generic solution, i want to use it for paging in non jpa managed return type queries):

SELECT count(*)
FROM (
       SELECT
         item_type,
         count(*) AS rand_
       FROM inventory_movements
       WHERE movement_date_time BETWEEN '2017-05-08 12:00:00' AND '2017-05-08 13:00:00'
       GROUP BY item_type
     ) AS sub;

CriteriaQuery is not instance of Expression so it's cant be use in criteriaBuilder.count();

Then i thought i can cast it to a Subquery, since both CriteriaQuery and Subquery implements the same Interfaces, but Subquery also implements Expression.

But this was not work as i think. This is my current non working code right now:

public <T>TypedQuery<Long> getCountQuery(CriteriaQuery<T> origQuery) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> query = cb.createQuery(Long.class);
    Subquery<T> subQuery = (Subquery<T>) origQuery;

    query.select(cb.count(subQuery));
    return entityManager.createQuery(query);
}

I get the following exception during run time:

java.lang.ClassCastException: org.hibernate.query.criteria.internal.CriteriaQueryImpl cannot be cast to javax.persistence.criteria.Subquery

Is this even possible in jpa/hibernate?

You create a subquery in JPA Criteria using "query.subquery(...)", not by casting something! — Neil Stockton, May 16 '17 at 06:06
So what you're basically saying is _I know the API is like this but I want to bastardise my queries to tie them to the internal implementation of the provider I'm using, in the hope of saving some lines of code_. Yes? — Neil Stockton, May 16 '17 at 06:24
No, i don't want to bastardise, that's why i ask is there a normal way to doing it, before a create tons of code duplication — Syngularity, May 16 '17 at 06:35
You put your code that generates the query in a method, and pass in the query that it will be applied to (whether subquery or query). How else? Or simply write the query API code to generate all queries you need and then look for common code and modularise it! — Neil Stockton, May 16 '17 at 06:37
http://stackoverflow.com/questions/5423937/how-do-i-count-the-number-of-rows-returned-by-subquery — Jay Smith, May 16 '17 at 10:17

wzy · Accepted Answer · 2019-11-21T07:07:26.437

@PersistenceContext
private EntityManager em;

...
...
public Page<T1> findByParameters(P p) {
        CriteriaQuery<T1> criteriaQuery = (CriteriaQuery<T1>) getEm().getCriteriaBuilder().createQuery(typeToken1.getRawType());           
        Root<T2> root = (Root<T2>) criteriaQuery.from(typeToken2.getRawType());
        criteriaQuery = SpecificationBuilder.of().buildCriteriaQuery(p, root, criteriaQuery, criteriaBuilder);

        TypedQuery<T1> query = getEm().createQuery(criteriaQuery);
        query.setFirstResult(pageRequest.getPageNumber() * pageRequest.getPageSize());
        query.setMaxResults(pageRequest.getPageSize());

        Integer totoal = getTotal(criteriaQuery, (CriteriaQueryTypeQueryAdapter) query);
        PageImpl<T1> page1 = new PageImpl<>(query.getResultList(), pageRequest, totoal);
         return page1;
}

private Integer getTotal(CriteriaQuery<T1> criteriaQuery, CriteriaQueryTypeQueryAdapter query) throws NoSuchFieldException, IllegalAccessException {
        String jql = getEm().createQuery(criteriaQuery.orderBy()).unwrap(Query.class).getQueryString();
        Field f = CriteriaQueryTypeQueryAdapter.class.getDeclaredField("jpqlQuery");
        f.setAccessible(true);
        QueryImpl jpqlQuery = (QueryImpl) f.get(query);
        Map<String, TypedValue> stringTypedValueMap = jpqlQuery.getQueryParameters().getNamedParameters();
        String sql = getSql(jql);
        sql = String.format("select count(*) from (%1$s) t", sql);
        NativeQuery nativeQuery = (NativeQuery) getEm().createNativeQuery(sql);
        int index = 1;
        for (Iterator<Map.Entry<String, TypedValue>> iterator = stringTypedValueMap.entrySet().iterator(); iterator.hasNext(); ) {
            Map.Entry<String, TypedValue> next = iterator.next();
            nativeQuery.setParameter(index, next.getValue().getValue());
            index++;
        }
        return (Integer) nativeQuery.getSingleResult();
    }

score 0 · Answer 2 · answered Feb 20 '18 at 07:04

default Long countByQueryEvent(QueryEvent event) {
    EntityManager entityManager = event.getEntityManager();
    Query query = event.getQuery();

    org.hibernate.Query hqlQuery = query.unwrap(HibernateQuery.class).getHibernateQuery();
    ASTQueryTranslatorFactory astQueryTranslatorFactory = new ASTQueryTranslatorFactory();
    HibernateEntityManagerFactory hibernateEntityManagerFactory = (HibernateEntityManagerFactory) entityManager.getEntityManagerFactory();
    SessionFactoryImplementor sessionFactory = (SessionFactoryImplementor) hibernateEntityManagerFactory.getSessionFactory();
    QueryTranslator queryTranslator = astQueryTranslatorFactory.createQueryTranslator("", hqlQuery.getQueryString(), EMPTY_MAP, sessionFactory, null);
    queryTranslator.compile(EMPTY_MAP, false);
    String queryString = "select count(*) from (" + queryTranslator.getSQLString() + ") t";
    org.hibernate.Query countQuery = entityManager.createNativeQuery(queryString).unwrap(org.hibernate.Query.class);
    ParameterTranslations parameterTranslations = queryTranslator.getParameterTranslations();

    //is there any way to get all parameters with values? 
    // all parameters!!! not only provided by setParameter() method in Query
    Map params; 
    try {
        Field f = AbstractQueryImpl.class.getDeclaredField("namedParameters");
        f.setAccessible(true);
        params = (Map) f.get(hqlQuery);
    } catch (NoSuchFieldException e) {
        e.printStackTrace();
        return null;
    } catch (IllegalAccessException e) {
        e.printStackTrace();
        return null;
    }

    for (Object param : parameterTranslations.getNamedParameterNames()) {
        String paramString = (String) param;
        final int[] positions =
                parameterTranslations.getNamedParameterSqlLocations(paramString);
        for (final int p : positions) {
            countQuery.setParameter(p, ((TypedValue)params.get(paramString)).getValue(),
                    ((TypedValue)params.get(paramString)).getType());
        }
    }

    Object result = countQuery.uniqueResult();
    if (result instanceof BigDecimal) {
        return ((BigDecimal) result).longValue();
    } else if (result instanceof BigInteger) {
        return ((BigInteger) result).longValue();
    } else {
        throw new IllegalArgumentException("JDBC driver returned unsupported resultType from count.");
    }
}

Wrap criteria query with count JPA 2.0 with hibernate vendor

2 Answers2