心血来潮想学习下jpa,然后我就发现我已经站在凉了的边缘:各种坑爹的语法让我生不如死!!!!
比如我一个简单的查询:需求是查询业务员的佣金,sql语句如下:
一点问题没有;
然后转换成jpa语法:
@Query(value = "select new map(t.orderNum,(select m.username from MembersPO m where" +
" m.id = t.member_id)as username,\n" +
" t.sumFinalPrice, IFNULL((select item.benfit from ItemPO item where " +
"item.id = mi.item_id)*sum(mi.buyFinalPrice),0) \n" +
"\tas\tcommission ,t.createTime as createTime)\n" +
" from PurchaseOrderPO t , PurchaseOrderItemPO mi \n" +
" where mi.purchaseOrderId = t.id\n" +
"\n" +
" and t.sendStatus = 1\n" +
"\tGROUP BY mi.purchaseOrderId \n" +
"\n" +
" order by t.id desc ",
countQuery = "select count(t.orderNum) from PurchaseOrderPO t, PurchaseOrderItemPO mi " +
" where mi.purchaseOrderId = t.id\n" +
" and t.sendStatus = 1\n" +
"\tGROUP BY mi.purchaseOrderId \n"
然后悲剧来了:
各种百度,找不到答案,谷歌一把,在https://stackoverflow.com/questions/27329843/how-to-use-isnull-in-jpa
找到答案了
改完后程序启动正常!