如何在Eclipselink中按子字符串分组？ [英] How to do group by substring in Eclipselink?

问题描述

我正在尝试按商店国家/地区对商店进行分组，以查看每个国家/地区有多少家商店。商店国家/地区恰好是商店代码的前2个字符。这意味着如果商店代码为 US000010，则国家/地区为 US

I'm trying to group the store by store country and see how many stores there are in each country. The store country happens to be the first 2 characters of the store's code. That means if the store code is "US000010", the country is "US"

我的实体对象：

...
public class Store {
    @column(name = "code")
    private String code;
    ...    
}

我的JPQL是：

  SELECT substring(s.code, 0, 2),
         count(s) 
    FROM Store s 
GROUP BY substring(s.code, 0, 2)

这总是让我 ORA-00979：不是GROUP BY表达式，我怀疑是由于上面的JPQL转换为

This keeps throwing me ORA-00979: not a GROUP BY expression, which I suspect is due to the JPQL above that is converted to

SELECT SUBSTR(CODE, ?, ?), COUNT(CODE) FROM Store GROUP BY SUBSTR(CODE, ?, ?)

不包括创建视图或使用本机查询之类的其他替代方法，我是否可以使用JPQL在Eclipselink中按子字符串进行分组？

Excluding other alternatives like creating a view or using native query, is there any way I perform a group by substring in Eclipselink using JPQL?

推荐答案

我最近在通过 TO_CHAR 函数分组时遇到了类似的问题。我发现的解决方案特定于ExclipseLink，它只是在查询上设置提示： query.setHint（QueryHints.BIND_PARAMETERS，HintValues.FALSE）; 。

I recently had a similar problem grouping by a TO_CHAR function. The solution I found is ExclipseLink specific, and it was just to set a hint on the query: query.setHint(QueryHints.BIND_PARAMETERS, HintValues.FALSE);.

在执行查询之前放置此代码，它将不使用参数，而是直接替换发送到JDBC驱动程序的SQL字符串上的值

Put this code before executing the query and it will not use parameters, but replace the values directly on the SQL string sent to the JDBC driver

这篇关于如何在Eclipselink中按子字符串分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！