Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to combine multiple date-between searches with CrudRepository of Spring Data JPA?

spring-data provides a way to generate SQL search by defining the method name.

The following works fine:

@Entity
public class Book {
  Date from, to;
}

//CrudRepository<Book>
findByFromDateBetween(Date departure, Date arrival);

But why then does the following not work?

findByFromDateBetweenAndToDateBetween(Date departure, Date arrival);

To connect two date searches, I have to repeat the date:

findByFromDateBetweenAndToDateBetween(Date departure, Date arrival, Date departure, Date arrival);

Question: is it possible to reuse the params?

like image 223
membersound Avatar asked Sep 16 '14 13:09

membersound


1 Answers

The Between keyword naturally binds two parameters. Thus after binding the from clause, the parameter list is exhausted and we don't know which parameters to use for the second criteria.

A manually defined query should do the trick:

interface BookRepository extends Repository<Book, Integer> {

  @Query("select b from Book b " +
         "where b.from between ?1 and ?2 and b.to between ?1 and ?2")
  List<Book> findByDatesBetween(Date departure, Date arrival);
}
like image 81
Oliver Drotbohm Avatar answered Nov 05 '22 16:11

Oliver Drotbohm