PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I use an SQL 'in' clause with potentially empty list parameter in Java JPA?

I have a JPA repository interface with the following method:

@Modifying
@Query(value = "DELETE FROM bivrip.bloop_options o WHERE o.bloop_id = ?1 AND o.id not in ?2", nativeQuery = true)
public void removeOtherOptions(int bloopID, List<Integer> validOptionIDs);

When the validOptionIDs contains at least one element, the method behaves as expected and removes all ids not in the list associated with the given bloop. However, if the list is empty, it doesn’t delete anything – when I would assume it would delete all options for the provided bloop.

Do I need to do anything extra configuration-wise in order to make this work? Or am I condemned to writing another method along the lines of "removeAllOptionsForBloop"?

Note: If this should generally work and is a result of particular database implementation, I’m using postgres.

Tags:

2

Answers


  1. 0

    Rewrite the query to use an array parameter and the <> ALL operator:

    ... AND o.id <> ALL (?2)

    That will work for empty arrays as well.

    Login or Signup to reply.
  2. 0

    You can try to use JpaSpecificationExecutor if you like, I think It is better because you have more control of your query and you don’t need to write native SQL.

    public interface BloopOptionRepository extends JpaRepository<BloopOption, Integer>, JpaSpecificationExecutor<BloopOption> {

}

public class MyService {

    @Autowired
    private BloopOptionRepository bloopOptionRepository;

    //You can move to another class Like BloopOptionQuerySpecification
    private static Specification<BloopOption> bloopId(int bloopId) {
        return (root, query, criteriaBuilder) ->
                criteriaBuilder.equal(root.get("bloopId"), bloopId);
    }

    private static Specification<BloopOption> notInOptionIds(List<Integer> optionIds) {
        return  (root, query, criteriaBuilder) -> criteriaBuilder.notIn(root.get("id")).value(optionIds);
    }

    public void removeOtherOptions(int bloopID, List<Integer> validOptionIDs) {
        Specification<BloopOption> spec = Optional.ofNullable(validOptionIDs)
                .filter(ids -> !ids.isEmpty())
                .map(ids -> bloopId(bloopID).and(notInOptionIds(ids)))
                .orElseGet(() -> bloopId(bloopID));
        bloopOptionRepository.delete(spec);
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search