Monday, April 12, 2010

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.

 

No comments: