Escaping the colon character ':' in JPA queries

mysql jpa jpql
Rob Crawford picture Rob Crawford · Nov 12, 2010 · Viewed 14.7k times · Source

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

user1985660 picture user1985660 · Mar 20, 2015

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"

Related questions

What is the LIMIT clause alternative in JPQL?

I'm working with PostgreSQL query implementing in JPQL. This is a sample native psql query which works fine, SELECT * FROM students ORDER BY id DESC LIMIT 1; The same query in JPQL doesnt work, @Query("SELECT s FROM Students s ORDER …

Read More
JPA's Map<KEY, VALUE> query by JPQL failed

I am storing a Map in JPA , which stores a keyword translation in each language . such as one object stores Locale.ENGLISH -> "Father" , Locale.CHINESE -> "PaPa". And another object stores Locale.ENGLISH -> "Mother" , Locale.…

Read More
Hibernate: Automatically creating/updating the db tables based on entity classes

I have the following entity class (in Groovy): import javax.persistence.Entity import javax.persistence.Id import javax.persistence.GeneratedValue import javax.persistence.GenerationType @Entity public class ServerNode { @Id @GeneratedValue(strategy = GenerationType.AUTO) Long id String firstName String lastName } and …

Read More