Spring JPA Data “OR” query

Question

I am using Spring Data JPA and trying to add a query to my repository. I was trying to just build the query without the @Query annotation like:

List<Item> findByTypeAndStateOrStateAndStartDateBetween(Type type, State s, State s2, Date startDate, Date endDate);

My goal is to build a query like so:

select * from item where type = ? and (state = ? or state = ?) and start_date between ? and ?

My problem is with the OR clause. Is there a way to make sure there are brackets? Otherwise, the logic isn't right. The examples I found here: http://static.springsource.org/spring-data/data-jpa/docs/1.0.0.M1/reference/html/#jpa.query-methods.query-creation

don't have any or clauses with more than 1 column.

Also, is there a way to pass in a List of objects. For instance, if I wanted to find items with 3 states I would have to create another query, which doesn't scale very well.

Thanks.

EDIT:

I figured out how to pass of list of states using the @Query notation. You do it like so:

@Query("FROM item i WHERE i.type = ?1 AND i.state IN (?2)")

Then you can just pass in a list to the method as the second parameter. Still don't know how to do this without using the @Query notation.

Answer 1

I wanted to do the same thing with getting all data for a particular station, that was between two dates.

findByStartdateBetweenOrEnddateBetweenAndStation(start,end,start,end,station)
Gives you ==>
SELECT...WHERE (startdate between start,end) OR ( enddatebetween start,end AND station=station)

And:

findByStationAndStartdateBetweenOrEnddateBetween(station, start,end,start,end)
Gives you ==>
SELECT...WHERE (station=station AND startdate between start,end) OR enddatebetween start,end

Both are wrong as I wanted:

 SELECT...WHERE station=station AND (startdate between start,end OR enddatebetween start,end)

Use the @Query to be very specific and don't take chances mixing your AND/OR with the method name.

Answer 2

Well, you are very close. To do this without @Query the In or IsIn keyword can be used(not sure whether these keywords were supported at the time the question was asked):

List<Item> findByTypeAndStateInAndStartDateBetween(Type type, Collection<State> states, Date startDate, Date endDate);

In and NotIn also take any subclass of Collection as parameter as well as arrays or varargs. For other syntactical versions of the very same logical operator check Repository query keywords.

This will suffice your need of passing any number of states for the OR criteria.

Reference: Table 4. Supported keywords inside method names in Query creation docs