mysql - Escaping the colon character ':' in JPA queries

Question

I'm trying to run a native query through JPA that uses a ':' character. The particular instance is using a MySQL user variable in the query:

SELECT foo, bar, baz, 
    @rownum:= if (@id = foo, @rownum+1, 1) as rownum, 
    @id    := foo                         as rep_id 
FROM 
    foo_table 
ORDER BY 
    foo, 
    bar desc 

The JPA code:

Query q = getEntityManager().createNativeQuery(query, SomeClass.class);
return q.getResultList();

However, this gives me an exception about not being allowed to follow a ':' with a space. I've tried escaping them with backslashes, I've tried escaping them by doubling them up. Is there any way to actually do this, or am I SOL?

Answer 1

I faced similar experience when using postgresql json function in native JPA query.

select * from component where data ::json ->> ?1 = ?2

JPA will throw error that i have not set the named parameter :json.

The solution:

"select * from component where data \:\:json ->> ?1 = ?2"