springboot jpa sql查询条件空值判断

最新推荐文章于 2025-06-05 21:41:56 发布

原创最新推荐文章于 2025-06-05 21:41:56 发布 · 1.9k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#spring boot #sql

文章讲述了在Java中使用ORM（如Hibernate）进行SQL查询时，如何处理不同类型参数（如String、Byte、Date和List<Byte>）的空值判断，以及原生SQL查询的用法，以获取`Student`表中满足特定条件的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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