咪卡四處看

主要透過SQL COALESCE做資料處理判斷，COALESCE ( expression [ ,...n ] )傳回第一個非null expression

JPA運作：

----------
以
List<Long> testList= new ArrayList();
testList.add(null);
testList.add(Long.valueOf(13699885));
testList.add(Long.valueOf(13699886));
為例
此段內容JPA 會把
COALESCE(null,13699885,13699886) is null 轉成SQL nvl(null, nvl(13699885, 13699886)) is null

以 List<Long> testList= null 為例
此段內容JPA 會把
COALESCE(null) is null 轉成SQL  null is null

----------

@Query("SELECT a FROM Member a WHERE (COALESCE(:idList) is null or a.id in (:idList))")
//@Query("SELECT a FROM Member a WHERE (COALESCE(:idList,null) is null or a.id in (:idList))", nativeQuery = true)
public List<Member> getByidList(List<Long> idList);

List<Long> testList= new ArrayList();
testList.add(null);
testList.add(Long.valueOf(13699885));
testList.add(Long.valueOf(13699886));
System.out.println(memMemberInfoRepository.getByidList(testList).size()); //right

testList= null;
System.out.println(memMemberInfoRepository.getByidList(testList).size()); //right

//testList= new ArrayList();
//System.out.println(memMemberInfoRepository.getByidList(testList).size()); //error

refer:  https://stackoverflow.com/questions/46789664/how-to-skip-param-in-query-if-is-null-or-empty-in-spring-data-jpa

https://docs.microsoft.com/zh-tw/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15