findByIn and findByBetween in Spring Data JPA — Collection and Range Queries
Fast recall
Quick revision
Core answer
Whether you reach for In/Between instead of custom @Query for multi-value and range filters
Not knowing In exists (writing @Query for IN), or passing an empty list and breaking the SQL
Mention findByIdIn for bulk fetch, BETWEEN being inclusive, and IN-clause size limits
Interview-ready guide
Interview answer
Spring Data parses In and Between as supported query keywords. findByStatusIn(statuses) binds a collection to a membership predicate. findByCreatedAtBetween(start, end) includes both boundaries, which makes a half-open time range often clearer for timestamps: GreaterThanEqual(start) and LessThan(end). An empty collection is not a portable 'invalid SQL' rule. Derived queries, manually written @Query methods, Hibernate versions, and databases can handle it differently. Validate the input and make the desired result explicit.
findByIn and findByBetween in Spring Data JPA — Collection and Range Queries
Direct Answer
findByStatusIn(Collection<Status>) creates an IN predicate, while findByAgeBetween(min, max) creates an inclusive BETWEEN predicate. Empty collection behavior should not be described as universally invalid SQL: repository query generation and database/provider handling differ. Decide explicitly whether an empty input should return an empty result, skip the filter, or be rejected.
Common Mistakes & Pitfalls
- Not knowing
Inexists — writing a custom@QueryIN clause whenfindByStatusIndoes it. - Empty collection —
findByStatusIn(List.of())→IN (), invalid SQL; guard or validate. - Wrong
Betweenboundaries —BETWEEN 10 AND 20includes both 10 and 20; useGreaterThan/LessThanfor exclusive ranges. - Timezone bugs — using
LocalDateTime.now()in date ranges without timezone care causes off-by-hours errors.
Check your understanding
Answer these without looking back at the article.
- What are derived query methods in Spring Data JPA?
- What is the findBy...Containing and findBy...Like in Spring Data JPA?
- How do you limit query results with Top and First in Spring Data JPA?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.