JPA if && in语句示例

示例:

//按照参数顺序填入sql
    @Query(value = "select count(1) from exam where class=?1 AND if(COALESCE(?2,NULL) IS NOT NULL, status in (?2), 1=1) AND score=?3", nativeQuery = true)
    int countByParam(int className, List<Integer> statusList, int score);

    //按照参数顺序填入sql
    @Query(value = "select count(1) from exam where class=:class AND if(COALESCE(:statusList,NULL) IS NOT NULL, status in (:statusList), 1=1) AND score=:score", nativeQuery = true)
    int countByParam(@Param("class") int className, @Param("statusList")List<Integer> statusList, @Param("score")int score);
  • 需要注意的是,list参数statusList不可以为empty list。 如果statusList.size() == 0,则设置statusList=null。

  • 语句说明:if(exp1, exp2, exp3) 表示 return exp1 ? exp2 : exp3。exp3一般是 1=1,一个空操作占位,使得sql语句合法。

  • ?1 和 :class 是两种带入参数的方法,不可在同一个sql语句中出现,否则报错:Mixed parameter strategies - use just one of named, positional or JPA-ordinal strategy

JPA sql语句调试

application.properties 中添加配置

spring.jpa.show-sql=true # 显示sql语句
spring.jpa.properties.hibernate.format_sql=true #sql语句格式规范

关于上述if & in 语句的调试,看下check list empty的几种写法和问题

COALESCE

@Query(value = "select count(1) from exam where if(COALESCE(:statusList,NULL) IS NOT NULL, status in (:statusList), 1=1) AND score=:score", nativeQuery = true)
int countByParam(@Param("statusList")List<Integer> statusList, @Param("score")int score);
  • statusList = [1]
    select
        count(1) 
    from
        exam 
    where
        if(COALESCE(?,NULL) IS NOT NULL, status in (?), 1=1) 
        and score=? 
  • statusList = [1,2]
    select
        count(1)  
    from
        exam 
    where
        if(COALESCE(?, ?,NULL) IS NOT NULL, status in (?, ?), 1=1) 
        and score=? 
  • statusList=[]
    select
        count(1)  
    from
        exam  
    where
        if(COALESCE(,NULL) IS NOT NULL, status in (), 1=1) 
        and score=? 

sql语句中有空白,会报错 You have an error in your SQL syntax,...

  • statusList = null
    select
        count(1)  
    from
        exam  
    where
        if(COALESCE(?,NULL) IS NOT NULL, status in (?), 1=1) 
        and score=? 

IS NOT NULL

@Query(value = "select count(1) from exam where if(:statusList IS NOT NULL, status in (:statusList), 1=1) AND score=:score", nativeQuery = true)
int countByParam(@Param("statusList")List<Integer> statusList, @Param("score")int score);
  • statusList = [1]
    select
        count(1)  
    from
        exam  
    where
        if(? IS NOT NULL, status in (?), 1=1) 
        and score=? 
  • statusList = [1,2]
    select
        count(1)  
    from
        exam  
    where
        if(?, ? IS NOT NULL, status in (?, ?), 1=1)  
        and score=? 

sql语句有误,报错:You have an error in your SQL syntax

  • statusList = []
    select
        count(1)  
    from
        exam  
    where
        if( IS NOT NULL, status in (), 1=1)   
        and score=? 

sql语句有误,报错:You have an error in your SQL syntax

  • statusList = null
    select
        count(1)  
    from
        exam  
    where
        if(? IS NOT NULL, status in (?), 1=1)   
        and score=? 

where 子句实际为 where 1=1 and score=?

参考:
官方文档

posted on 2021-01-28 11:42  漫夭  阅读(1289)  评论(0编辑  收藏  举报