Check That a List Parameter Is Null in a Spring Data JPA Query

Check that a List parameter is null in a Spring data JPA query

OK I though of a thing after waking up at the middle of the night :

@Query("SELECT DISTINCT c FROM Course c\n" +
       "WHERE c.courseDate < CURRENT_TIME\n" +
       "AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
       "AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
       "AND (COALESCE(?3) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

The solution was simply to use COALESCE in addition to IS NULL so it can work with multiple values. That way if the list contain at least one non-null value, the second expression ((c.category IN ?3)) will do the job of filtering.

I will wait at least a whole night next time before asking a question :)

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")));

Spring data jpa native query with null parameter (PostgreSQL)

This is a common problem with Hibernate + PostgreSQL, and the solution is to implement that method yourself, instead of having Spring do it for you. Within the implementation you have to do something like this

List<CustomProjection> findByCustomCondition(
        @Param("identifierId") Long identifierId,
        @Param("eventTypes") List<String> eventTypes) {
     // entityManager is acquired with the @PersistenceContext annotation as an injectable
     Query q = entityManager.createNativeQuery(..., CustomProjection.class);
     // the important part:
     q.setParameter("identifierId", 0L);
     q.setParameter("identifierId", identifierId);
     ...

First call to setParameter ensures Hibenate uses the correct type setter, second call overrides the first value without having executed the query, and skips type detection.

How to avoiding AND condition if parameter is null in Spring Data JPA query

Here are some possible options for you

1. Create multiple methods in your repository like

  @Query("select u.username,p.pname from Users u join u.priviJoin p where u.username = :uname AND p.pname = :pname")
    List<Users> findByusernamewithRole(@Param("uname") String uname , @Param("pname") String pname );

  @Query("select u.username,p.pname from Users u join u.priviJoin p where u.username = :uname")
    List<Users> findByUsernameWithoutRole(@Param("uname") String uname);

2. Write a custom respository and use EntityManager. With this you can create a dynamic queries based on your input using CriteriaBuilder and use this criteria in querying.

3. Last and the most preferred option in case of dynamic inputs(like you have) is Querydsl.
   Some articles about querydsl
   
   http://www.baeldung.com/querydsl-with-jpa-tutorial
   
   http://www.querydsl.com/static/querydsl/latest/reference/html/ch02.html

---

Related Topics