The pitfall of setting a list of parameters on a JQL query

I recently had a need to provide a collection as a parameter to a JQL IN expression. This is what I did:

List results = getJpaTemplate()
  .find(
    "from AbstractEvent e "
    + "where "
    + "(e.subscription, e.eventTime) in "
    + "(select e.subscription, max(e.eventTime) "
    + "from e "
    + "where "
    + "e.class in "
    + "(SentSMSFlightSubscribed,"
    + " ReceivedSMSFlightUnsubscribe) and "
    + "e.subscription.flightNo in (?) and "
    + "e.subscription.scheduled = ? "
    + "group by e.subscription)",
    renderedFlight.getFlights(),
    renderedFlight.getScheduled());

The problem with the query is that you get a class cast exception when an attempting to substitute the collection (renderedFlight.getFlights) .

To fix this you simply need to be explicit about the parameters:

    + "e.subscription.flightNo in (?1) and "
    + "e.subscription.scheduled = ?2 "

Problem solved.

Updated: Sunday 11 July 2010: Even using position parameters doesn't always work. While it works in the above example it didn't work for me when using a parameter of a non-primitive type; at least I think that was the issue. So in a nutshell, if the above doesn't work for you then just use findByNamedParams instead.

You may also note this beauty of a line:

e.class in (
 SentSMSFlightSubscribed,
 ReceivedSMSFlightUnsubscribe)

That is how you perform an "instanceof" style of operation in JQL. Nice.