Checking for Null on a Collection in Jpql Queries

Checking for NULL on a Collection in JPQL queries?

So I was stuck doing something quite similar. Basically you want to check to see if either

1. the collection you are passing in is empty: then don't include it in the query
2. the collection you are passing in is populated: then do include it in the query

The problem with this is there is no good way to handle this part:

:categories = NULL

This is because when translated to SQL it is going to look something like this (when 2 items are in the collection):

@p0, @p1 = NULL

You cannot wrap it in parenthesis either, because this is not valid in SQL:

(@p0, @p1) = NULL

I tried to use coalesce to reduce the values down to NULL, but I couldn't get that to work either.

Basically from what I can tell there isn't anything in JPQL that lets you run some kind of function of a collection (hashset, etc) to see if it is populated or not. If anyone knows of one please let us know.

Finally I resorted to a hack, in the calling code I check the collection to see if it is populated. If it is not I simple pass in one more parameter ... an empty string.

String catString = "";
if (categoryList != null && !categoryList.isEmpty()) catString = "HasCats";

Then in the JPQL do this:

WHERE (:catsString = '' or cats IN (:categories)) "

Not the best solution in the world but it is functional.

How to query a collection that can be null in JPQL?

You can try using inner join to make sure children exists:

select distinct par from Parent par join par.children chi where chi.value = :value

You can use exists operator:

select distinct par from Parent par 
where exists 
 (select chi 
  from Child chi 
where chi.value = :value and chi.parent = par)

JPQL query with input parameter collection containing null

As a Java programmer, where null = null yields true it might come as a surprise that in SQL (and JPQL) null = null is itself null which is "falsy". As a result, null in (null) yields null as well.

Instead you need to treat null seperately with a IS NULL check: e.status IS NULL OR e.status IN :statuses.

This is described in 4.11 Null Values of the JPA Specification:

• Comparison or arithmetic operations with a NULL value always yield an unknown value.
• Two NULL values are not considered to be equal, the comparison yields an unknown value.

How to check collection for null in spring data jpa @Query with in predicate

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:

@Entity
public class Error
{
   @Id
   private Long id;
   private String errorCode;

   // ...
}

@Entity
public class Table1
{
   @Id
   private Long id;
   private LocalDateTime date;
   private String code;

   @ManyToOne
   private 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;

public class TableSpecs
{

   public static Specification<Table1> findByFilter(LocalDateTime date, List<String> codes, List<String> errorCodes)
   {
      return (root, query, builder) -> {
         root.fetch("error", JoinType.LEFT);
         Predicate result = 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;
      };
   }
}

public interface TableRepository extends CrudRepository<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")));

JPQL query --- how to use 'is null'

As mentioned, address column is empty, then try using IS EMPTY expression instead of IS NULL.

em.createQuery( "SELECT o FROM Person o where (o.address.id IS NULL OR o.address.id = 0").setMaxResults(50).getResultList();

Check constraint according to id's datatype.

Also there is no need to mention setFirstResult(0) as it is not going to skip any results & without it, by default all matching results will be fetched.

How to check UUID null value in JPQL?

After a bit investigation on Internet, I found the solution.
As PostgreSQL can not determine dataType, we can declare it from before as TypedParameterValue.

TypedParameterValue attributeId = new TypedParameterValue(PostgresUUIDType.INSTANCE, UUIDUtil.toUUID(attributeId));
Page<Attribute> attributes = attributeRepo.findByAttributeId(attributeId);

Then in JPQL for nullChecking, cast to org.hibernate.type.PostgresUUIDType:

( In IDE, it can be shown as error, but it compiles actually)

 @Query("select a from Attribute a where (cast(:attributeId as org.hibernate.type.PostgresUUIDType) OR a.id = :attributeId)")
 Page<Attribute> findByAttributeId(@Param("attributeId") TypedParameterValue attributeId);

In Native Query:

 @Query(value = "select * from attribute a where (cast(:attributeId as uuid) OR a.id = :attributeId)",nativeQuery = true)
 List<Attribute> findByAttributeId(@Param("attributeId") TypedParameterValue attributeId);

How to query a column which value is null in JPA?

Just change your query to

 @NamedQuery(name = "Concept.findByRefTableNull", query = "SELECT c FROM Concept c WHERE c.conceptName = :conceptName and c.refTable IS NULL"),

---

Related Topics