为什么在JPA本机查询中需要转义反斜杠? [英] Why do backslashes need to be escaped in JPA native queries?

问题描述

在JPA中，当执行本机查询时，我必须对反斜杠字符进行转义以使反斜杠加倍.例如.当有类似where的子句时

In JPA, when executing a native query, I have to escape backslash characters to double backslash. E.g. when having a where clause like

UPPER(app.NAME) like UPPER(?) escape '\'

然后我需要创建一个包含以下内容的字符串:

then I need to create a string which contains:

UPPER(app.NAME) like UPPER(?) escape '\\'

然后我使用openJPA 2.2.2运行本机查询:

Then I run a native query using openJPA 2.2.2:

final EntityManager entityManager = EntityManagerFinder.getEntityManager();
final Query query = entityManager.createNativeQuery(sql, Response.class);

从XML文件读取SQL语句，因此此处未完成Java转义.此外，我在调试过程中验证了Java中的String与XML文件中的String确实相同.

The SQL statement is read from XML file, hence Java escaping is not done here. Furthermore I verified during debugging that the String in Java is really the same as in the XML file.

为什么JPA希望本机查询使用双反斜杠?这是openJPA中的错误吗?

Why is JPA expecting double backslash for native queries? Is this a bug in openJPA?

推荐答案

@Neil:这很好地暗示了它可能是数据库特定的，而不是JPA特定的. 使用Oracle 11gR2，我发现: Oracle文本查询中的特殊字符

@Neil: It was a good hint that it could be DB specific, not JPA specific. Using Oracle 11gR2, I found: Special characters in Oracle Text Queries

要转义反斜杠转义符，请使用\."

"To escape the backslash escape character, use \."

但是，我不明白为什么在使用JPA时为什么需要转义反斜杠，但是在PL/SQL Developer中，我绝不能转义.如果我在PL/SQL Developer中转义了反斜杠，则会收到错误消息.

However, I do not understand why I need to escape backslash when I use JPA, but in PL/SQL Developer, I MUST NOT escape. If I escape backslash in PL/SQL Developer, I get an error message.

此外，openJPA的跟踪日志显示了SQL，该SQL以'\'提交为'\'，这很令人困惑.

Furthermore the trace log of openJPA shows the SQL, which was submitted with '\' as '\', which is quite confusing.

关于JDBC: Oracle JDBC文档/LIKE转义字符提到:

Regarding JDBC: The Oracle JDBC documentation / LIKE Escape Characters mentions:

如果要将反斜杠字符()用作转义字符，则必须输入两次，即\.例如:"

"If you want to use the backslash character () as an escape character, then you must enter it twice, that is, \. For example:"

ResultSet rset = stmt.executeQuery("SELECT empno FROM emp
            WHERE ename LIKE '\\_%' {escape '\\'}");

这是一个简单的Java转义，Java内部产生一个反斜杠字符.

which is a simple Java escape, and Java-internal results in a single backslash character.

我还必须以正则表达式转义，不仅限于LIKE:

I also must escape in regular expressions, not only in LIKE:

SELECT regexp_replace(
  listagg(h.node_id, ',') WITHIN GROUP (ORDER BY h.node_id),
  '([^,]+)(,\1)+', '\1') as node_ids

所以我仍然很困惑，因为它既没有在JPA跟踪日志中也没有在PL/SQL开发人员中执行时转义. JPA跟踪应该是发送到数据库驱动程序的SQL.

So I'm still confused as it's not escaped neither in the JPA trace log nor when executing it in PL/SQL developer. The JPA trace should be the SQL which is sent to the DB driver.

这篇关于为什么在JPA本机查询中需要转义反斜杠?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！