CODEWITHSUNDEEP
mysqljpajpql
Rob Crawford
Rob Crawford

Reputation: 556

Escaping the colon character ':' in JPA queries

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?

Upvotes: 32

Views: 32221

Answers (4)

Razvan Mitrica
Razvan Mitrica

Reputation: 1

I had the same issue using hibernate-5.6.15.Final and after a bit of debugging i found the way to escape the : character in a native query is adding another : character.

you can try:

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

Upvotes: 0

user1985660
user1985660

Reputation: 1050

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"

Upvotes: 95

car fax
car fax

Reputation: 1

Try this:

String 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  -- escape='\' ";

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

Upvotes: -1

Pascal Thivent
Pascal Thivent

Reputation: 570285

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

Upvotes: 1

Related Questions