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

?

Rewire

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 ( ...";

Paratroops

Great! Do you know if that will be ORM-independent? – Rewire

I do not know of any standard in JPA query language, jpql. The definition of parameters, colon+identifier, would not need escaping the colon. But know, I have never encountered this problem. – Paratroops

@Joop Eggen will it also work in case of a stored procedure, lp_dyn:LOOP I have this in a dynamic stored procedure I have to write, but it gives me an error when I escape it with '//' – Sportswoman

@endlessProgrammer "....lp_dyn:\\:LOOP..." isn't it (backslash, twice when inside quoted string? I do not know. – Paratroops

@Joop Eggen Yeah! My bad, I meant `\\`. but this doesn't work inside of a stored proc. – Sportswoman

@Joop Eggen did you just use double colon ?? was that intentional ? – Sportswoman

side Note: Escape of backslash(as in file path) is also done with another backslash. – Scant