Spring data jpa @Query 使用原生Sql的坑
根據代碼來解說：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
@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); |
需要注意的方法有以下幾點
- 1、From 不支持重命名.
- 2、返回的是一個page<Object[]>，數組中只保存了數據，沒有對應的key，只能根據返回數據的順序，依次注入到DTO中。
- 3、對于使用分頁，需要：“ORDER BY ?#{#pageable}”，可以直接傳入一個pageable對象，會自動解析。
- 4、注意格式問題，很多時候就是換行的時候，沒有空格。
- 5、仔細對應數據庫中表字段，很多時候報某個字段找不到，就是因為字段名寫錯，和數據庫中對應不上。
- 6、這是解決使用微服務，大量的數據都需要遠程調用，會降低程序的性能。
- 7、使用Pageabel作為參數的時候，去進行分頁。剛開始的時候，覺得還是一個可行的辦法，但是得注意的時候，當需要排序的時候，是無法加入sort字段的。 會一直報錯left*。
- 8、針對7的解決方案，把原生SQL的數據查詢和countQuery分成兩個查詢方法。得到count，然后進行判斷，若是等于0，則直接返回空集合；反之，則取獲取數據。 需要自己進行分頁計算，傳入正確的pageNumber和pageSize。
大部分系統都是按照修改時間進行降序排序。 所以，order by可以寫死。然后pageNumber和pageSize動態傳入。
pageNumber的算法= (pageNumber - 1) * pageSize, 前提是PageNumber是從1開始，若0，則pageNumber=pageNumber * PageSize； 這樣就可以保證數據的正確。
|
1
2
3
4
5
6
7
8
9
|
/*** pageInfos: 轉換之后的數據。* pageable：傳入的pageable.* totalPage: 第一條SQL算好的返回值。* 這樣就可以統一的返回各種pageDTO。*/private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) { return new PageImpl<>(pageInfos, pageable, totalPage); } |
SpringData JPA @Query動態SQL語句
這次有個需求，需要動態的sql語句去查詢，但是@Query正常情況下SQL語句是寫死的，在查找了很多資料后，想到了一個好的解決辦法
思路
利用MYSQL的判斷來拼接SQL語句
實現
先上代碼
|
1
2
|
@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語句。
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持服務器之家。
原文鏈接：https://blog.csdn.net/weixin_38608626/article/details/83183556
