遇到问题

在使用jpa的时候发现,通过@Query自定义的sql,参数不能传空,传空就报错:

在使用jpa的时候发现,通过@Query自定义的

1
2
3
4
5
6
7
8
9
10
11
12
13
@Query(value = "select  new com.winstar.vo.OilCouponVo(t.oilId,s.phone,s.certNo,t.couponCode,t.orderNumber,t.couponAmt,t.couponName,t.useState,t.gasStationId,t.gasStationName,t.useDate,t.createdAt,t.updatedAt,t.status) from OilCoupon t left join Account s on t.userId = s.id " +
"where (t.createdAt between ?1 and ?2) and t.useDate = ?3 and t.couponCode like ?4 and t.orderNumber like ?5 and t.appId = ?6 and s.phone = ?7 and s.certNo = ?8",
countQuery = "select count(t.id) from OilCoupon t left join Account s on t.userId = s.id " +
"where (t.createdAt between ?1 and ?2) and t.useDate = ?3 and t.couponCode like ?4 and t.orderNumber like ?5 and t.appId = ?6 and s.phone = ?7 and s.certNo = ?8")
Page<OilCouponVo> getOilPage(Date startTime,
Date endTime,
String useState,
String couponCode,
String orderNumber,
String appId,
String phone,
String certNo,
Pageable pageable);

解决办法

原生sql解决办法如下:

1
2
3
@Query(value = "select * from xxx where if(?1 !='',x1=?1,1=1) and if(?2 !='',x2=?2,1=1)" +
"and if(?3 !='',x3=?3,1=1) ",nativeQuery = true)
List<XXX> find(String X1,String X2,String X3);

非原生sql解决办法如下

1
2
3
4
5
6
7
8
@Query(value = "select  new com.winstar.vo.OilCouponVo(t.oilId,s.phone,s.certNo,t.couponCode,t.orderNumber,t.couponAmt,t.couponName,t.useState,t.gasStationId,t.gasStationName,t.useDate,t.createdAt,t.updatedAt,t.status) from OilCoupon t left join Account s on t.userId = s.id " +
"where (t.createdAt between ?1 and ?2) and (?3 is null or ?3 = '' or t.useState = ?3) and (?4 is null or ?4 = '' or t.couponCode like ?4)" +
" and (?5 is null or ?5 = '' or t.orderNumber = ?5) and (?6 is null or ?6 = '' or t.appId = ?6)" +
" and (?7 is null or ?7 = '' or s.phone = ?7) and (?8 is null or ?8 = '' or s.certNo like ?8)",
countQuery = "select count(t.id) from OilCoupon t left join Account s on t.userId = s.id " +
"where (t.createdAt between ?1 and ?2) and (?3 is null or ?3 = '' or t.useState = ?3) and (?4 is null or ?4 = '' or t.couponCode like ?4)" +
" and (?5 is null or ?5 = '' or t.orderNumber = ?5) and (?6 is null or ?6 = '' or t.appId = ?6)" +
" and (?7 is null or ?7 = '' or s.phone = ?7) and (?8 is null or ?8 = '' or s.certNo like ?8)")

【1】简书主页·share猿
【2】掘金主页·share猿


扫描以下公众号关注小猿↓↓↓↓↓↓↓↓

更多资讯请在简书、微博、今日头条、掘金、CSDN都可以通过搜索“Share猿”找到小猿哦!!!