Java JPA "Error compiling the query" when it uses an enum

Question

The following JPA query doesn't compile:

@NamedQuery(name = "PSA.findBySourceSystem", 
    query = "SELECT p FROM PSA p WHERE p.sourceSystem.id = :sourceSystemId")

p.sourceSystem is the following enum:

public enum SourceSystem {
    FIRST(3, "ABC"), SECOND(9, "DEF"), THIRD(17, "GHI");

    private int id;
    private String code;
    ...
}

and is mapped in PSA's base class:

public class PsaBase implements Serializable {
    @Column(name = "sourceSystemId")
    @Enumerated(EnumType.ORDINAL)
    protected SourceSystem sourceSystem;
    ...
}

The query compiles and runs fine if I replace p.sourceSystem.id in the query with something more benign.

Thank you in advance for any help.

axtavt · Accepted Answer · 2010-11-16 17:26:48Z

2

It shouldn't compile.

You have to resolve the required enum value manually before passing it as a query parameter:

@NamedQuery(name = "PSA.findBySourceSystem",  
    query = "SELECT p FROM PSA p WHERE p.sourceSystem = :sourceSystem")

.

public enum SourceSystem { 
    ... 
    private static Map<Integer, SourceSystem> valuesById = new HashMap<Integer, SourceSystem>();
    static {
        for (SourceSystem s: values()) 
            valuesById.put(s.id, s);
    }
    public static SourceSystem findById(int id) {
        return valuesById.get(id);
    }
}

.

em.createNamedQuery("PSA.findBySourceSystem")
    .setParameter("sourceSystem", SourceSystem.findById(sourceSystemId));

EDIT: Since sourceSystem is annotated as @Enumerated(EnumType.ORDINAL), it's stored in the database as the ordinal numbers of the corresponding enum values, therefore FIRST is stored as 0. JPA doesn't directly support using arbitrary field of the enum value to identify it in the database. If your database schema assumes so, you can do the following trick to decouple state of your object from the database schema:

public class PsaBase implements Serializable { 
    protected SourceSystem sourceSystem; 

    @Column(name = "sourceSystemId") 
    public Integer getSourceSystemId() {
        return sourceSystem.getId();
    }

    public void setSourceSystemId(Integer id) {
        this.sourceSystem = SourceSystem.findById(id);
    }
    ... getter and setter of sourceSystem with @Transient ...
}

edited Nov 16, 2010 at 17:26

answered Nov 16, 2010 at 15:27

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Solum Over a year ago

Thank you that helped it compile, but now if I pass in a SourceSystem value with an id of 3 for example, it always binds 0. The id is what I'm looking for in the resultant query.

David Solum Over a year ago

Ahh! I see that -- I just thought the 0 was a default value. Thank you so much -- that's all I needed.

Collectives™ on Stack Overflow

Java JPA "Error compiling the query" when it uses an enum

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related