Implement JPA query with COUNT and SUM

Question

I want to implement JPA query using this SQL query:

String hql = "SELECT DATE(date) AS Date, SUM(volume) AS amount, COUNT(*) AS number_of_transactions " + 
                " FROM " + PaymentTransactionsDailyFacts.class.getName() + " WHERE (date BETWEEN :start_date AND :end_date )" + 
                " GROUP BY DATE(date)";

I tried this:

String hql = "SELECT DATE(e.date) AS Date, SUM(e.volume) AS amount, COUNT(e.*) AS count " + 
                " FROM " + PaymentTransactionsDailyFacts.class.getName() + " e WHERE (date BETWEEN :start_date AND :end_date )" + 
                " GROUP BY DATE(date)";

But I get error:

expecting IDENT, found '*' near line 1, column 63 [SELECT DATE(e.date) AS Date, SUM(e.volume) AS amount, COUNT(e.*) AS count  FROM .......PaymentTransactionsDailyFacts e WHERE (date BETWEEN :start_date AND :end_date ) GROUP BY DATE(date)]

What is the proper way to implement this query into JPA?

Is there any answer you got? because this question's answers isn’t accepted yet, and you didn't respond too. I need about this information too please — Fran Na Jaya, Jan 20 '21 at 14:23

mdfst13 · Accepted Answer · 2019-02-08T15:43:29.010

JPQL doesn't know what * means. You'd instead just say e, not e.*.

String hql = "SELECT DATE(e.date) AS Date, SUM(e.volume) AS amount, COUNT(e) AS count " 
              + " FROM " + PaymentTransactionsDailyFacts.class.getName()
              + " e WHERE (date BETWEEN :start_date AND :end_date )" 
              + " GROUP BY DATE(date)";

This is because JPQL operates on the Java entities. Note that you are saying from a class name, not a table. Objects don't have wildcards saying any field in the class. Instead, you specify that the object itself is not null to say that a row is there.

The e.* syntax in SQL is essentially (although perhaps not literally) saying that there exists at least one non-null column on that row. Because it doesn't have any more direct way of saying that a row exists. But JPQL does have a way to say that an object exists. That's a basic Java not-null check. So in JPQL, you do it the Java way (albeit in JPQL syntax) rather than the SQL way.

Implement JPA query with COUNT and SUM

1 Answers1