spring - 如何在不使用本机查询的情况下在休眠 5.0.1 中实现 listagg 功能
问题描述
我在 spring boot 1.5 项目中工作。我实现了 listagg 但我得到“Unexcepted token error WITHIN” 请帮助我在不使用本机查询的情况下在休眠中实现 listagg。
Output:
jobname Empno
AC_ACCOUNT 206
AC_MGR 205
AD_ASST 200
AD_PRES 100
AD_VP 101,102
FI_ACCOUNT 110 ,113 ,111 ,109 ,112
select jobname, listagg(empno, ',') within group (order by empno) empno
from emp group by jobname;
-
unexpected token: within","stack_trace":"antlr.NoViableAltException: unexpected token: within
at org.hibernate.hql.internal.antlr.HqlBaseParser.aliasedExpression(HqlBaseParser.java:2365)
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectedPropertiesList(HqlBaseParser.java:1379)
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectClause(HqlBaseParser.java:1282)
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1022)
at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:730)
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:323)
at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:186)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:279)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1907)
at
解决方案
要在休眠查询中使用 listagg:
首先通过扩展 Oracle10gDialect(如果您使用 Oracle)或 MySQLDialect(对于 MySQL)在 Hibernate Dialect 中注册 listagg 函数
public class CustomOracle10gDialect extends Oracle10gDialect { public CustomOracle10gDialect() { super(); registerFunction("LISTAGG", new SQLFunctionTemplate(StandardBasicTypes.STRING,"LISTAGG(?1,',') WITHIN GROUP(ORDER BY ?1)")); } }
更新您的配置文件以使用自定义方言。spring.jpa.database-platform=com.abc.CustomOracle10gDialect 或 com.abc.CustomOracle10gDialect
使用 LISTAGG() 示例 - select c.id, listagg(firstName) from Contacts c where GROUP BY c.id
推荐阅读
- javascript - 计算新数组映射中的项目
- php - Laravel Lighthouse GraphQL 重命名关系突变
- visual-studio-mac - Missing 'Manage User Secrets' context menu item on Visual Studio for Mac
- html - 如何根据设备创建到 AppStore 或 PlayStore 的自动重定向?
- automated-tests - Leanft 无法识别终端仿真器屏幕对象
- node.js - e2e - Error code: 135 - Could not find chromedriver at null. Run 'webdriver-manager update' to download binaries
- react-native - React Native 视图缩放
- angular - Angular-material-table :- Getting error for mat-table
- algorithm - 找到一组节点,其移除最大程度地减少了根的传递闭包
- python - if+or fails even though inverse condition works