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" 

Answer 2

I'm not aware of a standard way to escape a colon character in a query that is obviously interpreted as a named parameter prefix, and thus confuses the query parser.

My suggestion would be to create and use SQL functions if possible. Depending on your provider, there might be other options (like using another character and substituting the chosen character by a : in an interceptor) but at least the previous suggestion would keep your JPA code portable across providers.

PS: if you're using Hibernate, there is a very old patch attached to HHH-1237.

Update: There is an "interesting" paragraph in the JPA 1.0 spec about named parameters and native queries:

3.6.3 Named Parameters

A named parameter is an identifier that is prefixed by the ":" symbol. Named parameters are case-sensitive.

Named parameters follow the rules for identifiers defined in Section 4.4.1. The use of named parameters applies to the Java Persistence query language, and is not defined for native queries. Only positional parameter binding may be portably used for native queries.

The parameter names passed to the setParameter methods of the Query API do not include the ":" prefix.

This won't really help you but your case is a strong hint that the ":" in native queries shouldn't even be considered (at least not without a way to escape it or disable it detection).