IS NULL check not working on list in JPA SQL Query

I have following JPA Query with a where clause:

@Query("SELECT P FROM ParentEntity P n" +
"INNER JOIN P.childEntities C n" +
"WHERE C.childId IN (:childIds)")
List<ParentEntity> findByChildIds(@Param("childIds") List<UUID> childIds);

childId column is notnull in table, all rows have value for this column.

Query works fine when the childIds param has values. But childIds is an optional parameter, it may be an empty list or null, in which case I would like to ignore the where clause and simply return

SELECT P FROM ParentEntity P n" +
    "INNER JOIN P.childEntities C

So I modified the query to this:

@Query("SELECT P FROM ParentEntity P n" +
"INNER JOIN P.childEntities C n" +
"WHERE ((:childIds) IS NULL OR C.childId IN (:childIds))")
List<ParentEntity> findByChildIds(@Param("childIds") List<UUID> childIds);

Now, when childIds is empty (= []), this query returns 0 results as (:childIds) IS NULL is false and C.childId IN (:childIds) is always false as well. But I want all the rows in this case.

and when childIds is null, then it throws an exception:

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

Instead of exception, I would like all the rows to return here as well.

Answer

You really have 2 separate queries, so rather than bury the logic in SQL, move it into a default method choses the appropriate straightforward JPA method:

default List<ParentEntity> findByChildIds(List<UUID> childIds) {
    if (childIds == null || childIds.isEmpty()) {
        return getAll();
    }
    return getByChildIds(childIds);
}

@Query("SELECT P FROM ParentEntity P n" +
"INNER JOIN P.childEntities C n" +
"WHERE C.childId IN (:childIds)")
List<ParentEntity> getByChildIds(@Param("childIds") List<UUID> childIds);

@Query("SELECT P FROM ParentEntity P n" +
"INNER JOIN P.childEntities C")
List<ParentEntity> getAll();

This makes the SQL easier to read and the logic easier to follow, plus it gives the database a better chance to optimize well.

Name all the methods here as you like – they don’t need to employ JPA method keywords.

I have used this approach to good effect in production.