How to use two date parameters for two Betweens without repeating the parameters using Spring Boots?

Question

I’m starting with Spring Boot and I’m trying to use it for a query where I want to check if two date columns are between a period provided by parameters. The code below works, but for this I have to repeat the parameters (start date and end date for comparison).

@Transactional(readOnly = true)
Collection<MyClass> findByDateBeginBetweenOrStartedWorkBetween (Instant firstDate, Instant lastDate, Instant firstDateAgain, Instant lastDateAgain);

I haven’t found any similar example so far. I figured it could be something like the excerpt below:

@Transactional(readOnly = true)
Collection<MyClass> findByDateBeginOrStartedWorkBetween (Instant firstDate, Instant lastDate);

But when I compile the project, I get the error below:

Caused by: java.lang.Illegalargumentexception: Failed to create query for method public Abstract java.util.Collection with... Myclass.findByDateBeginOrStartedWorkBetween (java.time.Instant,java.time.Instant)! No Parameter available for part startedWork BETWEEN (2): [Isbetween, Between] NEVER.

I can (and if so, how can I) use my comparison parameters only once using Spring Boot?

Answer 1

The between connects two parameters, which are not reusable after the internal assembly of the query by Spring. The error message already gives you this tip:

No Parameter available for part startedWork

When the first part of query is built, the two parameters you passed are consumed, so there are no more parameters left for the second part of the query, hence the error.

For your problem, the use of @Query should solve (a generic example, apply it to your specific case):

Transactional(readOnly = true)
@Query(select x from MyClass x where x.firstDate between ?1 and ?2 and x.lastDate between ?1 and ?2)
Collection<MyClass> findByDatesBetween(Instant firstDate, Instant lastDate);