CODEWITHSUNDEEP
regexhibernatejpql
vanBasten
vanBasten

Reputation: 61

How to apply regular expression in JPQL?

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?

Upvotes: 6

Views: 11241

Answers (1)

Ortomala Lokni
Ortomala Lokni

Reputation: 62535

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.

Upvotes: 5

Related Questions