Spring JPA - Getting all entities that have any of X date columns between range

Question

I have an Organization entity that extends from AbstractEntity, which is giving it three date columns: createdAt, updatedAt and checkedAt.

public abstract class AbstractEntity {

  @Column(name = "createdAt")
  protected LocalDateTime createdAt;

  @Column(name = "updatedAt")
  protected LocalDateTime updatedAt;

  @Column(name = "checkedAt")
  protected LocalDateTime checkedAt;

  //more stuff
}

On the OrganizationRepository I made some query methods for getting any of 'createdAt', 'updatedAt' or 'checkedAt' which are working wonderfully, but I tried making a query method that checks all three columns at the same time and it breaks the build with a couple java.lang.IllegalStateException at PartTreeJpaQuery.java:161 exceptions.

@Repository
public interface OrganizationRepository extends JpaRepository<Organization, String>{

  List<Organization> findByParentOrganizationIsNull();

  Optional<Organization> findByExternalId(String id);

  List<Organization> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);
  List<Organization> findByUpdatedAtBetween(LocalDateTime start, LocalDateTime end);
  List<Organization> findByCheckedAtBetween(LocalDateTime start, LocalDateTime end);
  // THIS LAST ONE DOESN'T WORK
  List<Organization> findByCreatedAtBetweenOrUpdatedAtBetweenOrCheckedAtBetween(LocalDateTime start, LocalDateTime end);

}

How can I make a query that checks that Any of the three columns is between the start and end dates??

score 0 · Answer 1 · answered Aug 05 '20 at 14:32

0

List<Organization> findByCreatedAtBetweenOrUpdatedAtBetweenOrCheckedAtBetween(LocalDateTime start, LocalDateTime end);

or alternatively

List<Organization> findByCreatedAtOrUpdatedAtOrCheckedAtBetween(LocalDateTime start, LocalDateTime end);

Don't work, as far as I understand now, because the start and end parameters get consumed by each Between, so the next ones don't know what parameters to use. See How to combine multiple date-between searches with CrudRepository of Spring Data JPA?

The solution was making a regular @Query with the column between OR column between OR column between, that uses both parameters several times.

@Query("select o from Organization o " +
        "where o.createdAt between ?1 and ?2 " +
        "or o.updatedAt between ?1 and ?2 " +
        "or o.checkedAt between ?1 and ?2")
List<Organization> findByAnyDateBetween(LocalDateTime start, LocalDateTime end);

answered Aug 05 '20 at 14:32

Iñaki Guastalli

147
2
13

Use 3 pair of same start/end in method naming query then it will work – Eklavya Aug 05 '20 at 20:03
@User-Upvotedon'tsayThanks not exactly following you, can you write an example method? – Iñaki Guastalli Aug 06 '20 at 13:12
1

I mean `List findByCreatedAtBetweenOrUpdatedAtBetweenOrCheckedAtBetween(LocalDateTime start1, LocalDateTime end1,LocalDateTime start2, LocalDateTime end2,LocalDateTime start3, LocalDateTime end3);` – Eklavya Aug 06 '20 at 13:15
@User-Upvotedon'tsayThanks oh yeah, if you add multiple end and start dates that should work! thanks for the alternative – Iñaki Guastalli Aug 06 '20 at 14:15

Spring JPA - Getting all entities that have any of X date columns between range

1 Answers1