Get native SQL from QueryDSL JPA (JPAQuery or JPQLQuery)

Question

I have a requirement to count the number of group by records for pagination purpose. For example:

select count(*) from ( 
    select name, count(id) from customer
    group by name
);

However it couldn't be achieved via QueryDSL due to the limitation of JPA where JPQL doesn't allows to select count from sub query.

Is it possible to get the native SQL from QueryDSL JPAQuery or JPQLQuery? My plan is to construct and execute the select count native SQL statement via EntityManager.

String subQueryNativeSQL = "..."; // native SQL from QueryDSL
Query q = em.createNativeQuery("select count(*) from (" + subQueryNativeSQL + ")");
long count = (long) q.getSingleResult();

Of course JPQL allows you to select COUNT in a subquery, as per the JPA spec. simple_select_expression::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable — Neil Stockton
Hi Neil, May be you can show me on how to use JPQL to construct the SQL statement I posted above? — kenn3th
maybe you can post your ENTITIES and what you have tried with JPQL first. Because JPQL clearly depends on classes/fields and you post none of them — Neil Stockton
The title (what I'm looking for) has no relation with the actual question. Pls update. — anat0lius

bsiamionau bsiamionau · Accepted Answer · 2017-02-03T11:16:55

Both JPAQuery and JPQLQuery implement Projectable interface and implement count() method - and you ain't need subqueries

Get native SQL from QueryDSL JPA (JPAQuery or JPQLQuery)

1 Answers