SQL to JPA @NamedQuery Conversion

Question

I'm looking for an intelligent way to convert this SQL statement into @NamedQuery --if there is a way at all?

SELECT MONTH(dateField), sum(value) FROM mydb.records where status ='paid' group by MONTH(dateField) order by MONTH(dateField);

I have a JPA @Entity called Record (Hibernate). This details all invoices in the system that are created on daily basis. There will be many entries per month. Each record will have a status of paid, overdue, value, and lots of other info such as name and address of customer and so on etc.

The above statement basically summarises all the data on a month by month basis and sums the value of all paid invoices per month giveing a summary of all invoices paid in January, all paid in Feb and so on.....The result looks somethings like:

datefield    value
1            4500
2            5500
3            5669

The only way I can think of doing this using JPA @NamedQuery is to select all records in the table that are of status 'pai'd and then use my Java code to do the sorting, ordering and addition in a rather slow and ugly fashion! Is there a clever way I can do this with @NamedQuery?

Thanks

Answer 1

MONTH() is not a standard JPQL function defined in the JPA spec, however there is an alternative.

Make a view in your database that leverages your databases month function.

create view MONTLY_REVENUE as
    SELECT MONTH(dateField), 
       sum(value) 
    FROM mydb.records 
    where status ='paid' 
    group by MONTH(dateField) 
    order by MONTH(dateField);

Then just create this entity as you would from any other table with JPA. You can select from the view but will not be able to save and update it, however since your using the aggregates its not like you would anyway.