使用JPA的自定义SQL语句进行查询,使用:=进行变量赋值时,报org.hibernate.QueryException: Space is not allowed after parameter prefix ':'异常。
SELECT
t.rn
FROM
(
SELECT
sentry_code,
( SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) r ) AS rn
FROM
t_integral
WHERE
sentry_code LIKE ?1
ORDER BY
all_amount DESC
) t
WHERE
t.sentry_code = ?2;
解决方案:
1. 通过添加反斜杠 \\ 来转义 : ,不然Hibernate不识别赋值符号 :=(亲测可用) 。
2. 使用/*/:=/*/也可以解决,(没有测试过是否可行)
SELECT
t.rn
FROM
(
SELECT
sentry_code,
(SELECT @rownum \\:= @rownum + 1 FROM (SELECT @rownum \\:= 0) r) AS rn
FROM
t_integral
ORDER BY
all_amount DESC
) t
WHERE
t.sentry_code =?1
对应的Java代码如下:
@Query(value = "SELECT t.rn FROM (SELECT sentry_code,(SELECT @rownum \\:= @rownum+1 FROM (SELECT @rownum \\:= 0) r) AS rn FROM t_integral ORDER BY all_amount DESC) t WHERE t.sentry_code=?1", nativeQuery = true)
int getMyIntegralAllPostSort(String sentryCode);