Escaping the colon character ':' in JPA queries

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

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"