30654

Why do backslashes need to be escaped in JPA native queries?

<h3>Question</h3>

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?


<h3>Answer1:</h3>

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

来源:https://stackoverflow.com/questions/34024313/why-do-backslashes-need-to-be-escaped-in-jpa-native-queries

Recommend

  • Create Variable In Oracle
  • How to do feature selection using linear SVM weights [closed]
  • How can I prevent page-break in CFDocument from occuring in middle of content?
  • How to replace all occurences except the first one?
  • ScalaTest v3: why require to implement convertToLegacyEqualizer
  • Order items in a JS tracking code on Order received page in Woocommerce
  • How can I control the size of the help window using System.Windows.Forms.Help.ShowHelp()?
  • Best Practice for PHP exit()
  • Memory leaks when using pthead_exit() to exit thread
  • Call a specific instance of a service in Azure Service Fabric
  • Preloading webView doesn't work - trying to reduce loading time
  • Python Equivalent of Java's 'Keystore'?
  • Jekyll - How do I create pages in the root directory?
  • Reader monad - how does it conform to Monad interface?
  • How to convert days into months using datetime in Python3?
  • Implementation of timeout in LDAP
  • Getting Microsoft Calibri font on Amazon EC2 ubuntu
  • Checking for valid enum types from protobufs
  • Why do you need 2 Javascript files for cross-platform Cordova plugin?
  • Create an Office365 mailbox from within C# Web API method
  • How to create subsets of a single set of elements with XSLT?
  • Create an average of multiple excel chart without the data source
  • using maven pom while creating jar:test-jar some times it says JAR will be empty - no content was ma
  • PHPMailer return to AJAX
  • How to configure the APNS.Certificate in the arm template
  • Geokit in Ruby on Rails, problem with acts_as_mappable
  • How to include associated objects using gon in Rails/jQuery
  • How can I ssh into a server that requires 2 password authentication using python's paramiko mod
  • JavaScript Regex to Match Boundaries of Words with diacritics
  • how to run ejabberd with Erlang on Heroku?
  • How to use Streams api peek() function and make it work?
  • `$http:badreq Bad Request Configuration` - from angular post method, what is wrong here?
  • Send array to next viewcontroller iOs xcode [duplicate]
  • Background transfer download task failed when app was closed
  • Codeigniniter insert data through models and controller
  • XEP-0166: Jingle protocol implementation for voice/video chat in iOS
  • Call Microservice from another Microservice within Docker
  • PHP Permalinks.. how to change?
  • media foundation H264 decoder not working properly
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?