JPQL LIKE expression on enum

Question

Can JPQL execute LIKE expressions against enums?

If I have an entity Foo with an enum field bar I can execute the following in MySQL(bar is stored as a MySQL enum)...

SELECT * FROM Foo WHERE `bar` LIKE '%SUFFIX'

However, the corresponding query in JPQL...

SELECT f FROM Foo f WHERE f.bar LIKE '%SUFFIX'

...complains that...

Parameter value [%SUFFIX] was not matching type [com.example.Foo$EnumType] 

Answer 1

I don't think it's possible, the left part of a LIKE is supposed to be a string_expression (in standard JPA). From the specification:

4.6.9 Like Expressions

The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:

string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

The string_expression must have a string value. 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. The optional escape_character is a single-character string literal or a character-valued input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore and percent characters in pattern_value.

And an enum_expression is not a string_expression.

The following would work though (using enum literals):

SELECT f 
  FROM Foo f 
 WHERE f.bar = com.acme.Bar.SOME_CONSTANT 
    OR f.bar = com.acme.Bar.SOME_OTHER_CONSTANT

Another option would be to actually store the bar field as a String (and to do some conversion from and to an enum in the getter/setter).

Reference

• JPA 1.0 Specification
  • Section 4.6.9 "Like Expressions"
  • Section 4.14 "BNF"

Answer 2

What JPA provider and version are you using?

This should work in EclipseLink 2.1.

Otherwise you could try applying a function to the enum to change it to a varchar, or use native SQL, or map it as a String instead.