java - Hibernate createSqlQuery 设置变量

Question

我有一个像这样的 mysql 查询来获取行号。

set @rank = 0; 
select @rank := @rank + 1 as rank,id,name from test

在 hibernate 3 中 ':=' 是一个问题。

session.createSQLQuery("set @rank = 0; 
                        select @rank := @rank + 1 as rank,id,name from test");

我切换到 hibernate 3到4并使用转义字符。

session.createSQLQuery("set @rank = 0; 
                        select @rank \\:= @rank + 1 as rank,id,name from test");

现在又发生了一个异常。它给出了异常(exception):

"Parameter rank does not exist as a named parameter" 

我试过这个:

 session.createSQLQuery("select :rank \\:= :rank + 1 as rank,
                         ID,NAME,SURNAME from test").setInteger("rank", 0)

这次它给出了这个异常:

You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near ':= 0 + 1 as rank,id,
name from test' at line 1

知道如何解决这个问题吗？

Answer 1

使用 doReturningWork() 时，您可以访问普通 JDBC 连接。然后你应该将 SQL 分成 2 个执行(设置和选择):

final Object queryResultObject = session.doReturningWork(new ReturningWork<ReturnObjectType>() {

  @Override
  public ReturnObjectType execute(Connection connection) throws SQLException {

    final Statement statement = connection.createStatement();
    statement.execute("set @rank = 0");

    final ResultSet resultSet = statement.executeQuery(
                                      "select @rank := @rank + 1 as rank,id,name
                                       from test");

    ReturnObjectType result = /* transform ResultSet into return value */

    return result;
  }
});