英文:
generate_series function in Hibernate
问题
我有一个表格 Inv 里的列 total，我想要使用 Hibernate 提取每个月的总金额，使用 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();
我遇到了语法错误：
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]
我应该如何使用 FROM (FUNCTION ('generate_series')) 编写查询？
英文:
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'))?
答案1
得分: 1
I. 创建带有单行和相应虚拟实体的虚拟表：
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. 现在，您可以在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_
英文:
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_
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论