How To Check Collection For Null In Spring Data Jpa @query With In Predicate

I have this query in my spring data jpa repository: @Query('SELECT table1 FROM Table1 table1 ' + 'INNER JOIN FETCH table1.error error' + 'WHERE table1.date = ?1 ' + 'AND (

Solution 1:

1. COALESCE with one parameter does not make sense. This is an abbreviated CASE expression that returns the first non-null operand. (See this)

2. I would suggest you to use named parameters instead of position-based parameters. As it's stated in the documentation this makes query methods a little error-prone when refactoring regarding the parameter position.

3. As it's stated in documentation related to the IN predicate:

The list of values can come from a number of different sources. In the constructor_expression and collection_valued_input_parameter, the list of values must not be empty; it must contain at least one value.

4. I would suggest you also avoid to use outdated Date and use instead java 8 Date/Time API.

So, taken into account all above, you should use a dynamic query as it was suggested also in comments by @SimonMartinelli. Particularly you can have a look at the specifications.

Assuming that you have the following mapping:

@EntitypublicclassError
{
   @Idprivate Long id;
   private String errorCode;

   // ...
}

@EntitypublicclassTable1
{
   @Idprivate Long id;
   private LocalDateTime date;
   private String code;

   @ManyToOneprivate Error error;

   // ...
}

you can write the following specification:

import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.CollectionUtils;

publicclassTableSpecs
{

   publicstatic Specification<Table1> findByFilter(LocalDateTime date, List<String> codes, List<String> errorCodes)
   {
      return (root, query, builder) -> {
         root.fetch("error", JoinType.LEFT);
         Predicateresult= builder.equal(root.get("date"), date);
         
         if (!CollectionUtils.isEmpty(codes)) {
            result = builder.and(result, root.get("code").in(codes));
         }
         if (!CollectionUtils.isEmpty(errorCodes)) {
            result = builder.and(result, root.get("error").get("errorCode").in(errorCodes));
         }
         return result;
      };
   }
}

publicinterfaceTableRepositoryextendsCrudRepository<Table1, Long>, JpaSpecificationExecutor<Table1>
{
   default List<Table1> findByFilter(LocalDateTime date, List<String> codes, List<String> errorCodes)
   {
      return findAll(TableSpecs.findByFilter(date, codes, errorCodes));
   }
}

and then use it:

List<Table1> results = tableRepository.findByFilter(date, Arrays.asList("TBL1"), Arrays.asList("ERCODE2")));