30

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?

Rob Crawford
  • 536
  • 1
  • 5
  • 9

3 Answers3

90

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"
rogerdpack
  • 62,887
  • 36
  • 269
  • 388
user1985660
  • 1,001
  • 7
  • 7
  • 5
    This is exactly what I was looking for needed to escape postgres json to string conversion with ::text – GameSalutes Sep 23 '16 at 01:18
  • Just what i needed, Thanks, used this with where clause ---------------------------- where update_date \\:\\:date = :fromDate\\:\\:date-------------- in my @Query and it worked. – JavaDragon Mar 27 '21 at 17:59
1

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

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • Thanks, Pascal! It appears Hibernate is not quite on-spec with its treatment of named parameters with native queries. I've instead found a different way to solve the problem. – Rob Crawford Nov 19 '10 at 15:07
-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();
enigma
  • 3,476
  • 2
  • 17
  • 30
car fax
  • 1
  • It "looked" like I'd need to do that once. Turns out that I needed a newer version of hibernate (4.1.3) then it worked with single backslash :) https://stackoverflow.com/questions/9460018/how-can-i-use-mysql-assign-operator-in-hibernate-native-query/9461939#9461939 – rogerdpack Jul 28 '20 at 19:17