在JPA查询中转义冒号':' [英] Escaping the colon character ':' in JPA queries

问题描述

我正在尝试通过使用':'字符的JPA运行本机查询.特定实例在查询中使用MySQL用户变量:

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 

JPA代码:

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

但是，这给了我一个例外，即不允许其在':'后加上空格.我尝试用反斜杠将其转义，我尝试将它们加倍以进行转义.有什么方法可以真正做到这一点，或者我是SOL?

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?

推荐答案

我不知道在查询中转义冒号字符的标准方法，该查询显然被解释为命名参数前缀，从而使查询解析器混乱

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.

我的建议是，如果可能的话，创建并使用SQL函数.根据提供者的不同，可能还有其他选择(例如使用另一个字符，并在拦截器中用:替换所选字符)，但至少先前的建议将使您的JPA代码在提供者之间可移植.

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:如果您使用的是Hibernate，则 HHH-1237 .

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

更新:JPA 1.0规范中有一个有趣的"段落，涉及命名参数和本机查询:

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

3.6.3命名参数

命名参数是标识符 以:"符号为前缀. 命名参数区分大小写.

3.6.3 Named Parameters

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

命名参数遵循以下规则: 在4.4.1节中定义的标识符. 命名参数的使用适用于Java Persistence查询 语言，并且未针对 原生查询.仅位置 参数绑定可以方便地使用 用于本机查询.

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.

传递给参数的参数名称 Query的setParameter方法 API不包含:"前缀.

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

这篇关于在JPA查询中转义冒号':'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！