JPA enum comparison not equal fails

Question

I am using JPA 2.1.2.

I want to execute a select query with a where clause. The where statement should compare (not equal) enums stored in database (string).

@Entity
@Table(name = "MY_ENTITY")
public class MyEntity implements Serializable {

    @Column(name = "REMINDER_STATE")
    @Enumerated(EnumType.STRING)
    private ReminderStage reminderStage;

    ...
}

class DaoImpl{
 ....

    @Override
    public List<MyEntity> findAll(ReminderStage stage) {
        return em.createQuery("SELECT c FROM MyEntity c  WHERE  c.reminderStage != :reminderStage", MyEntity.class)
                .setParameter("reminderStage", stage).getResultList();

    }
}

But when I execute the query I get the following exception:

...
[28.07.14 08:27:07:910 CEST] 0000004e SystemErr     R Caused by: <openjpa-2.1.2-SNAPSHOT-r422266:1530146 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'SELECT c FROM CertInfo c  WHERE  c.reminderStage != :reminderStage'. Error message: org.apache.openjpa.kernel.jpql.TokenMgrError: Lexical error at line 1, column 50.  Encountered: "!" (33), after : ""
[28.07.14 08:27:07:910 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:2449)
[28.07.14 08:27:07:910 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.<init>(JPQLExpressionBuilder.java:2432)
[28.07.14 08:27:07:910 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:49)
[28.07.14 08:27:07:910 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.ExpressionStoreQuery.newCompilation(ExpressionStoreQuery.java:154)
[28.07.14 08:27:07:910 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:672)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:654)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:620)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:682)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:589)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:996)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at com.ibm.ws.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:107)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at com.ibm.ws.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:86)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at com.ibm.ws.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:34)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:974)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at com.ibm.ws.jpa.management.JPATxEmInvocation.createQuery(JPATxEmInvocation.java:353)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at com.ibm.ws.jpa.management.JPAEntityManager.createQuery(JPAEntityManager.java:550)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   at ...findAll(DaoImpl.java:271)
[28.07.14 08:27:07:911 CEST] 0000004e SystemErr     R   ... 13 more

When I change from not equal (!=) to equal (=) the query works fine.

So how can I use enum comparison with not equal?

Solution 1

Not equal ? <> use this operator.

JPQL operators (according to Section 10.2.5.6. of the JPQL Language Reference)

1. Navigation operator (.)
2. Arithmetic operators: +, - unary *, / multiplication and division +, - addition and subtraction
3. Comparison operators: =, >, >=, <, <=, <> (not equal), [ NOT ] BETWEEN, [ NOT ] LIKE, [ NOT ] IN, IS [ NOT ] NULL, IS [ NOT ] EMPTY, [ NOT ] MEMBER [ OF ]
4. Logical operators: NOT AND OR

Solution 2

When using Jpql, the correct operator for "not equal" is <> . So, update your code like this:

 return em.createQuery("SELECT c FROM MyEntity c  WHERE  c.reminderStage <> :reminderStage", MyEntity.class)
                .setParameter("reminderStage", stage).getResultList();

Remove != an use <> instead.