Enumeration List with IN operator in JPQL

Question

Which is missing the following JPQL query?

SELECT h FROM WorkFlowHistory h WHERE h.referenceNo = :referenceNo 
AND h.workflowTask IN ('CONFIRMATION','PAYMENT','ISSUING')

Error :

invalid IN expression argument [CONFIRMATION]

Dynamic Query Method

public List<WorkFlowHistory> findWorkFlowHistoryByRefNo(String refNo, WorkflowTask ...workflowTasks) {
    ...
    StringBuffer buffer = new StringBuffer("SELECT h FROM WorkFlowHistory h WHERE h.referenceNo = :referenceNo");
    if(workflowTasks != null && workflowTasks.length > 0) {
        buffer.append(" AND h.workflowTask IN (");
        for (int i = 0; i < workflowTasks.length; i++) {
            buffer.append("'" + workflowTasks[i] + "'");
            if ((i + 1) != workflowTasks.length) {
                buffer.append(",");
            }
        }
        buffer.append(")");
    }
    Query q = em.createQuery(buffer.toString());
    ....
}

My enum class

public enum WorkflowTask {
    UNDERWRITING("Underwriting"),
    SURVEY("Survey"),
    APPROVAL("Approval"),
    INFORM("Inform"),
    CONFIRMATION("Confirmation"),
    PAYMENT("Payment"),
    PROPOSAL_REJECT("Proposal Reject"),
    ISSUING("Issuing");

    private String label;

    private WorkflowTask(String label) {
        this.label = label;
    }

    public String getLabel() {
        return label;
    }
}   

Answer 1

I think that problem lies in representation of your Enums in DB. How are your enums mapped in the database Ordinal or String?

The best would be just putting your enums as parameter placeholder in your JPQL query:

SELECT h FROM WorkFlowHistory h WHERE h.referenceNo = :referenceNo 
AND h.workflowTask IN :workflowTasks

and than adding it as parameter to your query, something like this:

...
List<WorkflowTask> workflowTasks=Arrays.asList({WorkflowTask.CONFIRMATION, WorkflowTask.PAYMENT WorkflowTask.ISSUING });
...
Query q = em.createQuery(buffer.toString());
q.addParameter("workflowTasks", workflowTasks);

This way you don't have to think about how are your Enums represented in DB.