6

I'm using JPA (Hibernate) as the persistence layer.

I need to add a WHERE clause based on a regular expression, such some pattern are SELECT * FROM TableName where REGEXP_LIKE(ColumnName, 'Pattern'). What I get from the result is the list of string but I need to get mapped entities from the DB as an object not a string.

From my knowledge JPQL can return the result as an object but JPQL doesn't seem to support regular expressions, as it's a propietary extension from Oracle.

How can I apply regular expression to the JPQL?, what else should I need to know?

vanBasten
  • 61
  • 1
  • 2
  • 1
    This might be an answer to your question: http://stackoverflow.com/questions/8985205/does-eclipselink-support-queries-containing-regular-expression – heldt Mar 16 '12 at 08:09
  • 1
    You might need to use the native queries. – ManuPK Mar 16 '12 at 09:11

1 Answers1

5

There are no full regular expressions in JPQL but there are pattern values. According to the specification JPQL 2.2 (p. 188):

The pattern_value is a string literal or a string-valued input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for any sequence of characters (including the empty sequence), and all other characters stand for themselves.

[...]

Examples:

  • address.phone LIKE ‘12%3’ is true for ‘123’ ‘12993’ and false for ‘1234’

  • asentence.word LIKE ‘l_se’ is true for ‘lose’ and false for ‘loose’

  • aword.underscored LIKE ‘_%’ ESCAPE ‘\’ is true for ‘_foo’ and false for ‘bar’

  • address.phone NOT LIKE ‘12%3’ is false for ‘123’ and ‘12993’ and true for ‘1234’

If you want more advanced regular expression constructs you need to use native queries.

Community
  • 1
  • 1
Ortomala Lokni
  • 56,620
  • 24
  • 188
  • 240