报异常:
### Cause: java.sql.SQLSyntaxErrorException: ORA-00979: 不是 GROUP BY 表达式
SELECT
SUBSTR( region_code, 1,#{ queryMap.groupCodeLength, jdbcType = INTEGER } ) AS "region_code",
count( CASE WHEN TYPE = 1 THEN 0 END ) AS "like",
count( CASE WHEN TYPE = 2 THEN 0 END ) AS "roast"
FROM
t_pub_sentiment
WHERE
1 = 1
GROUP BY
SUBSTR(region_code,1,#{ queryMap.groupCodeLength,jdbcType = INTEGER })
更改后:
SELECT
SUBSTR( region_code, 1, $ { queryMap.groupCodeLength } ) AS "region_code",
count( CASE WHEN TYPE = 1 THEN 0 END ) AS "like",
count( CASE WHEN TYPE = 2 THEN 0 END ) AS "roast"
FROM
t_pub_sentiment
WHERE
1 = 1
GROUP BY
SUBSTR( region_code, 1, $ { queryMap.groupCodeLength } )
原因:#{} 和 ${} 在预编译中的处理是不一样的。#{} 在预处理时,会把参数部分用一个占位符 ? 代替。而 ${} 则只是简单的字符串替换。 ${}有sql注入的风险,需谨慎使用。