如何在Hibernate本地查询中使用MySQL的赋值运算符(:=)？

Question

我正在使用Hibernate。我写了一些本地查询，因为我需要使用子查询语句。

查询看起来像这样：

SELECT sub.rownum FROM 
    (SELECT k.`news_master_id` AS id, @row := @row + 1 AS rownum 
        FROM keyword_news_list k 
        JOIN (SELECT @row := 0) r 
        WHERE k.`keyword_news_id` = :kid
    ORDER BY k.`news_master_id` ASC) AS sub 
WHERE sub.id  = :nid

当我按照以下方式运行此查询：

sessionFactory.getCurrentSession()
    .createSQLQuery(query)
    .setParameter("kid", kid)
    .setParameter("nid", nid)
    .uniqueResult();

出现了这个异常：

org.hibernate.QueryException: Space is not allowed after parameter prefix ':' ....

可能是因为使用了:=运算符。我发现了一些关于此的Hibernate问题。这个问题仍然没有解决。难道没有任何解决这个问题的方法吗？

- Sanghyun Lee

---

有什么可以帮助你的吗？http://dev.mysql.com/doc/refman/5.0/en/assignment-operators.html#operator_assign-value - ManuPK

@ManuPK 谢谢，但是没有帮助。我认为我的问题没有解决方案。我在stackoverflow上找到了一个类似的问题https://dev59.com/VU3Sa4cB1Zd3GeqPu2m5 但是没有好的解决方案。 - Sanghyun Lee

Answer 1

请注意，HHH-2697现在已经在Hibernate 4.1.3中得到修复。您现在可以使用反斜杠进行转义：

SELECT k.`news_master_id` AS id, @row \:= @row + 1 AS rownum 
    FROM keyword_news_list k 
    JOIN (SELECT @row \:= 0) r 
    WHERE k.`keyword_news_id` = :kid
ORDER BY k.`news_master_id` ASC

- Ondra Žižka

---

使用Hibernate 4.3.7时，只需要一个反斜杠，例如PG 9.3查询 select f( p_tst \:= 1 )（在Java字符串中可能需要两个反斜杠来转义） - Andreas Covidiot

这应该被标记为正确答案，它是有效的并且更加容易。 - Jignesh M. Khatri

运行良好，在Spring JPA存储库中使用@Query(value="<...>", nativeQuery=true)，我必须在引号内使用双反斜杠\\:=以避免编译时错误。 - RAM237

Answer 2

对于我们中无法升级到Hibernate 4.1.3版本的人来说，另一个解决方案是在查询中使用/*'*/:=/*'*/。Hibernate代码将把位于'之间的所有内容视为字符串（忽略它）。另一方面，MySQL将忽略块引用中的所有内容，并将整个表达式计算为赋值运算符。
我知道这很简单粗暴，但它可以完成工作，无需存储过程、拦截器等。

- Mariusz S.

Answer 3

你可以以稍微不同的方式实现这个功能。你需要用其他东西（比如 '|' 字符）替换 : 运算符，在拦截器中将 '|' 替换为 :。

这样，Hibernate 就不会试图将 : 作为参数，而是忽略它。

关于拦截器逻辑，您可以参考 hibernate 手册。

这在我使用 MySQL 5 时有效。

请记住，只有对 ':=' 和其他 MySQL 特定要求进行替换，不要尝试替换参数占位符中的 :（否则，Hibernate 将无法识别参数）。

- Anantha Sharma

---

Hibernate有多愚蠢啊？我可以想象那个程序员说：“不可能在SQL中出现冒号，没有机会！让我们这样做，没有转义机制！” - Pedro

抱歉，我有点生气，因为不得不绕过这个问题。上面的解决方案只适用于你只有一把锤子的情况。你也可以使用:来转义冒号，但在我的情况下，它破坏了每一个命名参数（它们就停止工作了）。 - Pedro

Answer 4

在遇到mysql的Hibernate异常:=运算符时，Stanislav提供了除拦截器之外的另一种解决此问题的选项。

- varghese

Answer 5

我更喜欢使用Spring JDBC并执行查询，而不是与Hibernate拦截器作斗争。

- leccionesonline

Answer 6

如果你把SQL文件与Java代码分开管理 - 那么这段代码就非常适合你。我已经进行了大量尝试以获得正确的转义符数量：

String sqlPattern = FileUtils.readFile(this.getClass(), /sql/my_query.sql");
sqlPattern = sqlPattern.replaceAll(":=", "\\\\:=");
Query query = entityManager.createNativeQuery(sqlPattern);

- Zon

Answer 7

我猜=后面不应该有空格，运算符应该写成=:（没有任何空格）

- Rocky