generate_series function in Hibernate

Question

I have a column total in the table Inv and I want to extract total amount per month in hibernate using generate_series:

@Query(value = "SELECT _month," +
            "sum(inv.total) AS total" +
            "FROM FUNCTION('generate_series', 1, 12, 1) as _month" +
            "join InvSC as sc on FUNCTION('extract', month, sc.timestamp) = _month" +
            "JOIN Inv as inv on sc.invoiceNumber = inv.invoiceNumber" +
            "GROUP BY _month")
    List<String> totalAmount();

I have syntax error

Caused by: org.hibernate.query.sqm.ParsingException: line 1:58 mismatched input 'FUNCTION' expecting {<EOF>, ',', FROM, GROUP, ORDER, WHERE}
    at org.hibernate.query.hql.internal.StandardHqlTranslator$1.syntaxError(StandardHqlTranslator.java:46) ~[hibernate-core-6.1.7.Final.jar:6.1.7.Final]

How can I write the query using FROM (FUNCTION ('generate_series'))?

score 1 · Answer 1 · answered Jun 25 '23 at 16:23

1

Since Hibernate 6.2 introduced support of common table expressions, I believe, you can call table functions using following approach:

I. create dummy table with single row and corresponding dummy entity

create table dual(dummy varchar(1));
insert into dual(dummy) values('x');

@Entity(name = "dual")
@Table(name = "dual")
public class Dual {

    @Id
    @Column(name = "dummy")
    protected String dummy;
    
}

II. Now, you are able to write something like in JPQL:

WITH mon AS MATERIALIZED (
   SELECT FUNCTION('generate_series', 1, 12, 1) AS month_ FROM dual
)
SELECT mon.month_, sum(inv.total) AS total
FROM mon, InvSC as sc, Inv as inv
WHERE sc.invoiceNumber = inv.invoiceNumber
AND mon.month_ = FUNCTION('extract', 'month', sc.timestamp)
GROUP BY mon.month_

answered Jun 25 '23 at 16:23

Andrey B. Panfilov

4,324
2
12
18

Is it possible to achieve the same functionality using HQL? – Programmer2B Jun 25 '23 at 16:25
@Programmer2B have you tried what I have suggested? – Andrey B. Panfilov Jun 25 '23 at 16:33
Yes, it works with a materialized view. I was just wondering whether is it possible to do it using HQL with @Query annotation without creating a new Entity. – Programmer2B Jun 25 '23 at 16:35
That is HQL syntax – Andrey B. Panfilov Jun 25 '23 at 16:37

generate_series function in Hibernate

1 Answers1