springboot jpa sql查询条件空值判断

String、Byte、Date、List 类型空值判断

@Query(value =  "select new com.test.Student(s.id,s.name,s.status,s.type,s.gmtCreate) "
      		+ " from Student s "
      		+ " where s.isDeleted = 0 "
      		+ " and (s.name like concat('%',:name,'%') or :name is null) "
			+ " and (s.type = :type or :type is null)" 
			+ " and (s.gmtCreate >= :startTime or :startTime is null) "
			+ " and (s.gmtCreate <= :endTime or :endTime is null)"
      		+ " and (COALESCE(:status,null) is null or (s.status in (:status)) ) "
            + " order by s.gmtCreate desc ")
   Page<Student> listUserSellBySearch(@Param("pageable") Pageable pageable, @Param("name") String name,
													  @Param("type") Byte type,
													  @Param("startTime") Date startTime,
                                                      @Param("endTime") Date endTime,
													  @Param("status") List<Byte> status);

原生sql用法，list判断空有所不同

@Query(value =  "select * from student s "
      		+ " where s.is_deleted = 0 "
      		+ " and (s.name like concat('%',:name,'%') or :name is null) "
			+ " and (s.type = :type or :type is null)" 
			+ " and (s.gmt_create >= :startTime or :startTime is null) "
			+ " and (s.gmt_create <= :endTime or :endTime is null)"
      		+ " and if(:status is null , s.status in (:status),1=1 ) "
            + " order by s.gmtCreate desc ",nativeQuery = true)
   Page<Student> listUserSellBySearch(@Param("pageable") Pageable pageable, @Param("name") String name,
													  @Param("type") Byte type,
													  @Param("startTime") Date startTime,
                                                      @Param("endTime") Date endTime,
													  @Param("status") List<Byte> status);