如何使用union多表分頁(yè)條件查詢

當(dāng)前項(xiàng)目有一個(gè)全局搜索的功能，需要涉及到7-8張主表，由于當(dāng)前項(xiàng)目使用的是Jpa，所以我決定使用原生sql的方式使用union來(lái)進(jìn)行查詢，由于對(duì)這個(gè)框架的不熟悉，被坑了很久，特此記錄。

條件

1、在參數(shù)列表中，使用@Param注解（例：@Param(“userId”) Long userId）

2、在sql中應(yīng)該以 :userId 的方式來(lái)注入?yún)?shù)（例：person.user_id = :userId），或者用?1 ?2根據(jù)參數(shù)的順序來(lái)注入也可以，我采用的是第一種

3、sql應(yīng)該是寫在@Query的value中，并且設(shè)置 nativeQuery = true

4、如果要設(shè)置一個(gè)可能為空的值，比如：有一個(gè)分類，需要當(dāng)作條件查詢，但是這個(gè)值是可能為空的，所以需要在sql中判斷，如果為空就不進(jìn)行篩選。語(yǔ)法其實(shí)和在sql中判斷是一樣的

where IF(:category is not null,a.category = :category is not null,1=1)

分頁(yè)

1、分頁(yè)必須加countQuery這個(gè)參數(shù)，把上面sql價(jià)格count就可以

2、傳入Pageable，不要傳sort，不然會(huì)報(bào)錯(cuò)，暫時(shí)沒(méi)找到解決方案

例如 select * from a 為你的sql

那么分頁(yè)應(yīng)該是

select * from (select * from a) b order by ?#{#pageable}

order by在countQuery里面就不用加了哦

3、返回的是Page<Object[]>，里面content封裝的順序就是你sql查出來(lái)的順序

踩過(guò)的坑

1、多表union查詢，查詢的字段應(yīng)該是一樣的，比如第一張user表查詢id，name，sex、第二張address表也必須查詢id，name，sex，如果沒(méi)有這個(gè)字段則自己封裝返回固定的值，可以為空字符串

2、傳入pageable的時(shí)候 ?#{#pageable} 必須這樣寫

分享幾個(gè)用到的mysql語(yǔ)法

1、判斷是否為空，不為空查詢時(shí)間在傳入時(shí)間之間的數(shù)據(jù)

IF(:startTime is not null and :endTime is not null,proc.create_time between :startTime and :endTime,1=1)

這邊我在service是判斷了結(jié)束時(shí)間是否為空的，如果結(jié)束時(shí)間為空，開始時(shí)間不為空默認(rèn)查詢一年的數(shù)據(jù)

2、根據(jù)名稱模糊查詢

proc.content LIKE CONCAT('%',:content,'%')

3、最后根據(jù)查出來(lái)的總數(shù)據(jù)對(duì)類型篩選

IF(:category is not null,a.category = :category is not null,1=1)

小結(jié)一下：

1、其實(shí)很簡(jiǎn)單的分頁(yè)條件查詢，因?yàn)椴皇煜pa的語(yǔ)法，導(dǎo)致浪費(fèi)了很多時(shí)間，希望可以幫到更多的人。

2、暫時(shí)不知道怎么排序，無(wú)論是在pageable中傳入sort，還是在sql最后進(jìn)行排序都會(huì)報(bào)錯(cuò)，如果有大佬知道的話可以點(diǎn)撥一下。

jpa執(zhí)行原生sql union bug解決

@Query(value = "SELECT * FROM count_entity where id=-1111 union (SELECT * FROM count_entity where video_name = ?1 AND platform ='騰訊視頻' ORDER BY click_date DESC LIMIT ?2)"
?+ "UNION"
?+ "(SELECT * FROM count_entity where video_name = ?1 AND platform ='愛奇藝' ORDER BY click_date DESC LIMIT ?2)"
?+ "UNION"
?+ "(SELECT * FROM count_entity where video_name = ?1 AND platform ='芒果TV' ORDER BY click_date DESC LIMIT ?2)", nativeQuery = true)

union前面需要連接一個(gè)空數(shù)據(jù)查詢，因?yàn)閡nion 第一個(gè)查詢不能用（）

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論