Hibernate createSqlQuery setting variable

Question

I have a mysql query like this to get the row numbers.

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

In hibernate 3 ':=' was a problem.

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

i switch to hibernate 3 to 4 and used escape character.

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

Now another exception occured. It gives the exception:

"Parameter rank does not exist as a named parameter"

I tried this:

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

This time it gives this exception:

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

Any idea how can i solve this issue?

j3ny4 · Accepted Answer · 2014-05-28 15:22:22Z

1

While using doReturningWork() you gain the access to the plain JDBC connection. Then you should split your SQL into 2 executions (set and select):

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;
  }
});

answered May 28, 2014 at 15:22

j3ny4

4412 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vlad Mihalcea · Accepted Answer · 2014-05-28 08:12:17Z

0

Hibernate doesn't support database specific SQL syntaxes or SQL local variables.

From what I see you don't even want to select some entity, so you just need an aggregation query:

select count(t), t.id, t.name 
from Test t
group by t.id, t.name

Assuming you have a Test entity mapped to the test table.

answered May 28, 2014 at 8:12

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

5 Comments

varstas Over a year ago

Are you sure there is no way using local variables with hibernate? This is sad.

Vlad Mihalcea Over a year ago

You can always use native SQL, even with Hiberante so you can give that a try.

varstas Over a year ago

I dont understand you. I think i was using native SQL with "createSQLQuery". Can you explain your comment a little bit please?

Vlad Mihalcea Over a year ago

Check doWork to get access to the current Connection, where you can write plain old JDBC.

varstas Over a year ago

i tried doReturningWork() still doesnt let me to use local sql variable

Collectives™ on Stack Overflow

Hibernate createSqlQuery setting variable

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related