JPQL WHERE IN Help

[Norman Davis]

Hello to all -

I'm working with JPA for the first time, and have come across a problem I can't seem to find a solution for, executing a WHERE IN native query.

Entity Class:

@NamedQuery(name = "Event.findByBranches", query = "SELECT e FROM Event e WHERE e.location IN (:branches)"),

From EntityManagerController:

@PersistenceContext ( unitName = "tmbcPU" )
EntityManager manager;

public Collection<Event> getEventsByFilter ( RegistrantsFilter filter) {
List branchList = new ArrayList();
branchList.add(2);
branchList.add(45);
Collection<Event> eventCollection = manager.createNamedQuery( "Event.findByBranches").setParameter("branches", branchList).getResultList();
return eventCollection;
}

The RegistrantsFilter isn't being used currently, I can't even run a test case. The Exception thrown isn't much help, just an EJB exception subsequently being thrown by an AbortProcessException.

I'm using EclipseLink JPA 2.0 to a MySQL 5.x database w/ Glassfish 3.1. My persistence.xml works with 4 other entity classes with basic CRUD operations, but this WHERE IN is starting to be the death of me. I've tried different data structures, removing the () around :branches, tried making the List generic with integers, everything.

Any help would be greatly appreciated.

[James Sutherland]

Do you mean "named" query or "native" query?

You annotation is a named JPQL query, not an native SQL query.

This should work for JPQL, but remove the brackets. Also ensure you are using at least the EclipseLink 2.1 release.
For a native SQL query you cannot bind a collection, as it is not a valid SQL type.

What error are you getting? If you have something else masking the error, put a try catch around the query getResutList and dump the exception, or enable logging.