@Query(value = "select bill.id_ as id, bill.created_date as date, bill.no, lawyer_case .case_no as caseNo, " +
"lawyer_case .case_name as caseName, customer.no as customerNo, customer.cn_name as customerName, " +
"bill.total_expense_after_tax, bill.collected_money, bill.book_ticket_amount, bill.version " +
"e1.name as creator, bill.status" +
"from bill " +
"left join lawyer_case on lawyer_case .case_no=bill.case_no " +
"left join customer on customer.no=bill.customer_no " +
"left join employee e1 on e1.id_=bill.creator " +
"where IF (?1!='', customer_no=?1, 1=1) " +
"and IF (?2!='', case_no=?2, 1=1) " +
"and IF (?3!='', status=?3, 1=1) " +
"and IF (?4!='', creator'%',?4,'%')), 1=1) " +
"and create_by=?5 " +
"ORDER BY ?#{#pageable} ",
countQuery = "select count(*) " +
"from bill " +
"left join lawyer_case on lawyer_case .case_no=bill.case_no " +
"left join customer on customer.no=bill.customer_no " +
"left join employee e1 on e1.id_=bill.creator " +
"where IF (?1!='', customer_no=?1, 1=1) " +
"and IF (?2!='', case_no=?2, 1=1) " +
"and IF (?3!='', status=?3, 1=1) " +
"and IF (?4!='', creator'%',?4,'%')), 1=1) " +
"and create_by=?5 "+
"ORDER BY ?#{#pageable} ",
nativeQuery = true)
Page<Object[]> findAllBill(String customerNo, String caseNo, Integer status, String creator,
String createBy, Pageable pageable);
大部分系统都是按照修改时间进行降序排序。 所以，order by可以写死。然后pageNumber和pageSize动态传入。
pageNumber的算法= (pageNumber - 1) * pageSize, 前提是PageNumber是从1开始，若0，则pageNumber=pageNumber * PageSize； 这样就可以保证数据的正确。
/**
* pageInfos: 转换之后的数据。
* pageable：传入的pageable.
* totalPage: 第一条SQL算好的返回值。
* 这样就可以统一的返回各种pageDTO。
*/
private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {
return new PageImpl<>(pageInfos, pageable, totalPage);
}
这次有个需求，需要动态的sql语句去查询，但是@Query正常情况下SQL语句是写死的，在查找了很多资料后，想到了一个好的解决办法
利用MYSQL的判断来拼接SQL语句
先上代码
@Query(value = "select * from project_demand where project_id=?1 and if(?2!='',demand_id in (select demand_id from demand_user where user_id=?2),1=1)",nativeQuery = true) List<ProjectDemand> getListByUser(String projectId,String userId);
红色部分，就是生成动态SQL的方法，利用MYSQL的if函数和我们传递的参数去进行判断，然后获取SQL语句。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
