CODEWITHSUNDEEP
javajpajpa-2.0jpa-2.1
Peter Penzov
Peter Penzov

Reputation: 1588

Implement JPA query with COUNT and SUM

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?

Upvotes: 0

Views: 1973

Answers (1)

mdfst13
mdfst13

Reputation: 900

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.

See also

Upvotes: 1

Related Questions