3

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 '\\'

Then I run a native query using openJPA 2.2.2:

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

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.

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

CrushasaurusRex
  • 35
  • 7
Mick Belker - Pseudonym
  • 313
  • 2
  • 13

1 Answers1

2

@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 \."

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.

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

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 '\\'}");

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

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

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.

Mick Belker - Pseudonym
  • 313
  • 2
  • 13