mysql - Spring JPA 查询涉及 COUNT() 和 LOWER()

Question

我对 Spring JPA/JPQL 非常陌生，但尝试复制以下 MySQL 查询，但没有取得太大成功。我们无法克服看起来像语法错误的问题，该错误看起来是尝试复合使用 COUNT() 和 LOWER() 的函数。

MySQL(可以运行)是:

select lower(sdetail_cvalue) as stringValue, 
count(lower(sdetail_cvalue)) as stringValueCount 
from <someTable>
where sdetail_cfield not like <someValue>
and sdetail_cfield not like <someOtherValue>
and sdetail_cfield not like <someOtherOtherValue>
group by stringValue
order by stringValueCount desc

我正在尝试的相应 JPQL 是

SELECT new <searchResult> 
(lower(sd.searchText) as searchText, 
COUNT(lower(sd.searchText)) as searchTextOccurrenceCount) 
FROM <someTable> sd 
WHERE sd.searchType not like <someValue> 
AND sd.searchType not like <someOtherValue> 
AND sd.searchType not like <someOtherOtherValue> 
GROUP BY searchText 
ORDER BY searchTextOccurrenceCount DESC

但执行时出现以下错误消息

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 128 [SELECT new <REDACTED> (lower(sd.searchText) as searchText, COUNT(lower(sd.searchText)) as searchTextOccurrenceCount) FROM <someTable> sd WHERE sd.searchType not like <someValue> AND sd.searchType not like <someOtherValue> AND sd.searchType not like <someOtherOtherValue> GROUP BY searchText ORDER BY searchTextOccurrenceCount DESC]

我已经编辑了上面的一些内容，但列号(即 128)指的是“lower”和“sd”之间的“(”。

我们尝试了各种方法来解构查询以查明问题，这似乎是我们使用的是复合 COUNT(LOWER()) 构造。有谁有使用 JPQL 成功实现这样的事情的例子吗？...提前致谢。

Answer 1

您是否考虑过将 nativeQuery = true 添加到 @Query 注释中？