EclipseLink JPQL: Generic COUNT statement for any statement

Question

I have an application which hit very different queries (on different resources) written in JPQL.

For a lot of this queries I need to know the total of results (count) as I'm not applying any LIMIT/OFFSET

Because the nature of this queries is very different, I'm unable to build a parser which extract the FROM clause and it applies on a SELECT COUNT query.

Let's see an example:

Query 1: SELECT a FROM People a WHERE name = 'John'
Query 2: SELECT DISTINCT(o.category.id) FROM Company o

Trying to use a standard pattern, a count query will be SELECT (COUNT) z FROM (...) z and inside the missing parte I would put the full query as the 2 before

Query 1: SELECT COUNT(z) FROM (SELECT a FROM People a WHERE name = 'John') z Query 2: SELECT COUNT(z) FROM (SELECT DISTINCT(o.category.id) FROM Company o) z

From the documentation I can see that Sub-selects in FROM clause are supported: https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause

This is the sample code I made

try {
            String subSelect = "SELECT a FROM People a WHERE name = 'John'";
            String statement = "SELECT COUNT(z) FROM (" + subSelect + ") z";
            TypedQuery<Long> createQuery = em.createQuery(statement, Long.class);
            System.out.println(createQuery.getSingleResult().longValue());
        } finally {
            em.close();
        }

But if I try to execute the query (I'm using EclipseLink 2.7.0) I'm getting this error:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Problem compiling [SELECT COUNT(z) FROM (SELECT a FROM People a WHERE name = 'John') z]. 
[21, 67] '(SELECT a FROM People a WHERE name = 'John') z' cannot be the first declaration of the FROM clause.
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1743)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1764)

Even if sub-select seems to be supported, the cannot be the first declaration of the FROM clause. error is clear.

Am I doing something wrong?

The end goal of mine is trying to have a standard COUNT query which I can use for any kind of statement, simplying using COUNT(*) FROM (..statement..), in this way the implementation is very generic.

Or, if there are methods which can allow to achieve the same goal, can be useful as well.

EDIT

Also tried with COUNT(0)' as suggested, like this:SELECT COUNT(0) FROM (SELECT DISTINCT(o.category.id) FROM Company o)`

Exception Description: Syntax error parsing [SELECT COUNT(0) FROM (SELECT DISTINCT(o.category.id) FROM Company o)]. 
[68, 68] An identification variable must be provided for a range variable declaration.

Can you please explain why querying the size of the resultant List is not good enough? http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html#getResultList() — Scary Wombat, Oct 18 '17 at 06:59
@pirho Tried, but I'm still getting an error (see edit on main post) — Deviling Master, Oct 18 '17 at 11:01
@ScaryWombat For two reasons: 1- I want to know the TOTAL of the results, besides eventuale Limit and Offset applied to the query. 2- Hitting getResultsList on a query w/o limit/offset can retrieve a lot (thousands) of record, while I only need to know the count value — Deviling Master, Oct 18 '17 at 11:01
@DevilingMaster yes, think that jpql requires to pointing to field anyway so if `z` would otherwise work guess it should be `z.id` or so... — pirho, Oct 18 '17 at 11:36
@pirho also tried with `z.id`, the problem here is the `cannot be the first declaration of the FROM clause` error, it is like with JPQL you cannot have only a subSelect FROM, but only joined with another one — Deviling Master, Oct 18 '17 at 11:43
We implemented this functionality by creating a separate path for count queries when building the query rather than trying to wrap all the queries in a generic count structure as you are. If you are going through this work, you might be better off processing the query JPQL to get the SQL, and then wrap it in a native query for execution. see https://stackoverflow.com/a/43933889/496099 on how to get the SQL from the query before execution. — Chris, Oct 18 '17 at 14:12

Deviling Master · Answer 1 · 2017-10-19T06:08:06.363

With suggestion from @chris I came up with the following solution

    /**
     * https://stackoverflow.com/a/43933889/1013317
     */
    private static String queryToSqlString(EntityManager em, Query query) {
        Session session = em.unwrap(JpaEntityManager.class).getActiveSession();
        DatabaseQuery databaseQuery = query.unwrap(EJBQueryImpl.class).getDatabaseQuery();
        databaseQuery.prepareCall(session, new DatabaseRecord());
        Record r = databaseQuery.getTranslationRow();

        // Query with parameters
        return databaseQuery.getTranslatedSQLString(session, r);
    }

    /**
     * Overload, see {@link #queryToSqlString(EntityManager, Query)}
     */
    private static String queryToSqlString(EntityManager em, String statement) {
        return queryToSqlString(em, em.createQuery(statement));
    }

    /**
     * Count the amount of results for a given statement
     */
    private static long count(EntityManager em, Query query) {
        String countStatement = String.format("SELECT COUNT(*) FROM (%s) AS t", queryToSqlString(em, query));
        Query createNativeQuery = em.createNativeQuery(countStatement);
        return (long) createNativeQuery.getSingleResult();
    }

    /**
     * Overload, see {@link #count(EntityManager, Query)}
     */
    private static long count(EntityManager em, String statement) {
        return count(em, em.createQuery(statement));
    }

I tried with a couple of queries (simple queries, join queries, ...) and it seems to works very fine. Also, eventual LIMIT or OFFSET applied to the query object are ignored when SQL string is generated, so I don't even need to remove that from generated statement.

The queryToSqlString can also be very handy to log native statement that are submitted to the DBMS (if you do not want to enable the full EclipseLink logging)

EclipseLink JPQL: Generic COUNT statement for any statement

1 Answers1