How do I escape the colon (:) in a mysql query over jdbc that contains a variable assignment?

Question

I'm trying to run a query that involves a custom variable over JDBC toward a MySQL 5.0 database (Hibernate 4 as the ORM):

SET @rownum := 0; SELECT rnum FROM (
    SELECT (@rownum := @rownum + 1) AS rnum, col_name_a
    FROM table_name
    WHERE (col_name_b IS NULL  OR col_name_b != 'USER' )  ORDER BY col_name_a) c
WHERE col_name_a = :aValue

The first SET @rownum := 0; is needed to reset the variable, but the colon isn't required there (I've set the allowMutilQuery=true as suggested in this question).

The problem is with the following SELECT, where I keep on getting:

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

Is there a way around this? Should I use other methods than

javax.persistence.Query q = EntityManager instance>.createNativequery(String)

and

q.setParameter(<param name>, <param value>)

?

Answer 1

In hibernate the escape of the colon : is done with a backslash:

SET @rownum \:= 0; SELECT rnum FROM ( ...

Or in java:

String sql = "SET @rownum \\:= 0; SELECT rnum FROM ( ...";