@Query(value="SELECT c.byname as byname, c.cart as cart,c.phone as phone,c.surname as surname, c.id as id,c.update_time as updateTime,c.head_img as headImg,c.is_blacklist as isBlacklist,c.is_member as isMember,"
+ "c.open_id as openid,c.take_address as takeAddress, c.pay_money as payMoney, "
+ " SUM(culog.pay_money) AS countPayMoney FROM cuser c "
+ "LEFT JOIN cuser_money_log culog ON c.id = culog.cuser_id AND culog.is_pay = 1 "
+ "WHERE IF (:byname is not null, c.byname LIKE CONCAT('%',:byname,'%') , 1 = 1) and IF (:isMember is not null, c.is_member = :isMember , 1 = 1) and IF (:isBlacklist is not null, c.is_blacklist = :isBlacklist , 1 = 1) and "
+ "IF (:phone is not null, c.phone = :phone , 1 = 1)"
+ "GROUP BY c.id LIMIT :PageOne,:PageSize",nativeQuery=true)
List<Map<String, Object>> countByQuery(@Param("byname") String byname,@Param("isMember") Integer isMember,@Param("isBlacklist") Integer isBlacklist,@Param("phone") String phone,@Param("PageOne") Integer PageOne, @Param("PageSize")Integer PageSize);
多表多条件查询并统计金额 JPA
以Mysql数据库为例。
在存储过程中使用判断一个参数，例参数为vtitle
Select a.*
from trn_res_courseware a
where 1 = 1 and
IF (vtitle is NULL, 0 = 0, a.title like CONCAT('%'+vtitle+'%'));
vtitle 是参数。
如果参数为空，则不执行(0=0永远成立)，
不为空，则执行 a.title like CONCAT('%'+vtitle+'%')) 查询条件。
类型于mybaties上面的if(为空) 则不进入条件查询
该方式支持多表多条件查询 单表的话JPA有单独的接口可提供多条件查询