Problems with making a query when using Enum in entity

Question

I have the following in a Question entity:

@NamedQuery(name = "Question.allApproved",
    query = "SELECT q FROM Question q WHERE q.status = 'APPROVED'")

and

@Enumerated(EnumType.STRING)
private Status status;

// usual accessors

I am getting this exception:

Exception Description: Error compiling the query [Question.countApproved: SELECT COUNT(q) FROM Question q WHERE q.status = 'APPROVED'], line 1, column 47: invalid enum equal expression, cannot compare enum value of type [myCompnay.application.Status] with a non enum value of type [java.lang.String]. at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:501)

How do I fix this?

are you using a custom enum? Can you update the document to show the Status enum? — Gonzalo Garcia Lasurtegui, Nov 21 '11 at 19:22

score 231 · Accepted Answer · answered Nov 21 '11 at 19:10

231

I think you should use your (fully qualified) Status enum instead of literal value, so something like this: (assuming your Status enum is in com.myexample package)

@NamedQuery(name = "Question.allApproved", 
            query = "SELECT q 
                     FROM Question q 
                     WHERE q.status = com.myexample.Status.APPROVED").

answered Nov 21 '11 at 19:10

Piotr Nowicki

17,914
8
63
82

What about a JPA2 regular @Query ? It complains with: The value for annotation attribute Query.value must be a constant expression. – Stephane Sep 21 '14 at 10:23
2

What `@Query` annotation are you talking about? – Piotr Nowicki Sep 21 '14 at 12:31
4

This `fully qualified` is more important as I thought it could be. – alexander Aug 12 '15 at 16:13
17

On a sidenote: It didn't work when the enum was an inner class of the entity. If this doesn't work for you, make sure the enum is it's own file! – evandongen Oct 10 '19 at 06:57
1

@evandongen any solution if enum is inner class? – Raja aar Jan 05 '21 at 13:56
@Rajaaar, there wasn't unfortunately – evandongen Jan 06 '21 at 14:55
ok, luckily my code is flexible enough to send list of enum when calling the repo method. Example, @Query("SELECT a from a where a.Status IN (:statuses)") myRepo.someMethodName(@Param("statuses") List statuses) This worked for me. anyway thanks for the response @evandongen – Raja aar Jan 06 '21 at 15:47
@evandongen, see this answer from another thread to deal with inner classes: https://stackoverflow.com/a/48393602/3232174 – GB11 Feb 21 '21 at 18:21
Note that for example to work enum from query needs to be public, I had them as package private (in the same package as jpa repo) and I was getting errors. – iwek Jan 03 '23 at 13:36
@raja-aar see: https://stackoverflow.com/a/35995708/3632617 – Isaace Jan 04 '23 at 16:36

score -5 · Answer 2 · answered Apr 30 '16 at 14:52

4 years since the initial post, there are some developments. Using spring 4 and Hibernate 4 it's now possible to 'trick' Hibernate using a SpEL expression. For example:

The enum:

package com.mycompany.enums

public enum Status {
    INITIAL, PENDING, REJECTED, APPROVED, SHIPPED, DELIVERED, COMPLETE;
}

Here's a wrapper class called 'Filter' which we'll pass to the repository filtering method.

package com.mycompany.enums

public class Filter implements Serializable {

    /** The id of the filtered item */
    private Integer id;
    /** The status of the filtered item */
    private Status status;
    // more filter criteria here...

    // getters, setters, equals(), hashCode() - omitted for brevity

    /**
     * Returns the name of the status constant or null if the status is null. This is used in the repositories to filter
     * queries by the status using a the SPEL (T) expression, taking advantage of the status qualified name. For example:
     * {@code :#{T(com.mycompany.enums.Status).#filter.statusName}}
     *
     * @return the status constant name or null if the status is null
     */
    public String getStatusName() {
        return null == status ? status : status.name();
    }

 }

Finally, in the repository, we can now use the Filter class as the single parameter and make the query translate what appears to be a mixture of literals and SpEL expressions to a Status object:

The repository:

package com.mycompany.repository

@Repository
public interface OrderRepository extends CrudRepository<Order, Integer> {

    @Query("SELECT o from Order o "
            + "WHERE o.id = COALESCE(:#{#filter.id},o.id) "
            + "AND o.status = COALESCE(:#{T(com.mycompany.enums.Status).#filter.statusName},o.status)")
    public List<Order> getFilteredOrders(@Param(value = "filter") Filter filter);
}

This works perfectly, but for some odd reason I haven't figured out yet, if you enable SQL debugging in Hibernate and turn on the binder logging, you'll not be able to see Hibernate binding this expression to query variables.

You don't see it among the variable bindings because the constant has already been substituted into the query string. — AbuNassar, Dec 18 '18 at 16:33

Problems with making a query when using Enum in entity

2 Answers2

Linked