CODEWITHSUNDEEP
javaspringspring-bootspring-data-jpaspring-data
jared
jared

Reputation: 483

JPA query fails on empty list

I have this entity:

@Data
@Entity
public class MyEntity {
    @Id Long id;
    @ManyToMany List<Foo> foos;
    @ManyToMany List<Bar> bars;
}

I need to prevent overlapping, so if any foos contains a foo with id 1 and a bar with id 2 there cannot be another MyEntity containing the same combination of foos and bars.

So i made this repository:

public interface MyEntityRepository extends JpaRepository<MyEntity, Long>  {
    List<MyEntity> findAllByFoosInAndBarsIn(List<MyEntity> foo, List<MyEntity> bar);
}

This works fine as long as both lists contains something. But if for some reason either is empty, the result set is too.

Upvotes: 1

Views: 4234

Answers (1)

Panagiotis Bougioukos
Panagiotis Bougioukos

Reputation: 18949

Your logic is not the default one. So you need to provide your required logic to a custom query. The following should do the job using named parameters.

public interface MyEntityRepository extends JpaRepository<MyEntity, Long>  {
    
   @Query("select m from MyEntity m WHERE 
     ( (NOT(:foo IS EMPTY) AND m.foos IN :foo) OR :foo IS EMPTY ) AND
     ( (NOT(:bar IS EMPTY) AND m.bars IN :bar) OR :bar IS EMPTY ) 
     List<MyEntity> findAllByFoosInAndBarsIn(@Param("foo") List<MyEntity> foo, 
                                             @Param("bar") List<MyEntity> bar);
}

In the provided query the filtering IN (...) for a provided list, would apply only if the provided list is not empty. If it is empty the filter IN (...) will not be applied in the where clause.

Upvotes: 2

Related Questions