如何在休眠本机查询中使用 MySQL 分配运算符 (:=)? [英] How can I use MySQL assign operator(:=) in hibernate native query?

问题描述

我正在使用 Hibernate.我写了一些原生查询，因为我需要使用 sub select 语句.

I'm using Hibernate. I wrote some native query because I need to use sub select statement.

查询如下所示:

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

当我像这样运行这个查询时:

When I run this query like this:

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

出现这个异常:

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

这可能是因为 := 运算符.我发现了一些关于此的 Hibernate 问题.这个问题仍然是开放的.这个问题没有解决办法吗?

This might because of := operator. I found some Hibernate issue about this. This issue is still open. Isn't there any solution for this problem?

推荐答案

你可以用稍微不同的方式实现这个.你需要用别的东西(比如 '|' char )替换 : 操作符，并在你的拦截器中替换'|'与:.

you can implement this is a slightly different way.. you need to replace the : operator with something else (say '|' char ) and in your interceptor replace the '|' with the : .

这样 hibernate 不会试图认为 : 是一个参数，但会忽略它

this way hibernate will not try to think the : is a param but will ignore it

拦截器逻辑可以参考hibernate手册

这对我使用 MySQL 5 有用.

This has worked for me using MySQL 5.

请记住，这种替换 : 必须仅针对 ':=' 和其他 MySQL 特定要求.不要尝试替换 : 作为参数占位符.(此时休眠将无法识别参数)

remember, this replacing of : must be only done to ':=' and other MySQL specific requirments.. don't try to replace the : for the param-placeholders. (hibernate will not be able to identify the params then)

这篇关于如何在休眠本机查询中使用 MySQL 分配运算符 (:=)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！