JPA Query with multiple where clauses and multiple data sets

Question

@repository    
public interface BookRepository extends JPARepository<BookEntity bookEntity, Integer bookId>{

@Query
("select b from bookEntity b
where b.name=:#{#bookDTO.name}
and b.isbn=:#{#bookDTO.isbn}
... and 3-4 other where clauses
)
public Optional<BookEntity> findByMultipleCols(@Param("bookDTO") BookDTO bookDTO);

This works perfectly when I need to run this query only once. I have a scenario where this query needs to be run multiple times for various sets of BookDTO. More like a List object.

As of now, I am calling this method findByMultipleCols(bookDTO), using a list iterator. Is there any other way to call this method only once and use a List as a @Param?

I am using Spring Boot 3.1.1 version.

the search is with the following set
book1-name  author1     200
book1-name  author2     300


database table content:
book1-name  author2     200
book1-name  author2     300

If the query runs correctly, the result should be only one row, i.e. the second one.

Answer 1

you can use "IN" in your query like

@Repository public interface BookRepository extends JpaRepository<BookEntity, Integer> {

@Query("SELECT b FROM BookEntity b WHERE b.name IN :#{#bookDTOs.stream().map(BookDTO::getName).collect(Collectors.toList())} AND b.isbn IN :#{#bookDTOs.stream().map(BookDTO::getIsbn).collect(Collectors.toList())} ...")
List<BookEntity> findByMultipleBooks(@Param("bookDTOs") List<BookDTO> bookDTOs); }

if your queried tables are large can be useful batch, async or cache